X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F040C52C7B for ; Thu, 11 Aug 2005 07:37:17 -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 48389-04 for ; Thu, 11 Aug 2005 10:37:13 +0000 (GMT) Received: from linuxworld.com.au (unknown [203.34.46.50]) by svr1.postgresql.org (Postfix) with ESMTP id 9783D52C57 for ; Thu, 11 Aug 2005 07:37:11 -0300 (ADT) Received: from linuxworld.com.au (IDENT:swm@localhost.localdomain [127.0.0.1]) by linuxworld.com.au (8.13.2/8.13.2) with ESMTP id j7BAaxYu001724; Thu, 11 Aug 2005 20:36:59 +1000 Received: from localhost (swm@localhost) by linuxworld.com.au (8.13.2/8.13.2/Submit) with ESMTP id j7BAawZg001721; Thu, 11 Aug 2005 20:36:58 +1000 X-Authentication-Warning: linuxworld.com.au: swm owned process doing -bs Date: Thu, 11 Aug 2005 20:36:58 +1000 (EST) From: Gavin Sherry X-X-Sender: swm@linuxworld.com.au To: Tino Wildenhain Cc: Mark Cotner , Dan Harris , pgsql-performance@postgresql.org Subject: Re: Speedier count(*) In-Reply-To: <1123745049.27613.1.camel@Andrea.peacock.de> Message-ID: References: <1123745049.27613.1.camel@Andrea.peacock.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=[AWL=0.002] X-Spam-Level: X-Archive-Number: 200508/112 X-Sequence-Number: 13859 On Thu, 11 Aug 2005, Tino Wildenhain wrote: > Am Donnerstag, den 11.08.2005, 00:40 -0400 schrieb Mark Cotner: > > Here's a trigger I wrote to perform essentially the same purpose. The nice > > thing about this is it keeps the number up to date for you, but you do incur > > slight overhead. > ... > > > > CREATE TRIGGER del_rowcount_tr BEFORE DELETE ON test FOR EACH ROW EXECUTE > > PROCEDURE del_rowcount(); > > CREATE TRIGGER add_rowcount_tr BEFORE INSERT ON test FOR EACH ROW EXECUTE > > PROCEDURE add_rowcount(); > > > > INSERT INTO rowcount (tablename) VALUES ('test'); > ... > > beware of problems with concurrency and even what happens > if transactions roll back. Maybe you can "fix" it a bit > by regulary correcting the count via cronjob or so. What problems? MVCC takes care of this. Gavin