Heikki Linnakangas wrote:
> On Sat, 24 Jun 2006, Bruce Momjian wrote:
> > 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]
> Ok, now I think I get it. So the limitation of old and new tuple being on 
> the same page is required to make it possible to remove the old tuple 
> without touching the indexes?

Yes, modifying the heap page item pointer is required for reuse.

> If you move the new tuple (logically, by modifying item pointers) on 
> vacuum, isn't there a risk that a concurrent seqscan misses it?

Well, you lock the page while updating the item pointer.  Because the
versions are on the same page, a single page lock should be fine.

> > 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.
> It won't enable early expiration, but it means less work to do on update. 
> If there's a lot of indexes, not having to add so many index tuples can be 
> a significant saving.

Already added to TODO.

        * Reuse index tuples that point to heap tuples that are not visible to

> To summarise, we have two issues related to frequent updates:
> 1. Index and heap bloat, requiring frequent vacuum.
> 2. Updates are more expensive than on other DBMSs, because we have to add 
> a new index tuple in every index, even if none of the indexed columns are 
> modified.
> Tom suggested that we just improve vacuum and autovacuum, and someone 
> brought up the dead space map idea again. Those are all worthwhile things 
> to do and help with vacuuming after deletes as well as updates, but they 
> only address issue 1. Mark's suggestion (assuming that it would've 
> worked) as well as yours address both, but only for updates.


  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---------------------------(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

Reply via email to