Re: Deleting older versions in unique indexes to avoid page splits

On Thu, Nov 7, 2024 at 7:38 PM Andy Fan <zhihuifan1213(at)163(dot)com> wrote:
> If the delete goes with Index Scan of t_b_idx, we still have the chances
> to mark hints on t_b_idx, so that it can be useful during index split?

See for yourself, by using pageinspect. The bt_page_items function
returns a "dead" column, which will be true for index tuples that
already have their LP_DEAD bit set.

The exact rules for when LP_DEAD bits are set are a bit complicated,
and are hard to describe precisely. I don't think that your DELETE
statement will set any LP_DEAD bits, because the tuples won't be dead
until some time after the xact for the DELETE statement actually
commits -- it'd have to be some later SELECT statement that runs after
the DELETE commits (could be a DELETE statement instead of a SELECT
statement but SELECT is more typical).

This also has to happen during an index scan or an index-only scan --
bitmap scans don't do it. Plus there are some other obscure rules.

--
Peter Geoghegan