Tom, Bruce, and others involved in this recurring TODO discussion…
First, let me start by saying that I understand this has been discussed
many times before; however, I’d like to see what the current state of
affairs is regarding the possibility of using a unique index scan to
speed up the COUNT aggregate.
A few of my customers (some familiar with Oracle) are confused by the
amount of time it takes PostgreSQL to come up with the result and are
hesitating to use it because they think it’s too slow. I’ve tried to
explain to them why it is slow, but in doing so I’ve come to see that
it may be worth working on.
I've reviewed the many messages regarding COUNT(*) and have looked
through some of the source (8.0-RC4) and have arrived at the following
questions:
1. Is there any answer to Bruce’s last statement in the thread, “Re:
[PERFORM] COUNT(*) again (was Re: Index/Function organized”
(http://archives.postgresql.org/pgsql-hackers/2003-10/msg00245.php)
2. What do you think about a separate plan type such as IndexOnlyScan? Good/stupid/what is he on?
3. Assuming that Bruce’s aforementioned statement is correct, what
hidden performance bottlenecks might there be?
4. What is the consensus of updating a per-relation value containing
the row counts?
Though not exactly like PostgreSQL, Oracle uses MVCC and performs an
index scan on a unique value for all unqualified counts. Admittedly,
counts are faster than they used to be, but this is always a complaint
I hear from open source users and professionals alike.
I’ve been pretty busy, and I still need to get the user/group quota
working with 8.0 and forward the diffs to you all, but I would be
willing to work on speeding up the count(*) if you guys give me
your input.
As always, keep up the good work!
Respectfully,
Jonah H. Harris, Senior Web Administrator
Albuquerque TVI
505.224.4814