On Fri, Feb 4, 2022 at 1:15 PM Robert Haas <robertmh...@gmail.com> wrote: > My second thought was that perhaps we can create a test scenario > where, in one index, the deduplication and bottom-up index deletion > and kill_prior_tuple mechanisms are very effective, and in another > index, it's not effective at all. For example, maybe index A is an > index on the primary key, and index B is a non-unique index on some > column that we're updating with ever-increasing values (so that we > never put new tuples into a page that could be productively cleaned > up). I think what should happen in this case is that A should not grow > in size even if it's never vacuumed, while B will require vacuuming to > keep the size down.
That should work. All you need is a table with several indexes, and a workload consisting of updates that modify a column that is the key column for only one of the indexes. I would expect bottom-up index deletion to be 100% effective for the not-logically-modified indexes, in the sense that there will be no page splits -- provided there are no long held snapshots, and provided that the index isn't very small. If it is small (think of something like the pgbench_branches pkey), then even the occasional ANALYZE will act as a "long held snapshot" relative to the size of the index. And so then you might get one page split per original leaf page, but probably not a second, and very very probably not a third. The constantly modified index will be entirely dependent on index vacuuming here, and so an improved VACUUM design that allows that particular index to be vacuumed more frequently could really improve performance. BTW, it's a good idea to avoid unique indexes in test cases where there is an index that you don't want to set LP_DEAD bits for, since _bt_check_unique() tends to do a good job of setting LP_DEAD bits, independent of the kill_prior_tuple thing. You can avoid using kill_prior_tuple by forcing bitmap scans, of course. -- Peter Geoghegan
