From: Michael Fuhr <mike@fuhr.org>
To: Varun Mehta <vmehta@apple.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
Date: Thu, 7 Jul 2005 20:21:42 -0600
Message-ID: <20050708022141.GB3339@winnie.fuhr.org> (raw)
In-Reply-To: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
References: <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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, vmehta@apple.com
Subject: Re: Make COUNT(*) Faster?
In-Reply-To: <20050708022141.GB3339@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox