Date: Thu, 7 Jul 2005 20:21:42 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Varun Mehta <vmehta@apple.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
Message-ID: <20050708022141.GB3339@winnie.fuhr.org>
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>

On Thu, Jul 07, 2005 at 03:48:39PM -0700, Varun Mehta wrote:
> 
> I've started using PostgreSQL pretty recently, and I am quite  
> disturbed about the performance of a simple SELECT COUNT(*) FROM  
> table.  What should (in my mind) be a nearly instantaneous operation  
> instead takes nearly 700ms in a table with only 87k rows of data!

Speeding up COUNT is on the developers' TODO list, but it's not as
simple as it might seem or it would have been done already.  This
has been brought up many times over the years -- search the archives
to see past discussion.  Words to search for include "MVCC," "index,"
and "visibility."

> If I run an EXPLAIN on this query I can see that it is doing a  
> sequential scan, which seems quite needless, as surely this  
> information is cached in some secret location.

If an estimate will suffice then you could use the table's
pg_class.reltuples value, but beware that it can be rather
out of date.

http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/