Re: vacuum, performance, and MVCC - Mailing list pgsql-hackers

Heikki Linnakangas wrote:
> On Sat, 24 Jun 2006, Bruce Momjian wrote:
> 
> > OK, I have an idea.  Right now, an UPDATE where the old and new rows are
> > on the same page have two index entries.  What if we made only one index
> > entry for both?  We already have UPDATE chaining, where the old row
> > points to the new one.  If no key columns change, we set a bit in the
> > heap that the chaining points to the old and new row (both on the same
> > page), so an index scan uses one index entry to see the old and new row,
> > and once the old row is no longer visible, the page index id can be
> > updated to point to the new row and the old row can be marked free and
> > perhaps used for a future UPDATE.  (UPDATE already tries to do keep
> > updates on the same heap page.)
> 
> In fact, that's what I originally thought Mark was suggesting. A couple of 
> points:
> 
> Why the limitation of old and new row being on the same page?

Because having them be on the same page is the only way you can update
the page item pointer so when you recycle the row, you the indexes are
now pointing to the new version.  Pages look like:
[marker][item1][item2][item3]...[tuple1][tuple2][tuple3]

and indexes only point to items, not to tuples.  This allows tuples to
be compacted on the page without affecting the indexes.

If tuple1 is updated to tuple2, once tuple1 is no longer visible to any
backends, you can modify item1 to point to tuple2, and you can mark the
space used by tuple1 as reusable:
[marker][item1(tuple2)][item2][item3]...[free][tuple2][tuple3]

> This only works if none of the updated columns are indexed. That's a bit 
> annoying. It would be nice to be able to create new index tuples in those 

The hope is that a commonly updated tuple will eventually be on a page
where there is sufficient free space for updated version to stay on
there.  For an active server, there might be several updated versions of
rows on the same page.

> indexes that contain one of the changed columns, but not in others.

If you can't expire the old row because one of the indexed columns was
modified, I see no reason to try to reduce the additional index entries.

> What happens if you create a new index that contains one of 
> the changed columns?

Uh, I had not thought of that.  You could easily create two index
entries for the old and new rows, but then the heap bit saying there is
only one index row would be inaccurate for the new index.  I suppose you
could create new rows in all indexes and clear the heap bit.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +