X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4BBE7475EE2 for ; Sat, 19 Apr 2003 23:34:27 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 70A714758C9 for ; Sat, 19 Apr 2003 23:34:19 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h3K3YOU6024736; Sat, 19 Apr 2003 23:34:24 -0400 (EDT) To: Kevin Brown Cc: pgsql-performance@postgresql.org Subject: Re: [SQL] Yet Another (Simple) Case of Index not used In-reply-to: <20030420011336.GJ1847@filer> References: <200304090918.45275.josh@agliodbs.com> <20030419130146.GI1847@filer> <21955.1050767928@sss.pgh.pa.us> <20030420011336.GJ1847@filer> Comments: In-reply-to Kevin Brown message dated "Sat, 19 Apr 2003 18:13:37 -0700" Date: Sat, 19 Apr 2003 23:34:24 -0400 Message-ID: <24735.1050809664@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/175 X-Sequence-Number: 1681 Kevin Brown 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