Re: [SQLObject] PostgreSQL: costly count

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

On Dec 4, 2003, at 11:00 AM, J-P Lee wrote:
> Hmm.. the thread seems to suggest the same thing.  I'm not sure if you 
> can get around the sequential scan.  count(id) saves you the time of 
> fetching all field contents.  As the thread points out, this time diff 
> is significant for large tables.  For small ones, though, it's 
> probably the same as count(*).

count(*) at least has the potential to be more easily optimized (and 
now that I think about it, I believe MySQL optimizes all instances of 
count(*)).  If you are counting any particular field, you have to look 
for NULLs (which don't get counted).  If you're clever you can tell 
that the primary key column can't be NULL, with count(*) you don't need 
to be clever at all.  And of course there's the optimization that you 
don't have to keep any information during your scan, except to 
increment a counter for each result (which is true of any count, except 
count(distinct ...)).

Oh, but I see from the discussion that it's all about transactions, and 
that MySQL does the same thing with transactions as well.  Anyway, 
found this in that thread:

 >>>
example:
On our message boards each post is a row.  The powers that be like to 
know
how many posts there are total (In addition to 'today')-
select count(*) from posts is how it has been
done on our informix db.  With our port to PG I instead select reltuples
pg_class.
<<<

pg_class is a mysterious table indeed, but it's got lots of good magic 
in it.  (I'm not even sure if this is the right magic, but worth a try)

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