Re: Indices for select count(*)?

Alexander Scholz <alexander(dot)scholz1(at)freenet(dot)de> writes:

> Hi, thank you for your answer.
>
> Regarding the performance flow when trying to find out how many records are
> currently being stored in the table, I don't see how an index should help...
> Nevertheless we've created an unique index on "ID" but SELECT count("ID") from
> "XYZ" still takes 35 seconds*. (ID is the primary key basing on a sequence,
> select count(*) isn't faster.)
>
> So - what kind of indexing would speed this up then?

No form of indexing can speed this up. To answer the server has to look at
every record and count up how many of them should be included in your result.

If you only need an approximate value there's one available in the stats
tables (I don't remember exactly how to get it) or you can keep a recent value
in a table and update it periodically and just query that.

> *) MSSQL 2005 on the same server takes 4 seconds for this query for the
> analogue table, and there hasn't any special tuning been applied, too.

MSSQL presumably has the entire table cached in RAM and postgres doesn't. Even
if MSSQL can scan just the index (which postgres can't do) I would only expect
a factor of 2-4x. Hm. Unless perhaps this table is extremely wide? How large
are these records?

--
greg