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

ср, 25 нояб. 2020 г. в 19:41, Peter Geoghegan <pg(at)bowt(dot)ie>:

> We have promising tuples for bottom-up deletion. Why not have
> "promising heap blocks" for traditional LP_DEAD index tuple deletion?
> Or if you prefer, we can consider index tuples that *don't* have their
> LP_DEAD bits set already but happen to point to the *same heap block*
> as other tuples that *do* have their LP_DEAD bits set promising. (The
> tuples with their LP_DEAD bits set are not just promising -- they're
> already a sure thing.)
>

In the _bt_delete_or_dedup_one_page() we start with the simple loop over
items on the page and
if there're any LP_DEAD tuples, we're kicking off _bt_delitems_delete().

So if I understood you right, you plan to make this loop (or a similar one
somewhere around)
to track TIDs of the LP_DEAD tuples and then (perhaps on a second loop over
the page) compare all other
currently-not-LP_DEAD tuples and mark those pages, that have at least 2
TIDs pointing at (one LP_DEAD and other not)
as a promising one.

Later, should we require to kick deduplication, we'll go visit promising
pages first.

Is my understanding correct?

> I am missing a general perspective here.
> >
> > Is it true, that despite the long (vacuum preventing) transaction we can
> re-use space,
> > as after the DELETE statements commits, IndexScans are setting LP_DEAD
> hints after
> > they check the state of the corresponding heap tuple?
>
> The enhancement to traditional LP_DEAD deletion that I just described
> does not affect the current restrictions on setting LP_DEAD bits in
> the presence of a long-running transaction, or anything like that.
> That seems like an unrelated project. The value of this enhancement is
> purely its ability to delete *extra* index tuples that could have had
> their LP_DEAD bits set already (it was possible in principle), but
> didn't. And only when they are nearby to index tuples that really do
> have their LP_DEAD bits set.
>

I wasn't considering improvements here, that was a general question about
how this works
(trying to clear up gaps in my understanding).

What I meant to ask — will LP_DEAD be set by IndexScan in the presence of
the long transaction?

--
Victor Yegorov