Re: vacuum, performance, and MVCC

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Hannu Krosing <hannu(at)skype(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Csaba Nagy <nagy(at)ecircle-ag(dot)com>, Mark Woodward <pgsql(at)mohawksoft(dot)com>, "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>, Christopher Browne <cbbrowne(at)acm(dot)org>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: vacuum, performance, and MVCC
Date: 2006-06-25 19:21:27
Message-ID: 449EE237.2030009@Yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 6/25/2006 2:24 PM, Bruce Momjian wrote:

> Jan Wieck wrote:
>> >> Sure, but index reuse seems a lot easier, as there is nothing additional
>> >> to remember or clean out when doing it.
>> >
>> > Yes, seems so. TODO added:
>> >
>> > * Reuse index tuples that point to heap tuples that are not visible to
>> > anyone?
>> >
>> >> When reusing a heap tuple you have to clean out all index entries
>> >> pointing to it.
>> >
>> > Well, not for UPDATE for no key changes on the same page, if we do that.
>> >
>>
>> An update that results in all the same values of every indexed column of
>> a known deleted invisible tuple. This reused tuple can by definition not
>> be the one currently updated. So unless it is a table without a primary
>> key, this assumes that at least 3 versions of the same row exist within
>> the same block. How likely is that to happen?
>
> Good question. You take the current tuple, and make another one on the
> same page. Later, an update can reuse the original tuple if it is no
> longer visible to anyone (by changing the item id), so you only need two
> tuples, not three. My hope is that a repeated update would eventually
> move to a page that enough free space for two (or more) versions.
>
> Does that help explain it?
>

That's exactly what I meant. You need space for 3 or more tuple versions
within one page and the luck that one of them is invisible at the time
of the update. I don't know how likely or unlikely this is in reality,
but it doesn't sound very promising to me so far.

Another problem with this is that even if you find such row, it doesn't
spare you the index traversal. The dead row whos item id you're reusing
might have resulted from an insert that aborted or crashed before it
finished creating all index entries. Or some of its index entries might
already be flagged known dead, and you better reset those flags.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2006-06-25 19:42:01 Re: vacuum, performance, and MVCC
Previous Message Bruce Momjian 2006-06-25 18:52:44 Re: vacuum, performance, and MVCC