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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
