On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:
> Scott,
>
> unless id is indexed there is nothing that can be done with
> select count(*) from table where id >10000;
> Otherwise, the index should be scanned, not the table.
Maybe, maybe not. If id>10000 is most of the table,
you're effectively scanning the whole table (in fact in that
case it'll probably opt to do a seq scan anyway) since you
don't know if the tuple is live until you can actually see
it.
> And, scanning a large table to get count(*) will always be worst than
> maintaining your own count.
The select may be faster, but in overall speed you may lose if there's
alot of contention on changing the count relative to the frequency of
reading the count.
It could be a good thing, but you'd have to make sure that you
could accurately reproduce the count for all the various visibility
rules that it might be executed in. At the very least it'd have to
give the right results for both base serializable and read committed
(note that in the latter you may see rows that were committed by
a transaction that was not committed at the start).