Richard Huxton <dev@archonet.com> writes:
> On Friday 08 Nov 2002 5:21 pm, Bruce Momjian wrote:
>> The problem with optimizing COUNT() is that different backends have
>> different tuple views, meaning the count from one backend could be
>> different than from another backend. I can't see how to optimize that.
> The only way I could model it when I thought about it some time ago was as
> though you had a separate table "pg_table_counts" with columns (tableoid,
> count) - every insert/delete would also update this table.
The problem with that is that it would create a serialization
bottleneck: if transaction A has done an insert into table X, then every
other transaction B that wants to insert or delete in X has to wait for
A to commit or abort before B can update X's row in pg_table_counts.
That is exactly the scenario that MVCC was designed to avoid.
What it comes down to is that you can optimize "select count(*) from
foo" at the expense of slowing down *every* kind of database-update
operation. We don't think that's a win.
regards, tom lane