At Fri, 22 Sep 2017 15:00:20 +0900, Masahiko Sawada <sawada.m...@gmail.com> wrote in <cad21aod6zgb1w6ps1axj0ccab_chdyiitntedpmhkefgg13...@mail.gmail.com> > On Tue, Sep 19, 2017 at 3:31 PM, Kyotaro HORIGUCHI > <horiguchi.kyot...@lab.ntt.co.jp> wrote: > > I was just looking the thread since it is found left alone for a > > long time in the CF app. > > > > At Mon, 18 Sep 2017 16:35:58 -0700, Peter Geoghegan <p...@bowt.ie> wrote in > > <CAH2-WzkhJhAXD+6DdBp7D8WYLfJ3D0m=AZbGsiw=usujtmu...@mail.gmail.com> > >> On Wed, Apr 5, 2017 at 3:50 PM, Andres Freund <and...@anarazel.de> wrote: > >> > Hi, > >> > > >> > On 2017-04-01 03:05:07 +0900, Masahiko Sawada wrote: > >> >> On Fri, Mar 31, 2017 at 11:44 PM, Robert Haas <robertmh...@gmail.com> > >> >> wrote: > >> >> [ lots of valuable discussion ] > >> > > >> > I think this patch clearly still is in the design stage, and has > >> > received plenty feedback this CF. I'll therefore move this to the next > >> > commitfest. > >> > >> Does anyone have ideas on a way forward here? I don't, but then I > >> haven't thought about it in detail in several months. > > > > Is the additional storage in metapage to store the current status > > of vaccum is still unacceptable even if it can avoid useless > > full-page scan on indexes especially for stable tables? > > > > Or, how about additional 1 bit in pg_stat_*_index to indicate > > that the index *don't* require vacuum cleanup stage. (default > > value causes cleanup) > > You meant that "the next cycle" is the lazy_cleanup_index() function > called by lazy_scan_heap()?
Both finally call btvacuumscan under a certain condition, but what I meant by "the next cycle" is the lazy_cleanup_index call in the next round of vacuum since abstract layer (index am) isn't conscious of the detail of btree. > > index_bulk_delete (or ambulkdelete) returns the flag in > > IndexBulkDeleteResult then lazy_scan_heap stores the flag in > > stats and in the next cycle it is looked up to decide the > > necessity of index cleanup. > > > > Could you elaborate about this? For example in btree index, the index > cleanup skips to scan on the index scan if index_bulk_delete has been > called during vacuuming because stats != NULL. So I think we don't > need such a flag. The flag works so that successive two index full scans don't happen in a vacuum round. If any rows are fully deleted, just following btvacuumcleanup does nothing. I think what you wanted to solve here was the problem that index_vacuum_cleanup runs a full scan even if it ends with no actual work, when manual or anti-wraparound vacuums. (I'm getting a bit confused on this..) It is caused by using the pointer "stats" as the flag to instruct to do that. If the stats-as-a-flag worked as expected, the GUC doesn't seem to be required. Addition to that, as Simon and Peter pointed out index_bulk_delete can leave not-fully-removed pages (so-called half-dead pages and pages that are recyclable but not registered in FSM, AFAICS) in some cases mainly by RecentGlobalXmin interlock. In this case, just inhibiting cleanup scan by a threshold lets such dangling pages persist in the index. (I conldn't make such a many dangling pages, though..) The first patch in the mail (*1) does that. It seems having some bugs, though.. Since the dangling pages persist until autovacuum decided to scan the belonging table again, we should run a vacuum round (or index_vacuum_cleanup itself) even having no dead rows if we want to clean up such pages within a certain period. The second patch doesn that. [*1] https://www.postgresql.org/message-id/20170921.174957.236914340.horiguchi.kyot...@lab.ntt.co.jp regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers