No, I can't speed-up evaluation of gen= eric=20 "count(*) where ()" queries this way.

no you can't speed up generic where(), *but* you can check what are the most common "where"'s (like usually i do where on one column like= :
select count(*) from table where some_particular_column =3D 'some value'; where you can simply make the trigger aware of the fact that it should coun= t based on value in some_particular_column.
works good enough for me not to look for alternatives.

depesz

------=_Part_25997_11295934.1128768249494-- From mark@mark.mielke.cc Sat Oct 8 13:48:21 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8B4B4D7E8D for ; Sat, 8 Oct 2005 10:48:21 -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 76922-10 for ; Sat, 8 Oct 2005 13:48:16 +0000 (GMT) Received: from mail.envyfinancial.com (mark.mielke.cc [206.248.142.186]) by svr1.postgresql.org (Postfix) with ESMTP id 2B57BD7E05 for ; Sat, 8 Oct 2005 10:48:19 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.envyfinancial.com (Postfix) with ESMTP id 6F6C3234B8 for ; Sat, 8 Oct 2005 09:34:32 -0400 (EDT) Received: from mail.envyfinancial.com ([127.0.0.1]) by localhost (mark.mielke.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24033-07 for ; Sat, 8 Oct 2005 09:34:32 -0400 (EDT) Received: by mail.envyfinancial.com (Postfix, from userid 500) id 43EC0234B3; Sat, 8 Oct 2005 09:34:32 -0400 (EDT) Date: Sat, 8 Oct 2005 09:34:32 -0400 From: mark@mark.mielke.cc To: pgsql-performance@postgresql.org Subject: Re: count(*) using index scan in "query often, update rarely" environment Message-ID: <20051008133432.GC23913@mark.mielke.cc> References: <935801c5cb20$dcf93a00$131fc39e@stratos> <20051007104816.GA8371@uio.no> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20051007104816.GA8371@uio.no> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: amavisd-new at mail.envyfinancial.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.216 required=5 tests=[AWL=-0.012, FORGED_RCVD_HELO=0.05, NO_REAL_NAME=0.178] X-Spam-Level: X-Archive-Number: 200510/154 X-Sequence-Number: 14922 On Fri, Oct 07, 2005 at 12:48:16PM +0200, Steinar H. Gunderson wrote: > On Fri, Oct 07, 2005 at 11:24:05AM +0200, Cestmir Hybl wrote: > > Isn't it possible (and reasonable) for these environments to keep track of > > whether there is a transaction in progress with update to given table and > > if not, use an index scan (count(*) where) or cached value (count(*)) to > > perform this kind of query? > Even if there is no running update, there might still be dead rows in the > table. In any case, of course, a new update could always be occurring while > your counting query was still running. I don't see this being different from count(*) as it is today. Updating a count column is certainly clever. If using a trigger, perhaps it would allow the equivalent of: select count(*) from table for update; :-) Cheers, mark (not that this is necessarily a good thing!) -- mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/