pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMVCC & indexes?
4+ messages / 3 participants
[nested] [flat]
* MVCC & indexes?
@ 2006-10-31 21:55 Ivan Voras <ivoras@fer.hr>
0 siblings, 2 replies; 4+ messages in thread
From: Ivan Voras @ 2006-10-31 21:55 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MVCC & indexes?
@ 2006-10-31 22:36 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ivan Voras <ivoras@fer.hr>
1 sibling, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2006-10-31 22:36 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: pgsql-performance
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.
Well, there certainly is locking involved in inserting index entries,
but it's more fine-grained than you seem to think. Only one page of the
index is locked at any time, resulting in that typically there's very
little blocking involved. Two processes can be inserting into the same
index concurrently (btree and GiST indexes at least; GiST only gained
concurrency in a recent release, I don't remember if it was 8.0 or 8.1).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MVCC & indexes?
@ 2006-11-01 00:20 mark@mark.mielke.cc
parent: Ivan Voras <ivoras@fer.hr>
1 sibling, 0 replies; 4+ messages in thread
From: mark@mark.mielke.cc @ 2006-11-01 00:20 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MVCC & indexes?
@ 2006-11-01 12:06 Ivan Voras <ivoras@fer.hr>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ivan Voras @ 2006-11-01 12:06 UTC (permalink / raw)
To: pgsql-performance
Alvaro Herrera wrote:
> Well, there certainly is locking involved in inserting index entries,
> but it's more fine-grained than you seem to think. Only one page of the
> index is locked at any time, resulting in that typically there's very
> little blocking involved. Two processes can be inserting into the same
> index concurrently (btree and GiST indexes at least; GiST only gained
> concurrency in a recent release, I don't remember if it was 8.0 or 8.1).
Thank you, this was the bit I was missing. In retrospect, I don't really
know how I came to conclusion the whole index was being locked :(
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-01 12:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-31 21:55 MVCC & indexes? Ivan Voras <ivoras@fer.hr>
2006-10-31 22:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-11-01 12:06 ` Ivan Voras <ivoras@fer.hr>
2006-11-01 00:20 ` mark@mark.mielke.cc
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