X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C8E6852AD6 for ; Fri, 8 Jul 2005 12:41:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21002-08 for ; Fri, 8 Jul 2005 15:41:46 +0000 (GMT) Received: from keymaster.look.ca (delta1.look.ca [207.136.80.9]) by svr1.postgresql.org (Postfix) with ESMTP id 6FB4352A72 for ; Fri, 8 Jul 2005 12:41:45 -0300 (ADT) Received: from 216-154-13-109.dsl.look.ca ([216.154.13.109]) by keymaster.look.ca with esmtp (Exim 4.20) id 1Dquz2-0002f5-JX; Fri, 08 Jul 2005 15:41:44 +0000 From: Rod Taylor To: Dawid Kuroczko Cc: Steve Wampler , pgsql-sql@postgresql.org, Bruno Wolff III In-Reply-To: <758d5e7f050708083423c20c70@mail.gmail.com> References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com> <60vf3mklue.fsf@dba2.int.libertyrms.com> <42CE89CA.2070607@noao.edu> <20050708143508.GD22725@wolff.to> <42CE96AF.3020903@noao.edu> <1120836150.24708.251.camel@home> <758d5e7f050708083423c20c70@mail.gmail.com> Date: Fri, 08 Jul 2005 11:38:31 -0400 Message-Id: <1120837111.24708.252.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 FreeBSD GNOME Team Port X-SA-Exim-Mail-From: pg@rbt.ca Subject: Re: Make COUNT(*) Faster? Content-Type: text/plain Content-Transfer-Encoding: 7bit X-SA-Exim-Version: 3.1 (built Tue Feb 24 05:09:27 GMT 2004) X-SA-Exim-Scanned: Yes X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.036 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200507/78 X-Sequence-Number: 22056 On Fri, 2005-07-08 at 17:34 +0200, Dawid Kuroczko wrote: > On 7/8/05, Rod Taylor wrote: > > Create 2 sequences, one for counting tuple additions and one for > > counting tuple deletions. > > > > When you INSERT a tuple, bump the "added" sequence (select nextval()); > > > > When you DELETE a tuple, bump the "deleted" sequence (select nextval()); > > > > To retrieve an approximate count, take the current value of both > > sequences (select directly -- don't use currval) and subtract the > > "deletes" from the "adds". > > Never thought of that! Good idea. :-) > > Regards, > Dawid > > PS: There aren't any on ROLLBACK triggers, right? ;-))) No. You could make then RI triggers and have them deferred until commit though. --