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 DFC6952EB3 for ; Thu, 11 Aug 2005 04:24:20 -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 19591-04 for ; Thu, 11 Aug 2005 07:24:17 +0000 (GMT) Received: from service-web.de (p15093784.pureserver.info [217.160.106.224]) by svr1.postgresql.org (Postfix) with ESMTP id 7546752EC0 for ; Thu, 11 Aug 2005 04:24:16 -0300 (ADT) Received: from [192.168.178.99] (p548B2D2C.dip0.t-ipconnect.de [84.139.45.44]) by service-web.de (Postfix) with ESMTP id 0078A200039; Thu, 11 Aug 2005 09:24:16 +0200 (CEST) Subject: Re: Speedier count(*) From: Tino Wildenhain To: Mark Cotner Cc: Gavin Sherry , Dan Harris , pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Date: Thu, 11 Aug 2005 09:24:08 +0200 Message-Id: <1123745049.27613.1.camel@Andrea.peacock.de> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.234 tagged_above=0 required=5 tests=[AWL=-0.023, FORGED_RCVD_HELO=0.05, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200508/111 X-Sequence-Number: 13858 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.