Re: decoupling table and index vacuum

On Fri, Feb 4, 2022 at 1:46 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> 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.

Thanks for checking my work here - I wasn't 100% sure I had the right idea.

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

Thanks for this tip, too.

--
Robert Haas
EDB: http://www.enterprisedb.com