Re: [HACKERS] GUC for cleanup indexes threshold.

On Wed, Nov 29, 2017 at 6:06 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
wrote:

> On Wed, Nov 29, 2017 at 11:05 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>
> wrote:
> > On 25 September 2017 at 22:34, Kyotaro HORIGUCHI
> > <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> >
> >>> > Here is a small patch that skips scanning btree index if no pending
> >>> > deleted pages exists.
> >>> > It detects this situation by comparing pages_deleted with pages_free.
> >>
> >> It seems to work to prevent needless cleanup scans.
> >
> > So this leaves us in the situation that
> >
> > 1. Masahiko's patch has unresolved problems
> > 2. Yura's patch works and is useful
> >
> > Unless there is disagreement on the above, it seems we should apply
> > Yura's patch (an edited version, perhaps).
> >
>
> IIRC the patches that makes the cleanup scan skip has a problem
> pointed by Peter[1], that is that we stash an XID when a btree page is
> deleted, which is used to determine when it's finally safe to recycle
> the page. Yura's patch doesn't have that problem?
>
> [1] https://www.postgresql.org/message-id/CAH2-Wz%3D1%
> 3Dt5fcGGfarQGcAWBqaCh%2BdLMjpYCYHpEyzK8Qg6OrQ%40mail.gmail.com

Yes, I think Yura's patch doesn't have that problem, because it skips
cleanup only when there are no recyclable pages. And that means there
is no btpo.xact stored, so no XIDs to be wraparounded.

BTW, I've rebased Yura's patch. I think this patch has following issues
for now:

1) Adding BTP_NEED_NO_CLEANUP as per-page flag doesn't look nice.
2) In the append-only case, index statistics can lag indefinitely.
3) Patch definitely needs more comments.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company