Re: 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.

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