Ühel kenal päeval, P, 2006-06-25 kell 14:24, kirjutas Bruce Momjian: > 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.
I can confirm that this is exactly what happens when running an update-heavy load with frequent vacuums. Eventually most rows get their own db pages or share the same page with 2-3 rows. And there will be lots of unused (filed up, or cleaned and not yet reused) pages. The overall performance could be made a little better by tuning the system to not put more than N new rows on the same page at initial insert or when the row move to a new page during update. Currently several new rows are initially put on the same page and then move around during repeated updates until they slow(ish)ly claim their own page. > Does that help explain it? > -- ---------------- 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 2: Don't 'kill -9' the postmaster
