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

On Thu, Nov 7, 2024 at 7:31 PM Andy Fan <zhihuifan1213(at)163(dot)com> wrote:
> With the (1) (2) (3), only non-hot UPDATE is considered.
>
> Then why do we need "unchanged index" as a prerequisite?

How else is the nbtree code ever supposed to know when it might be a
good idea to do a bottom-up index deletion pass? You could do it every
time the page looked like it might have to be split, but that would be
quite wasteful.

> OK. I'd try to understand why we need such snapshotConflictHorizon logic
> later. So can I understand the relationship between
> "snapshotConflictHorizon" and "(non-hot + unchanged index + update)" is
> [OR], which means *either* of them is true, bottom-to-up deletes
> will be triggered.

The need to generate a snapshotConflictHorizon exists for both kinds
of index deletion (and for HOT pruning, and for freezing). It's just
that we have to do external heap page accesses in the case of index
tuples. That cost is fixed (we need it to correctly perform deletion),
so we should "get the most out of" these heap page accesses (by
attempting to delete extra index tuples that happen to point to the
heap pages, and that are on the same leaf page as a page with LP_DEAD
bits set for some other index tuples).

--
Peter Geoghegan