Skip site navigation (1) Skip section navigation (2)

Much Ado About COUNT(*)

From: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Much Ado About COUNT(*)
Date: 2005-01-12 17:42:56
Message-ID: 41E561A0.8080008@tvi.edu (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
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



In response to

Responses

pgsql-announce by date

Next:From: Tom LaneDate: 2005-01-12 18:24:26
Subject: Re: Much Ado About COUNT(*)
Previous:From: Reinhard MaxDate: 2005-01-12 17:20:14
Subject: Re: SUSE port (was [ANNOUNCE] PostgreSQL 8.0.0 Release

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-01-12 18:24:26
Subject: Re: Much Ado About COUNT(*)
Previous:From: Reinhard MaxDate: 2005-01-12 17:20:14
Subject: Re: SUSE port (was [ANNOUNCE] PostgreSQL 8.0.0 Release

pgsql-patches by date

Next:From: Tom LaneDate: 2005-01-12 18:24:26
Subject: Re: Much Ado About COUNT(*)
Previous:From: Reinhard MaxDate: 2005-01-12 17:20:14
Subject: Re: SUSE port (was [ANNOUNCE] PostgreSQL 8.0.0 Release

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group