Ühel kenal päeval, E, 2006-06-26 kell 23:08, kirjutas Bruce Momjian: > Jim C. Nasby wrote: > > On Mon, Jun 26, 2006 at 02:32:59PM -0400, Bruce Momjian wrote: > > > > > > It is certainly possible to do what you are suggesting, that is have two > > > index entries point to same chain head, and have the index access > > > routines figure out if the index qualifications still hold, but that > > > seems like a lot of overhead.
I think Jim meant not 2 pointing to the same head, but 2 pointing into the same chain. Say we have table with (id serial, ts timestamp) where ts changes at each update and id does not. So after 3 updates on one page we have one CITC/ITPC head with pointers from both indexes and two follow-up tuples with pointers from only ts index. The problem with this setup is, that we can't reuse any of those follow-up tuples without index cleanup. > > > Also, once there is only one visible row in the chain, removing old > > > index entries seems quite complex because you have to have vacuum keep > > > the qualifications of each row to figure out which index tuple is the > > > valid one (seems messy). > > > > Perhaps my point got lost... in the case where no index keys change > > during an update, SITC seems superior in every way to my proposal. My > > idea (let's call it Index Tuple Page Consolidation, ITPC) would be > > beneficial to UPDATEs that modify one or more index keys but still put > > the tuple on the same page. Where SITC would be most useful for tables > > that have a very heavy update rate and very few indexes, ITPC would > > benefit tables that have more indexes on them; where presumably it's > > much more likely for UPDATEs to change at least one index key (which > > means SITC goes out the window, if I understand it correctly). If I'm > > missing something and SITC can in fact deal with some index keys > > changing during an UPDATE, then I see no reason for ITPC. > > I understood what you had said. The question is whether we want to get > that complex with this feature, and if there are enough use cases > (UPDATE with index keys changing) to warrant it. I'd like to think that most heavily-updated tables avoid that, but there may be still cases where this is needed. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
