From: mark@mark.mielke.cc
To: pgsql-performance@postgresql.org
Subject: Re: count(*) using index scan in "query often, update rarely" environment
Date: Sat, 8 Oct 2005 09:34:32 -0400
Message-ID: <20051008133432.GC23913@mark.mielke.cc> (raw)
In-Reply-To: <20051007104816.GA8371@uio.no>
References: <935801c5cb20$dcf93a00$131fc39e@stratos>
<20051007104816.GA8371@uio.no>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@mark.mielke.cc
Subject: Re: count(*) using index scan in "query often, update rarely" environment
In-Reply-To: <20051008133432.GC23913@mark.mielke.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox