Re: [NOVICE] 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/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster