From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Cestmir Hybl <cestmirl@freeside.sk>
Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: count(*) using index scan in "query often, update rarely" environment
Date: Fri, 7 Oct 2005 09:07:15 -0400
Message-ID: <20051007130714.GB5175@surnet.cl> (raw)
In-Reply-To: <9fbe01c5cb30$4407d580$131fc39e@stratos>
References: <935801c5cb20$dcf93a00$131fc39e@stratos>
<20051007104816.GA8371@uio.no>
<9fbe01c5cb30$4407d580$131fc39e@stratos>
On Fri, Oct 07, 2005 at 01:14:20PM +0200, Cestmir Hybl wrote:
> collision: it's possible to either block updating transaction until
> index scan ends or discard index scan imediately and finish query using
> MVCC compliant scan
You can't change from one scan method to a different one on the fly.
There's no way to know which tuples have alreaady been returned.
Our index access methods are designed to be very concurrent, and it
works extremely well. One index scan being able to block an update
would destroy that advantage.
> dead rows: this sounds like more serious counter-argument, I don't know
> much about dead records management and whether it would be
> possible/worth to make indexes matching live records when there's no
> transaction in progress on that table
It's not possible, because a finishing transaction would have to clean
up every index it has used, and also any index it hasn't used but has
been modified by another transaction which couldn't clean up by itself
but didn't do the work because the first one was looking at the index.
It's easy to see that it's possible to create an unbounded number of
transactions, each forcing the other to do some index cleanup. This is
not acceptable.
Plus, it would be very hard to implement, and a very wide door to bugs.
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Et put se mouve" (Galileo Galilei)
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: alvherre@alvh.no-ip.org, cestmirl@freeside.sk, sgunderson@bigfoot.com
Subject: Re: count(*) using index scan in "query often, update rarely" environment
In-Reply-To: <20051007130714.GB5175@surnet.cl>
* 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