Re: [SQL] Yet Another (Simple) Case of Index not used

Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> This is why I suspect the best way to manage this would be to manage
> the counter itself using the MVCC mechanism (that is, you treat the
> shared counter as a row in a table just like any other and, in fact,
> it might be most beneficial for it to actually be exactly that), which
> handles the visibility problem automatically. But I don't know how
> much contention there would be as a result.

Hm. Contention probably wouldn't be the killer, since if transactions
don't try to update the count until they are about to commit, they won't
be holding the row lock for long. (You'd have to beware of deadlocks
between transactions that need to update multiple counters, but that
seems soluble.) What *would* be a problem is that such counter tables
would accumulate huge numbers of dead rows very quickly, making it
inefficient to find the live row. Josh already mentioned this as a
problem with user-trigger-based counting. You could stanch the bleeding
with sufficiently frequent vacuums, perhaps, but it just doesn't look
very appealing.

Ultimately what this comes down to is "how much overhead are we willing
to load onto all other operations in order to make SELECT-COUNT(*)-with-
no-WHERE-clause fast"? Postgres has made a set of design choices that
favor the other operations. If you've designed an application that
lives or dies by fast COUNT(*), perhaps you should choose another
database.

regards, tom lane