Re: PostgreSQL Gotchas --- count()

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Gregory S. Williamson wrote:
>> [ re COUNT(*) ]
>> On Informix however it is blindingly fast, and can also be instantly
>> conjured with the dbaccess tool (Info/Table/Status). They might be
>> stashing this count somewhere, but it is not available when the table
>> is locked, as during a load. However they do it, performance does not
>> seem to suffer, and having this rapidly available is certainly nice.
>> Especially when people are used to it.

> Informix locks rows during modification so they don't have the MVCC
> visibility problem we have (some rows are visible to only some
> backends).

More to the point: "performance does not seem to suffer" is an opinion
based on no facts. You have no idea what it's costing Informix to
maintain that count --- ie, how much faster might other things go if
COUNT(*) didn't have to be instant?

We know quite well what it would cost to make this happen in Postgres,
and it's the general judgment that we don't want to pay those costs ---
certainly not to force everyone to pay them.

There's some discussion in the pgsql-hackers archives about possible
add-on mechanisms to maintain COUNT(*) counts on tables for which the
DBA thinks it's justified. It seems clearly doable, but no one's gotten
excited enough to actually do it ... in the end, it seems that everyone
who's looked closely at their application has decided that it wasn't so
important after all.

regards, tom lane