Re: Long count(*) time

David Monarchi wrote:
> Hello -
>
> I'm running PG 8.2 on an 8-processor 16G Unix machine.  The machine is
> dedicated to the db, and only 5 threads/processors are busy.  The
> following query takes 70 seconds to execute.
>      select count(*) from url_list_url;
> There are 64,219,173 rows in the table.  The table consists of an
> integer field and a text field.  The average length of the text field is
> 50 characters.  There are btree indexes on both fields.  The integer
> field is the key.
>
> 70 seconds seems to be a long time for this kind of query.  Is this normal?
>
> Thanks.
>
> David

Unless I misplaced a decimal point, that works out to a tad over
52MBytes/sec which would not be unreasonable for a lower-end disk
subsystem (the details of which you didn't mention at all).

Now, if you're simply wondering "Why does count(*) take so long?" you
may want to search the list archives. It's a question that's been asked
and answered many times and a search on something like "slow count" will
provide hours of reading material.

The very short answer: An unrestricted count(*) must, by the nature of
the current MVCC implementation used by PostgreSQL, read the entire
table. An index cannot be used (well, it can, but using it would slow
things down even further).

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/