Re: [SQLObject] PostgreSQL: costly count

[Ian B. <ia...@co...>]

On Dec 4, 2003, at 4:05 PM, Frank Barknecht wrote:
> Hallo,
> Frank Barknecht hat gesagt: // Frank Barknecht wrote:
>
>> Now, how to put this to use?
>
> And related: This does not count the results from where-claused
> queries like: "SELECT COUNT(*) FROM normal WHERE artist LIKE
> '%Britney%';", or does it (somehow)?

My impression is that this is an internal value kept to do query 
optimization (since table size effects that considerably).  It may not 
be entirely accurate, and doesn't get updated immediately.  It can't be 
used with any query.

If you want a query to be fast, you may want to use full-text search, 
or otherwise partition your table more, e.g., an artist table, and you 
do something like "SELECT COUNT(normal.id) FROM normal, artist WHERE 
normal.artist_id = artist.id AND (artist.first_name = 'Britney' OR 
artist.last_name = 'Britney')".  Everything in that query can be 
indexed, and so it will be quite fast (and more importantly, scale to 
large databases).  LIKE doesn't scale well (but full text scales 
better).

If you are doing some sort of paging, you might simply want to cache 
the results of the count.  Also, if you are doing paging with ordering 
(which you probably want), then paging and slicing will only save you 
from fetching and constructing all the objects, but the database still 
has to look at all the rows (because it can't sort them until it does 
that).  So it might be faster to pull and cache the full results once, 
on the assumption that the user will be looking at later results 
eventually.

Hmm... a useful feature would be to implement equality and hashing for 
query objects (which are immutable).   Then you could construct a 
query, and use that as a dictionary/cache key for the select results.  
That might be too difficult, though, because AND(a, b) == AND(b, a), 
but those logic relationships are hard to find.  You can always get the 
query's string/SQL representation, and cache based on that.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org