Peter, Matthias, thanks kindly for the good work on skipscans! While admiring the recent performance improvements, I found a test case which fails after commit 21a152b37f36c9563d1b0b058fe1436baf578b4c. Please find a reproducible test case, attached.
Thanks! On Fri, Apr 4, 2025 at 10:58 AM Peter Geoghegan <p...@bowt.ie> wrote: > Improve nbtree skip scan primitive scan scheduling. > > Don't allow nbtree scans with skip arrays to end any primitive scan on > its first leaf page without giving some consideration to how many times > the scan's arrays advanced while changing at least one skip array > (though continue not caring about the number of array advancements that > only affected SAOP arrays, even during skip scans with SAOP arrays). > Now when a scan performs more than 3 such array advancements in the > course of reading a single leaf page, it is taken as a signal that the > next page is unlikely to be skippable. We'll therefore continue the > ongoing primitive index scan, at least until we can perform a recheck > against the next page's finaltup. > > Testing has shown that this new heuristic occasionally makes all the > difference with skip scans that were expected to rely on the "passed > first page" heuristic added by commit 9a2e2a28. Without it, there is a > remaining risk that certain kinds of skip scans will never quite manage > to clear the initial hurdle of performing a primitive scan that lasts > beyond its first leaf page (or that such a skip scan will only clear > that initial hurdle when it has already wasted noticeably-many cycles > due to inefficient primitive scan scheduling). > > Follow-up to commits 92fe23d9 and 9a2e2a28. > > Author: Peter Geoghegan <p...@bowt.ie> > Reviewed-By: Matthias van de Meent <boekewurm+postg...@gmail.com> > Discussion: > https://postgr.es/m/CAH2-Wz=rvdg3zwytfwbsyw7fwh7zvefvthed5jkesutt0rc...@mail.gmail.com > > Branch > ------ > master > > Details > ------- > > https://git.postgresql.org/pg/commitdiff/21a152b37f36c9563d1b0b058fe1436baf578b4c > > Modified Files > -------------- > src/backend/access/nbtree/nbtsearch.c | 16 +++++++ > src/backend/access/nbtree/nbtutils.c | 90 > +++++++++++++++++++++++------------ > src/include/access/nbtree.h | 3 +- > 3 files changed, 78 insertions(+), 31 deletions(-) > > -- — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
v1-0001-Add-test-showing-a-problem-with-btree-index-scans.patch
Description: Binary data
