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

Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> You missed the point of Josh's comment: in an MVCC system, the correct
>> COUNT() varies depending on which transaction is asking.  Therefore it
>> is not possible for a centrally maintained row counter to give accurate
>> results to everybody, no matter how cheap it is to maintain.

> Hmm...true...but only if you really implement it as a faithful copy of
> the trigger-based method.
> [ instead have transactions save up net deltas to apply at commit ]

Good try, but it doesn't solve the problem.  SERIALIZABLE transactions
should not see deltas applied by any transaction that commits after
they start.  READ COMMITTED transactions can see such deltas --- but not
deltas applied since the start of their current statement.  (And there
could be several different "current statements" with different snapshots
in progress in a single READ COMMITTED transaction.)

AFAICS, central-counter techniques could only work in an MVCC system
if each transaction copies every counter in the system at each snapshot
freeze point, in case it finds itself needing that counter value later
on.  This is a huge amount of mostly-useless overhead, and it makes the
problem of lock contention for access to the counters several orders of
magnitude worse than you'd first think.

Of course you can dodge lots of this overhead if you're willing to
accept approximate answers.  But I don't believe that central counters
are useful in an exact-MVCC-semantics system.

            regards, tom lane