Thread: COUNT(*) very slow on table with primary key

Hi folks,

I've been having trouble with a slow query on a table that has a primary
key (which I understand is supported in PostgreSQL by a UNIQUE non-NULL
index).  It's a SELECT COUNT(*) without a WHERE clause; really simple
stuff.

I would have thought the number of entries in the primary key index
could simply be inspected and returned.

EXPLAIN reports that a sequential table scan is being performed, which
sounds right, since the actual operation takes about 10 seconds when
there are about half a million rows in the table.

The table's pretty wide (about 2.5KB), but has been subjected to a
VACUUM ANALYZE operation in accordance with the advice given in the FAQ.

Is this just "how it is" or is there more stuff I could look at?  I'm
actually trying to speed up a more complex query, but figured it would
be better to report (and get feedback on) the simplest case.

Ciao,
Sheldon.

> Hi folks,
>
> I've been having trouble with a slow query on a table that has a primary
> key (which I understand is supported in PostgreSQL by a UNIQUE non-NULL
> index).  It's a SELECT COUNT(*) without a WHERE clause; really simple
> stuff.

    If there is no WHERE clause I don't see how anything but a sequential
scan could work. I think Tom is working on some more detailed statistics for
7.2 that will help getting the total number of rows in a table quickly,
though I can't say for sure..

> The table's pretty wide (about 2.5KB), but has been subjected to a
> VACUUM ANALYZE operation in accordance with the advice given in the FAQ.
>
> Is this just "how it is" or is there more stuff I could look at?  I'm
> actually trying to speed up a more complex query, but figured it would
> be better to report (and get feedback on) the simplest case.

    If you add a WHERE clause and index the field in the WHERE clause you
should get different results. I should say that you should get an index scan
if that is what the optomizer thinks in the best plan, if you don't you can
force it by setting enable_seqscan to on or off.

-Mitch

On Tue, 14 Aug 2001, Sheldon Hearn wrote:

> I've been having trouble with a slow query on a table that has a primary
> key (which I understand is supported in PostgreSQL by a UNIQUE non-NULL
> index).  It's a SELECT COUNT(*) without a WHERE clause; really simple
> stuff.
>
> I would have thought the number of entries in the primary key index
> could simply be inspected and returned.
>
> EXPLAIN reports that a sequential table scan is being performed, which
> sounds right, since the actual operation takes about 10 seconds when
> there are about half a million rows in the table.
>
> The table's pretty wide (about 2.5KB), but has been subjected to a
> VACUUM ANALYZE operation in accordance with the advice given in the FAQ.
>
> Is this just "how it is" or is there more stuff I could look at?  I'm
> actually trying to speed up a more complex query, but figured it would
> be better to report (and get feedback on) the simplest case.

IIRC, Because the tuple validity status information is stored in the data
file and not the index, it's got to read the row from the data file to see
if it's currently valid to your transaction (and the index becomes more
expensive at that point since you'll be reading all the heap rows anyway)