From: mark@mark.mielke.cc
To: Ivan Voras <ivoras@fer.hr>
Cc: pgsql-performance@postgresql.org
Subject: Re: MVCC & indexes?
Date: Tue, 31 Oct 2006 19:20:26 -0500
Message-ID: <20061101002025.GA21873@mark.mielke.cc> (raw)
In-Reply-To: <4547C65C.6090208@fer.hr>
References: <4547C65C.6090208@fer.hr>
On Tue, Oct 31, 2006 at 10:55:40PM +0100, Ivan Voras wrote:
> Ok, so MVCC is the best thing since a guy put a round stone on a stick
> and called it "the wheel", but I've seen several references on this list
> about "indexes not being under MVCC" - at least that's how I read it,
> the original posts were explaining why indexes can't be used for solving
> MIN()/MAX()/COUNT() aggregates. Is this correct?
> In particular, I'm trying to find out is there (b)locking involved when
> concurrently updating and/or inserting records in an indexed table. My
> guess is that, since PG does copy+delete on updating, even updating a
> non-indexed field will require fixups in the index tree (to point to the
> new record) and thus (b)locking.
Short bits of blocking. The PostgreSQL index 'problem', is that indexes
are conservative. They only guarantee to return at least as much data as
you should see. They cannot be used to limit what you see to only as much
as you should see.
Cheers,
mark
--
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, ivoras@fer.hr
Subject: Re: MVCC & indexes?
In-Reply-To: <20061101002025.GA21873@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