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?
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
indexes that contain one of the changed columns, but not in others.
What happens if you create a new index that contains one of
the changed columns?
- Heikki
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
