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

Peter Geoghegan <pg(at)bowt(dot)ie> writes:

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

Yes, I can understand the above fact:) So I'm *not* talking about
"Simple deletion". I'm thinking if we can use "Bottom-Up deletion"
algirithom in some more places.

IIUC, the key factor of "bottom-up deletion" should be trigged (likely)
when *we know* there are some garbage in the leaf page, even we are not
sure these garbage can be clean at the index split time. So in my above
example, "simple deletion" doesn't work, but it is true that *we know*
there are some garbage in the leaf, should we consider "Bottom-Up
deletion" algirithom for that as well during index split? Per the commit
message or btree/README, looks such sistuation is not considered yet,
not sure how is the code.

--
Best Regards
Andy Fan