From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Brown <kevin@sysexperts.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Sat, 19 Apr 2003 23:34:24 -0400
Message-ID: <24735.1050809664@sss.pgh.pa.us> (raw)
In-Reply-To: <20030420011336.GJ1847@filer>
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
<200304090918.45275.josh@agliodbs.com>
<20030419130146.GI1847@filer>
<21955.1050767928@sss.pgh.pa.us>
<20030420011336.GJ1847@filer>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
In-Reply-To: <24735.1050809664@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox