public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v36 4/6] Index skip scan 3+ messages / 3 participants [nested] [flat]
* [PATCH v36 4/6] Index skip scan @ 2020-06-08 18:39 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dmitrii Dolgov @ 2020-06-08 18:39 UTC (permalink / raw) Implementation of basic Index Skip Scan (see Loose Index Scan in the wiki [1]) infrastructure on top of IndexOnlyScan and IndexScan. Introduces a new index am function amskip to allow advance past duplicate key values in a scan. This innocently looking description could be a bit tricky on the edge between am specific and common parts of the implementation, mostly due to different information available at each level, e.g. visibility. This means the common parts should apply skipping multiple times if necessary. Original patch and design were proposed by Thomas Munro [2], revived and improved by Dmitry Dolgov and Jesper Pedersen. [1] https://wiki.postgresql.org/wiki/Loose_indexscan [2] https://www.postgresql.org/message-id/flat/CADLWmXXbTSBxP-MzJuPAYSsL_2f0iPm5VWPbCvDbVvfX93FKkw%40mai... Author: Jesper Pedersen, Dmitry Dolgov Reviewed-by: Thomas Munro, David Rowley, Floris Van Nee, Kyotaro Horiguchi, Tomas Vondra, Peter Geoghegan --- contrib/bloom/blutils.c | 1 + doc/src/sgml/indexam.sgml | 1 + src/backend/access/brin/brin.c | 1 + src/backend/access/gin/ginutil.c | 1 + src/backend/access/gist/gist.c | 1 + src/backend/access/hash/hash.c | 1 + src/backend/access/index/indexam.c | 18 ++ src/backend/access/spgist/spgutils.c | 1 + src/backend/commands/explain.c | 25 +++ src/backend/executor/nodeIndexonlyscan.c | 97 ++++++++- src/backend/executor/nodeIndexscan.c | 56 +++++- src/backend/nodes/copyfuncs.c | 2 + src/backend/nodes/outfuncs.c | 2 + src/backend/nodes/readfuncs.c | 2 + src/backend/optimizer/path/costsize.c | 1 + src/backend/optimizer/path/indxpath.c | 187 +++++++++++++++++- src/backend/optimizer/path/pathkeys.c | 54 ++++- src/backend/optimizer/plan/createplan.c | 20 +- src/backend/optimizer/util/pathnode.c | 37 ++++ src/backend/optimizer/util/plancat.c | 1 + src/backend/utils/misc/guc.c | 9 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/amapi.h | 8 + src/include/access/genam.h | 2 + src/include/access/sdir.h | 7 + src/include/nodes/execnodes.h | 6 + src/include/nodes/pathnodes.h | 4 + src/include/nodes/plannodes.h | 4 + src/include/optimizer/cost.h | 1 + src/include/optimizer/pathnode.h | 4 + src/include/optimizer/paths.h | 5 +- src/test/regress/expected/sysviews.out | 3 +- 32 files changed, 546 insertions(+), 17 deletions(-) diff --git a/contrib/bloom/blutils.c b/contrib/bloom/blutils.c index d3bf8665df..4eec21b99e 100644 --- a/contrib/bloom/blutils.c +++ b/contrib/bloom/blutils.c @@ -134,6 +134,7 @@ blhandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = blbulkdelete; amroutine->amvacuumcleanup = blvacuumcleanup; amroutine->amcanreturn = NULL; + amroutine->amskip = NULL; amroutine->amcostestimate = blcostestimate; amroutine->amoptions = bloptions; amroutine->amproperty = NULL; diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml index af87f172a7..8c552c22bd 100644 --- a/doc/src/sgml/indexam.sgml +++ b/doc/src/sgml/indexam.sgml @@ -150,6 +150,7 @@ typedef struct IndexAmRoutine amendscan_function amendscan; ammarkpos_function ammarkpos; /* can be NULL */ amrestrpos_function amrestrpos; /* can be NULL */ + amskip_function amskip; /* can be NULL */ /* interface functions to support parallel index scans */ amestimateparallelscan_function amestimateparallelscan; /* can be NULL */ diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index 7db3ae5ee0..e3e09efa31 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -115,6 +115,7 @@ brinhandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = brinbulkdelete; amroutine->amvacuumcleanup = brinvacuumcleanup; amroutine->amcanreturn = NULL; + amroutine->amskip = NULL; amroutine->amcostestimate = brincostestimate; amroutine->amoptions = brinoptions; amroutine->amproperty = NULL; diff --git a/src/backend/access/gin/ginutil.c b/src/backend/access/gin/ginutil.c index a400f1fedb..7e83a8ee58 100644 --- a/src/backend/access/gin/ginutil.c +++ b/src/backend/access/gin/ginutil.c @@ -66,6 +66,7 @@ ginhandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = ginbulkdelete; amroutine->amvacuumcleanup = ginvacuumcleanup; amroutine->amcanreturn = NULL; + amroutine->amskip = NULL; amroutine->amcostestimate = gincostestimate; amroutine->amoptions = ginoptions; amroutine->amproperty = NULL; diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index 79fe6eb8d6..6fe9f8f5a9 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -87,6 +87,7 @@ gisthandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = gistbulkdelete; amroutine->amvacuumcleanup = gistvacuumcleanup; amroutine->amcanreturn = gistcanreturn; + amroutine->amskip = NULL; amroutine->amcostestimate = gistcostestimate; amroutine->amoptions = gistoptions; amroutine->amproperty = gistproperty; diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c index 3ec6d528e7..85d44ee58d 100644 --- a/src/backend/access/hash/hash.c +++ b/src/backend/access/hash/hash.c @@ -84,6 +84,7 @@ hashhandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = hashbulkdelete; amroutine->amvacuumcleanup = hashvacuumcleanup; amroutine->amcanreturn = NULL; + amroutine->amskip = NULL; amroutine->amcostestimate = hashcostestimate; amroutine->amoptions = hashoptions; amroutine->amproperty = NULL; diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c index 6b9750c244..c7b70997ff 100644 --- a/src/backend/access/index/indexam.c +++ b/src/backend/access/index/indexam.c @@ -33,6 +33,7 @@ * index_can_return - does index support index-only scans? * index_getprocid - get a support procedure OID * index_getprocinfo - get a support procedure's lookup info + * index_skip - advance past duplicate key values in a scan * * NOTES * This file contains the index_ routines which used @@ -736,6 +737,23 @@ index_can_return(Relation indexRelation, int attno) return indexRelation->rd_indam->amcanreturn(indexRelation, attno); } +/* ---------------- + * index_skip + * + * Skip past all tuples where the first 'prefix' columns have the + * same value as the last tuple returned in the current scan. + * ---------------- + */ +bool +index_skip(IndexScanDesc scan, ScanDirection direction, + ScanDirection indexdir, bool scanstart, int prefix) +{ + SCAN_CHECKS; + + return scan->indexRelation->rd_indam->amskip(scan, direction, + indexdir, scanstart, prefix); +} + /* ---------------- * index_getprocid * diff --git a/src/backend/access/spgist/spgutils.c b/src/backend/access/spgist/spgutils.c index 0efe05e552..0491ea172a 100644 --- a/src/backend/access/spgist/spgutils.c +++ b/src/backend/access/spgist/spgutils.c @@ -69,6 +69,7 @@ spghandler(PG_FUNCTION_ARGS) amroutine->ambulkdelete = spgbulkdelete; amroutine->amvacuumcleanup = spgvacuumcleanup; amroutine->amcanreturn = spgcanreturn; + amroutine->amskip = NULL; amroutine->amcostestimate = spgcostestimate; amroutine->amoptions = spgoptions; amroutine->amproperty = spgproperty; diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index a283e4d45c..3c6258761a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -148,6 +148,7 @@ static void ExplainXMLTag(const char *tagname, int flags, ExplainState *es); static void ExplainIndentText(ExplainState *es); static void ExplainJSONLineEnding(ExplainState *es); static void ExplainYAMLLineStarting(ExplainState *es); +static void ExplainIndexSkipScanKeys(int skipPrefixSize, ExplainState *es); static void escape_yaml(StringInfo buf, const char *str); @@ -1096,6 +1097,22 @@ ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used) return planstate_tree_walker(planstate, ExplainPreScanNode, rels_used); } +/* + * ExplainIndexSkipScanKeys - + * Append information about index skip scan to es->str. + * + * Can be used to print the skip prefix size. + */ +static void +ExplainIndexSkipScanKeys(int skipPrefixSize, ExplainState *es) +{ + if (skipPrefixSize > 0) + { + if (es->format != EXPLAIN_FORMAT_TEXT) + ExplainPropertyInteger("Distinct Prefix", NULL, skipPrefixSize, es); + } +} + /* * ExplainNode - * Appends a description of a plan tree to es->str @@ -1433,6 +1450,8 @@ ExplainNode(PlanState *planstate, List *ancestors, { IndexScan *indexscan = (IndexScan *) plan; + ExplainIndexSkipScanKeys(indexscan->indexskipprefixsize, es); + ExplainIndexScanDetails(indexscan->indexid, indexscan->indexorderdir, es); @@ -1443,6 +1462,8 @@ ExplainNode(PlanState *planstate, List *ancestors, { IndexOnlyScan *indexonlyscan = (IndexOnlyScan *) plan; + ExplainIndexSkipScanKeys(indexonlyscan->indexskipprefixsize, es); + ExplainIndexScanDetails(indexonlyscan->indexid, indexonlyscan->indexorderdir, es); @@ -1703,6 +1724,8 @@ ExplainNode(PlanState *planstate, List *ancestors, switch (nodeTag(plan)) { case T_IndexScan: + if (((IndexScan *) plan)->indexskipprefixsize > 0) + ExplainPropertyBool("Skip scan", true, es); show_scan_qual(((IndexScan *) plan)->indexqualorig, "Index Cond", planstate, ancestors, es); if (((IndexScan *) plan)->indexqualorig) @@ -1716,6 +1739,8 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); break; case T_IndexOnlyScan: + if (((IndexOnlyScan *) plan)->indexskipprefixsize > 0) + ExplainPropertyBool("Skip scan", true, es); show_scan_qual(((IndexOnlyScan *) plan)->indexqual, "Index Cond", planstate, ancestors, es); if (((IndexOnlyScan *) plan)->indexqual) diff --git a/src/backend/executor/nodeIndexonlyscan.c b/src/backend/executor/nodeIndexonlyscan.c index 5617ac29e7..c4e4b087a7 100644 --- a/src/backend/executor/nodeIndexonlyscan.c +++ b/src/backend/executor/nodeIndexonlyscan.c @@ -41,6 +41,7 @@ #include "miscadmin.h" #include "storage/bufmgr.h" #include "storage/predicate.h" +#include "storage/itemptr.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -62,9 +63,26 @@ IndexOnlyNext(IndexOnlyScanState *node) EState *estate; ExprContext *econtext; ScanDirection direction; + ScanDirection readDirection; IndexScanDesc scandesc; TupleTableSlot *slot; ItemPointer tid; + ItemPointerData startTid; + IndexOnlyScan *indexonlyscan = (IndexOnlyScan *) node->ss.ps.plan; + + /* + * Tells if the current position was reached via skipping. In this case + * there is no nead for the index_getnext_tid + */ + bool skipped = false; + + /* + * Index only scan must be aware that in case of skipping we can return to + * the starting point due to visibility checks. In this situation we need + * to jump further, and number of skipping attempts tell us how far do we + * need to do so. + */ + int skipAttempts = 0; /* * extract necessary information from index scan node @@ -72,7 +90,7 @@ IndexOnlyNext(IndexOnlyScanState *node) estate = node->ss.ps.state; direction = estate->es_direction; /* flip direction if this is an overall backward scan */ - if (ScanDirectionIsBackward(((IndexOnlyScan *) node->ss.ps.plan)->indexorderdir)) + if (ScanDirectionIsBackward(indexonlyscan->indexorderdir)) { if (ScanDirectionIsForward(direction)) direction = BackwardScanDirection; @@ -114,16 +132,87 @@ IndexOnlyNext(IndexOnlyScanState *node) node->ioss_OrderByKeys, node->ioss_NumOrderByKeys); } + else + { + ItemPointerCopy(&scandesc->xs_heaptid, &startTid); + } + + /* + * Check if we need to skip to the next key prefix, because we've been + * asked to implement DISTINCT. + * + * When fetching a cursor in the direction opposite to a general scan + * direction, the result must be what normal fetching should have + * returned, but in reversed order. In other words, return the last or + * first scanned tuple in a DISTINCT set, depending on a cursor direction. + * Due to that we skip also when the first tuple wasn't emitted yet, but + * the directions are opposite. + */ + if (node->ioss_SkipPrefixSize > 0 && + (node->ioss_FirstTupleEmitted || + ScanDirectionsAreOpposite(direction, indexonlyscan->indexorderdir))) + { + if (!index_skip(scandesc, direction, indexonlyscan->indexorderdir, + !node->ioss_FirstTupleEmitted, node->ioss_SkipPrefixSize)) + { + /* + * Reached end of index. At this point currPos is invalidated, and + * we need to reset ioss_FirstTupleEmitted, since otherwise after + * going backwards, reaching the end of index, and going forward + * again we apply skip again. It would be incorrect and lead to an + * extra skipped item. + */ + node->ioss_FirstTupleEmitted = false; + return ExecClearTuple(slot); + } + else + { + skipAttempts = 1; + skipped = true; + tid = &scandesc->xs_heaptid; + } + } + + readDirection = skipped ? indexonlyscan->indexorderdir : direction; /* * OK, now that we have what we need, fetch the next tuple. */ - while ((tid = index_getnext_tid(scandesc, direction)) != NULL) + while (skipped || (tid = index_getnext_tid(scandesc, readDirection)) != NULL) { bool tuple_from_heap = false; CHECK_FOR_INTERRUPTS(); + /* + * While doing index only skip scan with advancing and reading in + * different directions we can return to the same position where we + * started after visibility check. Recognize such situations and skip + * more. + */ + if ((readDirection != direction) && + ItemPointerIsValid(&startTid) && ItemPointerEquals(&startTid, tid)) + { + int i; + skipAttempts += 1; + + for (i = 0; i < skipAttempts; i++) + { + if (!index_skip(scandesc, direction, + indexonlyscan->indexorderdir, + !node->ioss_FirstTupleEmitted, + node->ioss_SkipPrefixSize)) + { + node->ioss_FirstTupleEmitted = false; + return ExecClearTuple(slot); + } + } + + tid = &scandesc->xs_heaptid; + } + + skipped = false; + /* * We can skip the heap fetch if the TID references a heap page on * which all tuples are known visible to everybody. In any case, @@ -250,6 +339,8 @@ IndexOnlyNext(IndexOnlyScanState *node) ItemPointerGetBlockNumber(tid), estate->es_snapshot); + node->ioss_FirstTupleEmitted = true; + return slot; } @@ -504,6 +595,8 @@ ExecInitIndexOnlyScan(IndexOnlyScan *node, EState *estate, int eflags) indexstate->ss.ps.plan = (Plan *) node; indexstate->ss.ps.state = estate; indexstate->ss.ps.ExecProcNode = ExecIndexOnlyScan; + indexstate->ioss_SkipPrefixSize = node->indexskipprefixsize; + indexstate->ioss_FirstTupleEmitted = false; /* * Miscellaneous initialization diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c index d0a96a38e0..449aaec3ac 100644 --- a/src/backend/executor/nodeIndexscan.c +++ b/src/backend/executor/nodeIndexscan.c @@ -85,6 +85,13 @@ IndexNext(IndexScanState *node) ScanDirection direction; IndexScanDesc scandesc; TupleTableSlot *slot; + IndexScan *indexscan = (IndexScan *) node->ss.ps.plan; + + /* + * tells if the current position was reached via skipping. In this case + * there is no nead for the index_getnext_tid + */ + bool skipped = false; /* * extract necessary information from index scan node @@ -92,7 +99,7 @@ IndexNext(IndexScanState *node) estate = node->ss.ps.state; direction = estate->es_direction; /* flip direction if this is an overall backward scan */ - if (ScanDirectionIsBackward(((IndexScan *) node->ss.ps.plan)->indexorderdir)) + if (ScanDirectionIsBackward(indexscan->indexorderdir)) { if (ScanDirectionIsForward(direction)) direction = BackwardScanDirection; @@ -117,6 +124,12 @@ IndexNext(IndexScanState *node) node->iss_ScanDesc = scandesc; + /* Index skip scan assumes xs_want_itup, so set it to true */ + if (indexscan->indexskipprefixsize > 0) + node->iss_ScanDesc->xs_want_itup = true; + else + node->iss_ScanDesc->xs_want_itup = false; + /* * If no run-time keys to calculate or they are ready, go ahead and * pass the scankeys to the index AM. @@ -127,12 +140,48 @@ IndexNext(IndexScanState *node) node->iss_OrderByKeys, node->iss_NumOrderByKeys); } + /* + * Check if we need to skip to the next key prefix, because we've been + * asked to implement DISTINCT. + * + * When fetching a cursor in the direction opposite to a general scan + * direction, the result must be what normal fetching should have + * returned, but in reversed order. In other words, return the last or + * first scanned tuple in a DISTINCT set, depending on a cursor direction. + * Due to that we skip also when the first tuple wasn't emitted yet, but + * the directions are opposite. + */ + if (node->iss_SkipPrefixSize > 0 && + (node->iss_FirstTupleEmitted || + ScanDirectionsAreOpposite(direction, indexscan->indexorderdir))) + { + if (!index_skip(scandesc, direction, indexscan->indexorderdir, + !node->iss_FirstTupleEmitted, node->iss_SkipPrefixSize)) + { + /* + * Reached end of index. At this point currPos is invalidated, and + * we need to reset iss_FirstTupleEmitted, since otherwise after + * going backwards, reaching the end of index, and going forward + * again we apply skip again. It would be incorrect and lead to an + * extra skipped item. + */ + node->iss_FirstTupleEmitted = false; + return ExecClearTuple(slot); + } + else + { + skipped = true; + index_fetch_heap(scandesc, slot); + } + } + /* * ok, now that we have what we need, fetch the next tuple. */ - while (index_getnext_slot(scandesc, direction, slot)) + while (skipped || index_getnext_slot(scandesc, direction, slot)) { CHECK_FOR_INTERRUPTS(); + skipped = false; /* * If the index was lossy, we have to recheck the index quals using @@ -149,6 +198,7 @@ IndexNext(IndexScanState *node) } } + node->iss_FirstTupleEmitted = true; return slot; } @@ -910,6 +960,8 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags) indexstate->ss.ps.plan = (Plan *) node; indexstate->ss.ps.state = estate; indexstate->ss.ps.ExecProcNode = ExecIndexScan; + indexstate->iss_SkipPrefixSize = node->indexskipprefixsize; + indexstate->iss_FirstTupleEmitted = false; /* * Miscellaneous initialization diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1f50400fd2..829a1a7c6c 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -493,6 +493,7 @@ _copyIndexScan(const IndexScan *from) COPY_NODE_FIELD(indexorderbyorig); COPY_NODE_FIELD(indexorderbyops); COPY_SCALAR_FIELD(indexorderdir); + COPY_SCALAR_FIELD(indexskipprefixsize); return newnode; } @@ -518,6 +519,7 @@ _copyIndexOnlyScan(const IndexOnlyScan *from) COPY_NODE_FIELD(indexorderby); COPY_NODE_FIELD(indextlist); COPY_SCALAR_FIELD(indexorderdir); + COPY_SCALAR_FIELD(indexskipprefixsize); return newnode; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index c3a9632992..88254446ce 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -562,6 +562,7 @@ _outIndexScan(StringInfo str, const IndexScan *node) WRITE_NODE_FIELD(indexorderbyorig); WRITE_NODE_FIELD(indexorderbyops); WRITE_ENUM_FIELD(indexorderdir, ScanDirection); + WRITE_INT_FIELD(indexskipprefixsize); } static void @@ -576,6 +577,7 @@ _outIndexOnlyScan(StringInfo str, const IndexOnlyScan *node) WRITE_NODE_FIELD(indexorderby); WRITE_NODE_FIELD(indextlist); WRITE_ENUM_FIELD(indexorderdir, ScanDirection); + WRITE_INT_FIELD(indexskipprefixsize); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 3a18571d0c..9766fc11ed 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1829,6 +1829,7 @@ _readIndexScan(void) READ_NODE_FIELD(indexorderbyorig); READ_NODE_FIELD(indexorderbyops); READ_ENUM_FIELD(indexorderdir, ScanDirection); + READ_INT_FIELD(indexskipprefixsize); READ_DONE(); } @@ -1848,6 +1849,7 @@ _readIndexOnlyScan(void) READ_NODE_FIELD(indexorderby); READ_NODE_FIELD(indextlist); READ_ENUM_FIELD(indexorderdir, ScanDirection); + READ_INT_FIELD(indexskipprefixsize); READ_DONE(); } diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 945aa93374..fe6ef62e8f 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -125,6 +125,7 @@ int max_parallel_workers_per_gather = 2; bool enable_seqscan = true; bool enable_indexscan = true; bool enable_indexonlyscan = true; +bool enable_indexskipscan = true; bool enable_bitmapscan = true; bool enable_tidscan = true; bool enable_sort = true; diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index bcb1bc6097..27c945413b 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -780,6 +780,16 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel, { IndexPath *ipath = (IndexPath *) lfirst(lc); + /* + * To prevent unique paths from index skip scans being potentially used + * when not needed scan keep them in a separate pathlist. + */ + if (ipath->indexskipprefix != 0) + { + add_unique_path(rel, (Path *) ipath); + continue; + } + if (index->amhasgettuple) add_path(rel, (Path *) ipath); @@ -862,12 +872,15 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, double loop_count; List *orderbyclauses; List *orderbyclausecols; - List *index_pathkeys; + List *index_pathkeys = NIL; List *useful_pathkeys; + List *index_pathkeys_pos = NIL; bool found_lower_saop_clause; bool pathkeys_possibly_useful; bool index_is_ordered; bool index_only_scan; + bool not_empty_qual = false; + bool can_skip; int indexcol; /* @@ -985,7 +998,8 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, if (index_is_ordered && pathkeys_possibly_useful) { index_pathkeys = build_index_pathkeys(root, index, - ForwardScanDirection); + ForwardScanDirection, + &index_pathkeys_pos); useful_pathkeys = truncate_useless_pathkeys(root, rel, index_pathkeys); orderbyclauses = NIL; @@ -1017,6 +1031,120 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, index_only_scan = (scantype != ST_BITMAPSCAN && check_index_only(rel, index)); + /* Check if an index skip scan is possible. */ + can_skip = enable_indexskipscan & index->amcanskip; + + if (can_skip) + { + /* + * Skip scan is not supported when there are qual conditions, which are not + * covered by index. The reason for that is that those conditions are + * evaluated later, already after skipping was applied. + * + * TODO: This implementation is too restrictive, and doesn't allow e.g. + * index expressions. For that we need to examine index_clauses too. + */ + if (root->parse->jointree != NULL) + { + ListCell *lc; + + foreach(lc, (List *)root->parse->jointree->quals) + { + Node *expr, *qual = (Node *) lfirst(lc); + Var *var; + bool found = false; + + if (!is_opclause(qual)) + { + not_empty_qual = true; + break; + } + + expr = get_leftop(qual); + + if (!IsA(expr, Var)) + { + not_empty_qual = true; + break; + } + + var = (Var *) expr; + + for (int i = 0; i < index->ncolumns; i++) + { + if (index->indexkeys[i] == var->varattno) + { + found = true; + break; + } + } + + if (!found) + { + not_empty_qual = true; + break; + } + } + } + + /* + * For an index scan verify that index fully covers distinct + * expressions, otherwise there is not enough information for skipping + */ + if (!index_only_scan && root->query_uniquekeys != NULL) + { + ListCell *lc; + + foreach(lc, root->query_uniquekeys) + { + UniqueKey *uniqueKey = (UniqueKey *) lfirst(lc); + ListCell *lc1; + + foreach(lc1, uniqueKey->exprs) + { + Expr *expr = (Expr *) lfirst(lc1); + bool found = false; + + if (!IsA(expr, Var)) + { + ListCell *lc2; + + foreach(lc2, index->indexprs) + { + if(equal(lfirst(lc1), lfirst(lc2))) + { + found = true; + break; + } + } + } + else + { + Var *var = (Var *) expr; + + for (int i = 0; i < index->ncolumns; i++) + { + if (index->indexkeys[i] == var->varattno) + { + found = true; + break; + } + } + } + + if (!found) + { + can_skip = false; + break; + } + } + + if (!can_skip) + break; + } + } + } + /* * 4. Generate an indexscan path if there are relevant restriction clauses * in the current clauses, OR the index ordering is potentially useful for @@ -1040,6 +1168,32 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, false); result = lappend(result, ipath); + /* Consider index skip scan as well */ + if (root->query_uniquekeys != NULL && can_skip && !not_empty_qual) + { + int numusefulkeys = list_length(useful_pathkeys); + int numsortkeys = list_length(root->query_pathkeys); + + if (numusefulkeys == numsortkeys) + { + int prefix; + if (list_length(root->distinct_pathkeys) > 0) + prefix = find_index_prefix_for_pathkey(index_pathkeys, + index_pathkeys_pos, + llast_node(PathKey, + root->distinct_pathkeys)); + else + /* all are distinct keys are constant and optimized away. + * skipping with 1 is sufficient as all are constant anyway + */ + prefix = 1; + + result = lappend(result, + create_skipscan_unique_path(root, index, + (Path *) ipath, prefix)); + } + } + /* * If appropriate, consider parallel index scan. We don't allow * parallel index scan for bitmap index scans. @@ -1078,7 +1232,8 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, if (index_is_ordered && pathkeys_possibly_useful) { index_pathkeys = build_index_pathkeys(root, index, - BackwardScanDirection); + BackwardScanDirection, + &index_pathkeys_pos); useful_pathkeys = truncate_useless_pathkeys(root, rel, index_pathkeys); if (useful_pathkeys != NIL) @@ -1095,6 +1250,32 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel, false); result = lappend(result, ipath); + /* Consider index skip scan as well */ + if (root->query_uniquekeys != NULL && can_skip && !not_empty_qual) + { + int numusefulkeys = list_length(useful_pathkeys); + int numsortkeys = list_length(root->query_pathkeys); + + if (numusefulkeys == numsortkeys) + { + int prefix; + if (list_length(root->distinct_pathkeys) > 0) + prefix = find_index_prefix_for_pathkey(index_pathkeys, + index_pathkeys_pos, + llast_node(PathKey, + root->distinct_pathkeys)); + else + /* all are distinct keys are constant and optimized away. + * skipping with 1 is sufficient as all are constant anyway + */ + prefix = 1; + + result = lappend(result, + create_skipscan_unique_path(root, index, + (Path *) ipath, prefix)); + } + } + /* If appropriate, consider parallel index scan */ if (index->amcanparallel && rel->consider_parallel && outer_relids == NULL && diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c index 97e1c2de83..61c0ea98e7 100644 --- a/src/backend/optimizer/path/pathkeys.c +++ b/src/backend/optimizer/path/pathkeys.c @@ -522,6 +522,47 @@ get_cheapest_parallel_safe_total_inner(List *paths) * NEW PATHKEY FORMATION ****************************************************************************/ +/* + * Find the prefix size for a specific path key in an index. For example, an + * index with (a,b,c) finding path key b will return prefix 2. Optionally + * pathkeys_positions can be provided, to specify at which position in the + * original pathkey list this particular key could be found (this is helpful + * when we deal with redundant pathkeys). + * + * Returns 0 when not found. + */ +int +find_index_prefix_for_pathkey(List *index_pathkeys, + List *pathkeys_positions, + PathKey *target_pathkey) +{ + ListCell *lc; + int i; + + i = 0; + foreach(lc, index_pathkeys) + { + PathKey *cpathkey = (PathKey *) lfirst(lc); + + if (cpathkey == target_pathkey) + { + /* + * Prefix expected to start from 1, increment positions since + * they're 0 based. + */ + if (pathkeys_positions != NIL && + pathkeys_positions->length > i) + return list_nth_int(pathkeys_positions, i) + 1; + else + return i + 1; + } + + i++; + } + + return 0; +} + /* * build_index_pathkeys * Build a pathkeys list that describes the ordering induced by an index @@ -534,7 +575,9 @@ get_cheapest_parallel_safe_total_inner(List *paths) * We iterate only key columns of covering indexes, since non-key columns * don't influence index ordering. The result is canonical, meaning that * redundant pathkeys are removed; it may therefore have fewer entries than - * there are key columns in the index. + * there are key columns in the index. Since by removing redundant pathkeys the + * information about original position is lost, return it via positions + * argument. * * Another reason for stopping early is that we may be able to tell that * an index column's sort order is uninteresting for this query. However, @@ -545,7 +588,8 @@ get_cheapest_parallel_safe_total_inner(List *paths) List * build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index, - ScanDirection scandir) + ScanDirection scandir, + List **positions) { List *retval = NIL; ListCell *lc; @@ -554,6 +598,8 @@ build_index_pathkeys(PlannerInfo *root, if (index->sortopfamily == NULL) return NIL; /* non-orderable index */ + *positions = NIL; + i = 0; foreach(lc, index->indextlist) { @@ -607,7 +653,11 @@ build_index_pathkeys(PlannerInfo *root, * for this query. Add it to list, unless it's redundant. */ if (!pathkey_is_redundant(cpathkey, retval)) + { retval = lappend(retval, cpathkey); + *positions = lappend_int(*positions, + foreach_current_index(lc)); + } } else { diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 99278eed93..7185424c20 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -177,12 +177,14 @@ static IndexScan *make_indexscan(List *qptlist, List *qpqual, Index scanrelid, Oid indexid, List *indexqual, List *indexqualorig, List *indexorderby, List *indexorderbyorig, List *indexorderbyops, - ScanDirection indexscandir); + ScanDirection indexscandir, + int skipprefix); static IndexOnlyScan *make_indexonlyscan(List *qptlist, List *qpqual, Index scanrelid, Oid indexid, List *indexqual, List *indexorderby, List *indextlist, - ScanDirection indexscandir); + ScanDirection indexscandir, + int skipprefix); static BitmapIndexScan *make_bitmap_indexscan(Index scanrelid, Oid indexid, List *indexqual, List *indexqualorig); @@ -2987,7 +2989,8 @@ create_indexscan_plan(PlannerInfo *root, fixed_indexquals, fixed_indexorderbys, best_path->indexinfo->indextlist, - best_path->indexscandir); + best_path->indexscandir, + best_path->indexskipprefix); else scan_plan = (Scan *) make_indexscan(tlist, qpqual, @@ -2998,7 +3001,8 @@ create_indexscan_plan(PlannerInfo *root, fixed_indexorderbys, indexorderbys, indexorderbyops, - best_path->indexscandir); + best_path->indexscandir, + best_path->indexskipprefix); copy_generic_path_info(&scan_plan->plan, &best_path->path); @@ -5265,7 +5269,8 @@ make_indexscan(List *qptlist, List *indexorderby, List *indexorderbyorig, List *indexorderbyops, - ScanDirection indexscandir) + ScanDirection indexscandir, + int skipPrefixSize) { IndexScan *node = makeNode(IndexScan); Plan *plan = &node->scan.plan; @@ -5282,6 +5287,7 @@ make_indexscan(List *qptlist, node->indexorderbyorig = indexorderbyorig; node->indexorderbyops = indexorderbyops; node->indexorderdir = indexscandir; + node->indexskipprefixsize = skipPrefixSize; return node; } @@ -5294,7 +5300,8 @@ make_indexonlyscan(List *qptlist, List *indexqual, List *indexorderby, List *indextlist, - ScanDirection indexscandir) + ScanDirection indexscandir, + int skipPrefixSize) { IndexOnlyScan *node = makeNode(IndexOnlyScan); Plan *plan = &node->scan.plan; @@ -5309,6 +5316,7 @@ make_indexonlyscan(List *qptlist, node->indexorderby = indexorderby; node->indextlist = indextlist; node->indexorderdir = indexscandir; + node->indexskipprefixsize = skipPrefixSize; return node; } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index d4abb3cb47..e63ba85efd 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3003,6 +3003,43 @@ create_upper_unique_path(PlannerInfo *root, return pathnode; } +/* + * create_skipscan_unique_path + * Creates a pathnode the same as an existing IndexPath except based on + * skipping duplicate values. This may or may not be cheaper than using + * create_upper_unique_path. + * + * The input path must be an IndexPath for an index that supports amskip. + */ +IndexPath * +create_skipscan_unique_path(PlannerInfo *root, IndexOptInfo *index, + Path *basepath, int prefix) +{ + IndexPath *pathnode = makeNode(IndexPath); + int numDistinctRows; + UniqueKey *ukey; + + Assert(IsA(basepath, IndexPath)); + + /* We don't want to modify basepath, so make a copy. */ + memcpy(pathnode, basepath, sizeof(IndexPath)); + + ukey = linitial_node(UniqueKey, root->query_uniquekeys); + + Assert(prefix > 0); + pathnode->indexskipprefix = prefix; + pathnode->path.uniquekeys = root->query_uniquekeys; + + numDistinctRows = estimate_num_groups(root, ukey->exprs, + pathnode->path.rows, + NULL); + + pathnode->path.total_cost = pathnode->path.startup_cost * numDistinctRows; + pathnode->path.rows = numDistinctRows; + + return pathnode; +} + /* * create_agg_path * Creates a pathnode that represents performing aggregation/grouping diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 0b2f9d398a..6da71e5828 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -272,6 +272,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, info->amoptionalkey = amroutine->amoptionalkey; info->amsearcharray = amroutine->amsearcharray; info->amsearchnulls = amroutine->amsearchnulls; + info->amcanskip = (amroutine->amskip != NULL); info->amcanparallel = amroutine->amcanparallel; info->amhasgettuple = (amroutine->amgettuple != NULL); info->amhasgetbitmap = amroutine->amgetbitmap != NULL && diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 6f603cbbe8..b04ef1811d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -957,6 +957,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"enable_indexskipscan", PGC_USERSET, QUERY_TUNING_METHOD, + gettext_noop("Enables the planner's use of index-skip-scan plans."), + NULL + }, + &enable_indexskipscan, + true, + NULL, NULL, NULL + }, { {"enable_bitmapscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of bitmap-scan plans."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 5a0b8e9821..947dff83a5 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -357,6 +357,7 @@ #enable_hashjoin = on #enable_indexscan = on #enable_indexonlyscan = on +#enable_indexskipscan = on #enable_material = on #enable_mergejoin = on #enable_nestloop = on diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h index 4325faa460..85cca4d520 100644 --- a/src/include/access/amapi.h +++ b/src/include/access/amapi.h @@ -130,6 +130,13 @@ typedef void (*amrescan_function) (IndexScanDesc scan, typedef bool (*amgettuple_function) (IndexScanDesc scan, ScanDirection direction); +/* skip past duplicates in a given prefix */ +typedef bool (*amskip_function) (IndexScanDesc scan, + ScanDirection dir, + ScanDirection indexdir, + bool start, + int prefix); + /* fetch all valid tuples */ typedef int64 (*amgetbitmap_function) (IndexScanDesc scan, TIDBitmap *tbm); @@ -231,6 +238,7 @@ typedef struct IndexAmRoutine amendscan_function amendscan; ammarkpos_function ammarkpos; /* can be NULL */ amrestrpos_function amrestrpos; /* can be NULL */ + amskip_function amskip; /* can be NULL */ /* interface functions to support parallel index scans */ amestimateparallelscan_function amestimateparallelscan; /* can be NULL */ diff --git a/src/include/access/genam.h b/src/include/access/genam.h index 931257bd81..95ce4a5d80 100644 --- a/src/include/access/genam.h +++ b/src/include/access/genam.h @@ -180,6 +180,8 @@ extern IndexBulkDeleteResult *index_bulk_delete(IndexVacuumInfo *info, extern IndexBulkDeleteResult *index_vacuum_cleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats); extern bool index_can_return(Relation indexRelation, int attno); +extern bool index_skip(IndexScanDesc scan, ScanDirection direction, + ScanDirection indexdir, bool start, int prefix); extern RegProcedure index_getprocid(Relation irel, AttrNumber attnum, uint16 procnum); extern FmgrInfo *index_getprocinfo(Relation irel, AttrNumber attnum, diff --git a/src/include/access/sdir.h b/src/include/access/sdir.h index 23feb90986..094a127464 100644 --- a/src/include/access/sdir.h +++ b/src/include/access/sdir.h @@ -55,4 +55,11 @@ typedef enum ScanDirection #define ScanDirectionIsForward(direction) \ ((bool) ((direction) == ForwardScanDirection)) +/* + * ScanDirectionsAreOpposite + * True iff scan directions are backward/forward or forward/backward. + */ +#define ScanDirectionsAreOpposite(dirA, dirB) \ + ((bool) (dirA != NoMovementScanDirection && dirA == -dirB)) + #endif /* SDIR_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 6f96b31fb4..68563b1d1b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1429,6 +1429,8 @@ typedef struct IndexScanState ExprContext *iss_RuntimeContext; Relation iss_RelationDesc; struct IndexScanDescData *iss_ScanDesc; + int iss_SkipPrefixSize; + bool iss_FirstTupleEmitted; /* These are needed for re-checking ORDER BY expr ordering */ pairingheap *iss_ReorderQueue; @@ -1458,6 +1460,8 @@ typedef struct IndexScanState * TableSlot slot for holding tuples fetched from the table * VMBuffer buffer in use for visibility map testing, if any * PscanLen size of parallel index-only scan descriptor + * SkipPrefixSize number of keys for skip-based DISTINCT + * FirstTupleEmitted has the first tuple been emitted * ---------------- */ typedef struct IndexOnlyScanState @@ -1476,6 +1480,8 @@ typedef struct IndexOnlyScanState struct IndexScanDescData *ioss_ScanDesc; TupleTableSlot *ioss_TableSlot; Buffer ioss_VMBuffer; + int ioss_SkipPrefixSize; + bool ioss_FirstTupleEmitted; Size ioss_PscanLen; } IndexOnlyScanState; diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index a5c406bd4e..7287594582 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1229,6 +1229,9 @@ typedef struct Path * we need not recompute them when considering using the same index in a * bitmap index/heap scan (see BitmapHeapPath). The costs of the IndexPath * itself represent the costs of an IndexScan or IndexOnlyScan plan type. + * + * 'indexskipprefix' represents the number of columns to consider for skip + * scans. *---------- */ typedef struct IndexPath @@ -1241,6 +1244,7 @@ typedef struct IndexPath ScanDirection indexscandir; Cost indextotalcost; Selectivity indexselectivity; + int indexskipprefix; } IndexPath; /* diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 83e01074ed..efafdf9361 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -409,6 +409,8 @@ typedef struct IndexScan List *indexorderbyorig; /* the same in original form */ List *indexorderbyops; /* OIDs of sort ops for ORDER BY exprs */ ScanDirection indexorderdir; /* forward or backward or don't care */ + int indexskipprefixsize; /* the size of the prefix for distinct + * scans */ } IndexScan; /* ---------------- @@ -436,6 +438,8 @@ typedef struct IndexOnlyScan List *indexorderby; /* list of index ORDER BY exprs */ List *indextlist; /* TargetEntry list describing index's cols */ ScanDirection indexorderdir; /* forward or backward or don't care */ + int indexskipprefixsize; /* the size of the prefix for distinct + * scans */ } IndexOnlyScan; /* ---------------- diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 613db8eab6..c0f176eaaa 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -50,6 +50,7 @@ extern PGDLLIMPORT int max_parallel_workers_per_gather; extern PGDLLIMPORT bool enable_seqscan; extern PGDLLIMPORT bool enable_indexscan; extern PGDLLIMPORT bool enable_indexonlyscan; +extern PGDLLIMPORT bool enable_indexskipscan; extern PGDLLIMPORT bool enable_bitmapscan; extern PGDLLIMPORT bool enable_tidscan; extern PGDLLIMPORT bool enable_sort; diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 6796ad8cb7..8ec1780a56 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -207,6 +207,10 @@ extern UpperUniquePath *create_upper_unique_path(PlannerInfo *root, Path *subpath, int numCols, double numGroups); +extern IndexPath *create_skipscan_unique_path(PlannerInfo *root, + IndexOptInfo *index, + Path *subpath, + int prefix); extern AggPath *create_agg_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 0cb8030e33..6ebcb0e0a4 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -198,8 +198,11 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths, Relids required_outer, double fraction); extern Path *get_cheapest_parallel_safe_total_inner(List *paths); +extern int find_index_prefix_for_pathkey(List *index_pathkeys, + List *pathkey_positions, + PathKey *target_pathkey); extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index, - ScanDirection scandir); + ScanDirection scandir, List **positions); extern List *build_partition_pathkeys(PlannerInfo *root, RelOptInfo *partrel, ScanDirection scandir, bool *partialkeys); extern List *build_expression_pathkey(PlannerInfo *root, Expr *expr, diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index 06c4c3e476..ce883e5060 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -79,6 +79,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_incremental_sort | on enable_indexonlyscan | on enable_indexscan | on + enable_indexskipscan | on enable_material | on enable_mergejoin | on enable_nestloop | on @@ -90,7 +91,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_seqscan | on enable_sort | on enable_tidscan | on -(18 rows) +(21 rows) -- Test that the pg_timezone_names and pg_timezone_abbrevs views are -- more-or-less working. We can't test their contents in any great detail -- 2.21.0 --op7mf2i72xk5yu7c Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v36-0005-Btree-implementation-of-skipping.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Decoupling antiwraparound autovacuum from special rules around auto cancellation @ 2023-01-09 01:49 Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Peter Geoghegan @ 2023-01-09 01:49 UTC (permalink / raw) To: Jeff Davis <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Thu, Dec 29, 2022 at 7:01 PM Peter Geoghegan <[email protected]> wrote: > Attached is v2, which is just to fix bitrot. Attached is v3. We no longer apply vacuum_failsafe_age when determining the cutoff for antiwraparound autovacuuming -- the new approach is a bit simpler. This is a fairly small change overall. Now any "table age driven" autovacuum will also be antiwraparound when its relfrozenxid/relminmxid attains an age that's either double the relevant setting (either autovacuum_freeze_max_age or effective_multixact_freeze_max_age), or 1 billion XIDs/MXIDs -- whichever is less. That makes it completely impossible to disable antiwraparound protections (the special antiwrap autocancellation behavior) for table-age-driven autovacuums once table age exceeds 1 billion XIDs/MXIDs. It's still possible to increase autovacuum_freeze_max_age to well over a billion, of course. It just won't be possible to do that while also avoiding the no-auto-cancellation behavior for those autovacuums that are triggered due to table age crossing the autovacuum_freeze_max_age/effective_multixact_freeze_max_age threshold. -- Peter Geoghegan Attachments: [application/x-patch] v3-0001-Add-table-age-trigger-concept-to-autovacuum.patch (26.3K, ../../CAH2-Wz=dvf6AzA1SjCCbShbt5Cd5LSAJQ_9hcOhgnJw7D0v0FA@mail.gmail.com/2-v3-0001-Add-table-age-trigger-concept-to-autovacuum.patch) download | inline diff: From 6d78e62226608683d9882b4507d37442164267a1 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan <[email protected]> Date: Fri, 25 Nov 2022 11:23:20 -0800 Subject: [PATCH v3] Add "table age" trigger concept to autovacuum. Teach autovacuum.c to launch "table age" autovacuums at the same point that it previously triggered antiwraparound autovacuums. Antiwraparound autovacuums are retained, but are only used as a true option of last resort, when regular autovacuum has presumably tried and failed to advance relfrozenxid (likely because the auto-cancel behavior kept cancelling regular autovacuums triggered based on table age). The special auto-cancellation behavior applied by antiwraparound autovacuums is known to cause problems in the field, so it makes sense to avoid it, at least until the point where it starts to look like a proportionate response. Besides, the risk of the system eventually triggering xidStopLimit because of cancellations is a lot lower than it was back when the current auto-cancellation behavior was added by commit acac68b2. For example, there was no visibility map, so restarting antiwraparound autovacuum meant that the next autovacuum would get very little benefit from the work performed by earlier cancelled autovacuums. Also add new instrumentation that lists a triggering condition in the server log whenever an autovacuum is logged. This reports "table age" as the triggering criteria when regular (not antiwraparound) autovacuum runs because we need to advance the table's age. In other cases it will report an autovacuum was launched due to the tuple insert thresholds or the dead tuple thresholds. Note that pg_stat_activity doesn't have any special instrumentation for regular autovacuums that happen to have been triggered based on table age (since it really is just another form of autovacuum, and follows exactly the same rules in vacuumlazy.c and in proc.c). Author: Peter Geoghegan <[email protected]> Reviewed-By: Jeff Davis <[email protected]> Discussion: https://postgr.es/m/CAH2-Wz=S-R_2rO49Hm94Nuvhu9_twRGbTm6uwDRmRu-Sqn_t3w@mail.gmail.com --- src/include/commands/vacuum.h | 18 ++- src/include/storage/proc.h | 2 +- src/backend/access/heap/vacuumlazy.c | 14 ++ src/backend/access/heap/visibilitymap.c | 5 +- src/backend/access/transam/multixact.c | 4 +- src/backend/commands/vacuum.c | 18 ++- src/backend/postmaster/autovacuum.c | 207 +++++++++++++++++------- src/backend/storage/lmgr/proc.c | 4 +- 8 files changed, 197 insertions(+), 75 deletions(-) diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 689dbb770..b70f69fd9 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -191,6 +191,21 @@ typedef struct VacAttrStats #define VACOPT_SKIP_DATABASE_STATS 0x100 /* skip vac_update_datfrozenxid() */ #define VACOPT_ONLY_DATABASE_STATS 0x200 /* only vac_update_datfrozenxid() */ +/* + * Values used by autovacuum.c to tell vacuumlazy.c about the specific + * threshold type that triggered an autovacuum worker. + * + * AUTOVACUUM_NONE is used when VACUUM isn't running in an autovacuum worker. + */ +typedef enum AutoVacType +{ + AUTOVACUUM_NONE = 0, + AUTOVACUUM_TABLE_XID_AGE, + AUTOVACUUM_TABLE_MXID_AGE, + AUTOVACUUM_DEAD_TUPLES, + AUTOVACUUM_INSERTED_TUPLES, +} AutoVacType; + /* * Values used by index_cleanup and truncate params. * @@ -222,7 +237,8 @@ typedef struct VacuumParams * use default */ int multixact_freeze_table_age; /* multixact age at which to scan * whole table */ - bool is_wraparound; /* force a for-wraparound vacuum */ + bool is_wraparound; /* antiwraparound autovacuum? */ + AutoVacType trigger; /* Autovacuum trigger condition, if any */ int log_min_duration; /* minimum execution threshold in ms at * which autovacuum is logged, -1 to use * default */ diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index b5c6f46d0..8a92a9fe5 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -59,7 +59,7 @@ struct XidCache * CONCURRENTLY or REINDEX * CONCURRENTLY on non-expressional, * non-partial index */ -#define PROC_VACUUM_FOR_WRAPAROUND 0x08 /* set by autovac only */ +#define PROC_VACUUM_FOR_WRAPAROUND 0x08 /* emergency autovac */ #define PROC_IN_LOGICAL_DECODING 0x10 /* currently doing logical * decoding outside xact */ #define PROC_AFFECTS_ALL_HORIZONS 0x20 /* this proc's xmin must be diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index a42e881da..31c68e95e 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -639,6 +639,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, * implies aggressive. Produce distinct output for the corner * case all the same, just in case. */ + Assert(params->trigger == AUTOVACUUM_TABLE_XID_AGE || + params->trigger == AUTOVACUUM_TABLE_MXID_AGE); if (vacrel->aggressive) msgfmt = _("automatic aggressive vacuum to prevent wraparound of table \"%s.%s.%s\": index scans: %d\n"); else @@ -656,6 +658,18 @@ heap_vacuum_rel(Relation rel, VacuumParams *params, vacrel->relnamespace, vacrel->relname, vacrel->num_index_scans); + if (!verbose) + { + Assert(params->trigger != AUTOVACUUM_NONE); + if (params->trigger == AUTOVACUUM_TABLE_XID_AGE) + appendStringInfo(&buf, _("autovacuum trigger: table XID age threshold\n")); + else if (params->trigger == AUTOVACUUM_TABLE_MXID_AGE) + appendStringInfo(&buf, _("autovacuum trigger: table MultiXactId age threshold\n")); + else if (params->trigger == AUTOVACUUM_DEAD_TUPLES) + appendStringInfo(&buf, _("autovacuum trigger: dead tuples threshold\n")); + else if (params->trigger == AUTOVACUUM_INSERTED_TUPLES) + appendStringInfo(&buf, _("autovacuum trigger: inserted tuples threshold\n")); + } appendStringInfo(&buf, _("pages: %u removed, %u remain, %u scanned (%.2f%% of total)\n"), vacrel->removed_pages, new_rel_pages, diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c index 1d1ca423a..8a0ed5a06 100644 --- a/src/backend/access/heap/visibilitymap.c +++ b/src/backend/access/heap/visibilitymap.c @@ -26,8 +26,9 @@ * per heap page. A set all-visible bit means that all tuples on the page are * known visible to all transactions, and therefore the page doesn't need to * be vacuumed. A set all-frozen bit means that all tuples on the page are - * completely frozen, and therefore the page doesn't need to be vacuumed even - * if whole table scanning vacuum is required (e.g. anti-wraparound vacuum). + * completely frozen. VACUUM doesn't give up the right to advance the rel's + * relfrozenxid/relminmxid just by skipping its all-frozen pages; it need only + * scan those pages that might have remaining unfrozen XIDs or MultiXactIds. * The all-frozen bit must be set only when the page is already all-visible. * * The map is conservative in the sense that we make sure that whenever a bit diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index e75e1fdf7..c0ee3876f 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -2553,8 +2553,8 @@ GetOldestMultiXactId(void) * info in MultiXactState, where it can be used to prevent overrun of old data * in the members SLRU area. * - * The return value is true if emergency autovacuum is required and false - * otherwise. + * The return value is true if emergency offset autovacuum (which appears as a + * table MXID age autovacuum to users) is required, and false otherwise. */ static bool SetOffsetVacuumLimit(bool is_startup) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index c4ed7efce..2a4950df5 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -271,8 +271,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) params.multixact_freeze_table_age = -1; } - /* user-invoked vacuum is never "for wraparound" */ + /* user-invoked vacuum never uses these autovacuum-only flags */ params.is_wraparound = false; + params.trigger = AUTOVACUUM_NONE; /* user-invoked vacuum uses VACOPT_VERBOSE instead of log_min_duration */ params.log_min_duration = -1; @@ -1049,8 +1050,8 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, /* * Determine the minimum freeze age to use: as specified by the caller, or * vacuum_freeze_min_age, but in any case not more than half - * autovacuum_freeze_max_age, so that autovacuums to prevent XID - * wraparound won't occur too frequently. + * autovacuum_freeze_max_age, so that table XID age autovacuums won't + * occur too frequently. */ if (freeze_min_age < 0) freeze_min_age = vacuum_freeze_min_age; @@ -1068,8 +1069,8 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, /* * Determine the minimum multixact freeze age to use: as specified by * caller, or vacuum_multixact_freeze_min_age, but in any case not more - * than half effective_multixact_freeze_max_age, so that autovacuums to - * prevent MultiXact wraparound won't occur too frequently. + * than half effective_multixact_freeze_max_age, so that table MXID age + * autovacuums won't occur too frequently. */ if (multixact_freeze_min_age < 0) multixact_freeze_min_age = vacuum_multixact_freeze_min_age; @@ -1858,7 +1859,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) * * We also set the VACUUM_FOR_WRAPAROUND flag, which is passed down by * autovacuum; it's used to avoid canceling a vacuum that was invoked - * in an emergency. + * because no earlier vacuum (in particular no earlier "table age" + * autovacuum) ran and advanced relfrozenxid/relminmxid. * * Note: these flags remain set until CommitTransaction or * AbortTransaction. We don't want to clear them until we reset @@ -1870,7 +1872,11 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); MyProc->statusFlags |= PROC_IN_VACUUM; if (params->is_wraparound) + { + Assert(params->trigger == AUTOVACUUM_TABLE_XID_AGE || + params->trigger == AUTOVACUUM_TABLE_MXID_AGE); MyProc->statusFlags |= PROC_VACUUM_FOR_WRAPAROUND; + } ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; LWLockRelease(ProcArrayLock); } diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index f5ea381c5..016261f7e 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -135,6 +135,8 @@ int Log_autovacuum_min_duration = 600000; #define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */ #define MAX_AUTOVAC_SLEEPTIME 300 /* seconds */ +#define ANTIWRAPAROUND_MAX_AGE 1000000000 /* one billion XIDs/MXIDs */ + /* Flags to tell if we are in an autovacuum process */ static bool am_autovacuum_launcher = false; static bool am_autovacuum_worker = false; @@ -327,15 +329,17 @@ static void FreeWorkerInfo(int code, Datum arg); static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map, TupleDesc pg_class_desc, int effective_multixact_freeze_max_age); -static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts, - Form_pg_class classForm, - int effective_multixact_freeze_max_age, - bool *dovacuum, bool *doanalyze, bool *wraparound); -static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, - Form_pg_class classForm, - PgStat_StatTabEntry *tabentry, - int effective_multixact_freeze_max_age, - bool *dovacuum, bool *doanalyze, bool *wraparound); +static AutoVacType recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts, + Form_pg_class classForm, + int effective_multixact_freeze_max_age, + bool *dovacuum, bool *doanalyze, + bool *wraparound); +static AutoVacType relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, + Form_pg_class classForm, + PgStat_StatTabEntry *tabentry, + int effective_multixact_freeze_max_age, + bool *dovacuum, bool *doanalyze, + bool *wraparound); static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); @@ -1148,8 +1152,8 @@ do_start_worker(void) { List *dblist; ListCell *cell; - TransactionId xidForceLimit; - MultiXactId multiForceLimit; + TransactionId xidAgeLimit; + MultiXactId multiAgeLimit; bool for_xid_wrap; bool for_multi_wrap; avw_dbase *avdb; @@ -1186,17 +1190,17 @@ do_start_worker(void) * particular tables, but not loosened.) */ recentXid = ReadNextTransactionId(); - xidForceLimit = recentXid - autovacuum_freeze_max_age; + xidAgeLimit = recentXid - autovacuum_freeze_max_age; /* ensure it's a "normal" XID, else TransactionIdPrecedes misbehaves */ /* this can cause the limit to go backwards by 3, but that's OK */ - if (xidForceLimit < FirstNormalTransactionId) - xidForceLimit -= FirstNormalTransactionId; + if (xidAgeLimit < FirstNormalTransactionId) + xidAgeLimit -= FirstNormalTransactionId; /* Also determine the oldest datminmxid we will consider. */ recentMulti = ReadNextMultiXactId(); - multiForceLimit = recentMulti - MultiXactMemberFreezeThreshold(); - if (multiForceLimit < FirstMultiXactId) - multiForceLimit -= FirstMultiXactId; + multiAgeLimit = recentMulti - MultiXactMemberFreezeThreshold(); + if (multiAgeLimit < FirstMultiXactId) + multiAgeLimit -= FirstMultiXactId; /* * Choose a database to connect to. We pick the database that was least @@ -1229,7 +1233,7 @@ do_start_worker(void) dlist_iter iter; /* Check to see if this one is at risk of wraparound */ - if (TransactionIdPrecedes(tmp->adw_frozenxid, xidForceLimit)) + if (TransactionIdPrecedes(tmp->adw_frozenxid, xidAgeLimit)) { if (avdb == NULL || TransactionIdPrecedes(tmp->adw_frozenxid, @@ -1240,7 +1244,7 @@ do_start_worker(void) } else if (for_xid_wrap) continue; /* ignore not-at-risk DBs */ - else if (MultiXactIdPrecedes(tmp->adw_minmulti, multiForceLimit)) + else if (MultiXactIdPrecedes(tmp->adw_minmulti, multiAgeLimit)) { if (avdb == NULL || MultiXactIdPrecedes(tmp->adw_minmulti, avdb->adw_minmulti)) @@ -1626,7 +1630,7 @@ AutoVacWorkerMain(int argc, char *argv[]) /* * Force synchronous replication off to allow regular maintenance even if * we are waiting for standbys to connect. This is important to ensure we - * aren't blocked from performing anti-wraparound tasks. + * aren't blocked from performing table age tasks. */ if (synchronous_commit > SYNCHRONOUS_COMMIT_LOCAL_FLUSH) SetConfigOption("synchronous_commit", "local", @@ -2767,6 +2771,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, autovac_table *tab = NULL; bool wraparound; AutoVacOpts *avopts; + AutoVacType trigger; /* fetch the relation's relcache entry */ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); @@ -2790,9 +2795,10 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, avopts = &hentry->ar_reloptions; } - recheck_relation_needs_vacanalyze(relid, avopts, classForm, - effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + trigger = recheck_relation_needs_vacanalyze(relid, avopts, classForm, + effective_multixact_freeze_max_age, + &dovacuum, &doanalyze, + &wraparound); /* OK, it needs something done */ if (doanalyze || dovacuum) @@ -2878,6 +2884,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age; tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age; tab->at_params.is_wraparound = wraparound; + tab->at_params.trigger = trigger; tab->at_params.log_min_duration = log_min_duration; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; @@ -2906,7 +2913,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * Fetch the pgstat of a relation and recheck whether a relation * needs to be vacuumed or analyzed. */ -static void +static AutoVacType recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts, Form_pg_class classForm, @@ -2916,26 +2923,29 @@ recheck_relation_needs_vacanalyze(Oid relid, bool *wraparound) { PgStat_StatTabEntry *tabentry; + AutoVacType trigger; /* fetch the pgstat table entry */ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, relid); - relation_needs_vacanalyze(relid, avopts, classForm, tabentry, - effective_multixact_freeze_max_age, - dovacuum, doanalyze, wraparound); + trigger = relation_needs_vacanalyze(relid, avopts, classForm, tabentry, + effective_multixact_freeze_max_age, + dovacuum, doanalyze, wraparound); /* ignore ANALYZE for toast tables */ if (classForm->relkind == RELKIND_TOASTVALUE) *doanalyze = false; + + return trigger; } /* * relation_needs_vacanalyze * - * Check whether a relation needs to be vacuumed or analyzed; return each into - * "dovacuum" and "doanalyze", respectively. Also return whether the vacuum is - * being forced because of Xid or multixact wraparound. + * Check whether a relation needs to be vacuumed or analyzed; set each using + * "dovacuum" and "doanalyze", respectively. Also indicate whether the vacuum + * must use special antiwraparound protections by setting "wraparound". * * relopts is a pointer to the AutoVacOpts options (either for itself in the * case of a plain table, or for either itself or its parent table in the case @@ -2953,9 +2963,9 @@ recheck_relation_needs_vacanalyze(Oid relid, * the number of tuples (both live and dead) that there were as of the last * analyze. This is asymmetric to the VACUUM case. * - * We also force vacuum if the table's relfrozenxid is more than freeze_max_age - * transactions back, and if its relminmxid is more than - * multixact_freeze_max_age multixacts back. + * We also force table age vacuum if the table's relfrozenxid is more than + * freeze_max_age transactions back, and if its relminmxid is more than + * multixact_freeze_max_age multixacts back. This cannot be disabled. * * A table whose autovacuum_enabled option is false is * automatically skipped (unless we have to vacuum it due to freeze_max_age). @@ -2966,8 +2976,17 @@ recheck_relation_needs_vacanalyze(Oid relid, * autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor * value < 0 is substituted with the value of * autovacuum_vacuum_scale_factor GUC variable. Ditto for analyze. + * + * Return value is the condition that triggered autovacuum to run VACUUM + * (useful only when *dovacuum is set). There can only be exactly one + * triggering condition, even when multiple thresholds happened to be crossed + * at the same time. We prefer to return "table XID age" in the event of such + * a conflict, after which we prefer "table MXID age" as the criteria, then + * "dead tuples", with "inserted tuples" placed last. These predecence rules + * are largely arbitrary. We must at least ensure that all antiwraparound + * autovacuums are advertised as triggered by table XID/MXID age criteria. */ -static void +static AutoVacType relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, @@ -2978,7 +2997,10 @@ relation_needs_vacanalyze(Oid relid, bool *doanalyze, bool *wraparound) { - bool force_vacuum; + TransactionId relfrozenxid = classForm->relfrozenxid; + MultiXactId relminmxid = classForm->relminmxid; + AutoVacType trigger = AUTOVACUUM_NONE; + bool tableagevac; bool av_enabled; float4 reltuples; /* pg_class.reltuples */ @@ -3003,8 +3025,8 @@ relation_needs_vacanalyze(Oid relid, /* freeze parameters */ int freeze_max_age; int multixact_freeze_max_age; - TransactionId xidForceLimit; - MultiXactId multiForceLimit; + TransactionId xidAgeLimit; + MultiXactId multiAgeLimit; Assert(classForm != NULL); Assert(OidIsValid(relid)); @@ -3051,40 +3073,89 @@ relation_needs_vacanalyze(Oid relid, av_enabled = (relopts ? relopts->enabled : true); - /* Force vacuum if table is at risk of wraparound */ - xidForceLimit = recentXid - freeze_max_age; - if (xidForceLimit < FirstNormalTransactionId) - xidForceLimit -= FirstNormalTransactionId; - force_vacuum = (TransactionIdIsNormal(classForm->relfrozenxid) && - TransactionIdPrecedes(classForm->relfrozenxid, - xidForceLimit)); - if (!force_vacuum) - { - multiForceLimit = recentMulti - multixact_freeze_max_age; - if (multiForceLimit < FirstMultiXactId) - multiForceLimit -= FirstMultiXactId; - force_vacuum = MultiXactIdIsValid(classForm->relminmxid) && - MultiXactIdPrecedes(classForm->relminmxid, multiForceLimit); - } - *wraparound = force_vacuum; + /* Force vacuum if table age exceeds cutoff */ + xidAgeLimit = recentXid - freeze_max_age; + if (xidAgeLimit < FirstNormalTransactionId) + xidAgeLimit -= FirstNormalTransactionId; + multiAgeLimit = recentMulti - multixact_freeze_max_age; + if (multiAgeLimit < FirstMultiXactId) + multiAgeLimit -= FirstMultiXactId; - /* User disabled it in pg_class.reloptions? (But ignore if at risk) */ - if (!av_enabled && !force_vacuum) + tableagevac = true; + *wraparound = false; + /* See header comments about trigger precedence */ + if (TransactionIdIsNormal(relfrozenxid) && + TransactionIdPrecedes(relfrozenxid, xidAgeLimit)) + trigger = AUTOVACUUM_TABLE_XID_AGE; + else if (MultiXactIdIsValid(relminmxid) && + MultiXactIdPrecedes(relminmxid, multiAgeLimit)) + trigger = AUTOVACUUM_TABLE_MXID_AGE; + else + tableagevac = false; + + /* User disabled non-table-age autovacuums in pg_class.reloptions? */ + if (!av_enabled && !tableagevac) { *doanalyze = false; *dovacuum = false; - return; + return AUTOVACUUM_NONE; + } + + /* + * If we're forcing table age autovacuum, are we at the point where it has + * to be an antiwraparound autovacuum? + * + * Antiwraparound autovacuums are different to other autovacuums in that + * they cannot be automatically canceled, and are described directly in + * pg_stat_activity. They're used only in emergencies, when no earlier + * standard table age autovacuum could complete and advance the table's + * relfrozenxid/relminmxid, despite an ample table age autovacuum window. + */ + if (tableagevac) + { + /* + * Double the table age to determine the cutoff for antiwraparound. + * This gives standard autovacuuming plenty of space to succeed, so we + * can be relatively confident that that hasn't and won't work out by + * the time antiwraparound mode finally starts to trigger. + * + * Don't ever put off antiwraparound autovacuuming past the point + * where relfrozenxid has already attained an age >= 1 billion XIDs, + * or where relminmxid has already attained an age >= 1 billion MXIDs. + */ + if (freeze_max_age < ANTIWRAPAROUND_MAX_AGE) + freeze_max_age *= 2; + freeze_max_age = Min(freeze_max_age, ANTIWRAPAROUND_MAX_AGE); + if (multixact_freeze_max_age < ANTIWRAPAROUND_MAX_AGE) + multixact_freeze_max_age *= 2; + multixact_freeze_max_age = Min(multixact_freeze_max_age, + ANTIWRAPAROUND_MAX_AGE); + + /* Similar test to before, but with double the max age */ + xidAgeLimit = recentXid - freeze_max_age; + if (xidAgeLimit < FirstNormalTransactionId) + xidAgeLimit -= FirstNormalTransactionId; + multiAgeLimit = recentMulti - multixact_freeze_max_age; + if (multiAgeLimit < FirstMultiXactId) + multiAgeLimit -= FirstMultiXactId; + *wraparound = ((TransactionIdIsNormal(relfrozenxid) && + TransactionIdPrecedes(relfrozenxid, xidAgeLimit)) || + (MultiXactIdIsValid(relminmxid) && + MultiXactIdPrecedes(relminmxid, multiAgeLimit))); } /* * If we found stats for the table, and autovacuum is currently enabled, * make a threshold-based decision whether to vacuum and/or analyze. If - * autovacuum is currently disabled, we must be here for anti-wraparound + * autovacuum is currently disabled, we must be here for forced table age * vacuuming only, so don't vacuum (or analyze) anything that's not being * forced. */ if (PointerIsValid(tabentry) && AutoVacuumingActive()) { + bool deadtupvac, + inserttupvac; + reltuples = classForm->reltuples; vactuples = tabentry->dead_tuples; instuples = tabentry->ins_since_vacuum; @@ -3112,25 +3183,39 @@ relation_needs_vacanalyze(Oid relid, NameStr(classForm->relname), vactuples, vacthresh, anltuples, anlthresh); + deadtupvac = (vactuples > vacthresh); + inserttupvac = (vac_ins_base_thresh >= 0 && instuples > vacinsthresh); + /* See header comments about trigger precedence */ + if (!tableagevac) + { + if (deadtupvac) + trigger = AUTOVACUUM_DEAD_TUPLES; + else if (inserttupvac) + trigger = AUTOVACUUM_INSERTED_TUPLES; + } + /* Determine if this table needs vacuum or analyze. */ - *dovacuum = force_vacuum || (vactuples > vacthresh) || - (vac_ins_base_thresh >= 0 && instuples > vacinsthresh); + *dovacuum = (tableagevac || deadtupvac || inserttupvac); *doanalyze = (anltuples > anlthresh); } else { /* * Skip a table not found in stat hash, unless we have to force vacuum - * for anti-wrap purposes. If it's not acted upon, there's no need to + * for table age purposes. If it's not acted upon, there's no need to * vacuum it. */ - *dovacuum = force_vacuum; + *dovacuum = tableagevac; *doanalyze = false; } /* ANALYZE refuses to work with pg_statistic */ if (relid == StatisticRelationId) *doanalyze = false; + + Assert((trigger != AUTOVACUUM_NONE) == *dovacuum); + + return trigger; } /* diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 00d26dc0f..dc66b9af0 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1384,8 +1384,8 @@ ProcSleep(LOCALLOCK *locallock, LockMethod lockMethodTable) LWLockRelease(ProcArrayLock); /* - * Only do it if the worker is not working to protect against Xid - * wraparound. + * Only do it if the worker is not an antiwraparound autovacuum, a + * special type of autovacuum that is only used in emergencies */ if ((statusFlags & PROC_IS_AUTOVACUUM) && !(statusFlags & PROC_VACUUM_FOR_WRAPAROUND)) -- 2.38.1 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Decoupling antiwraparound autovacuum from special rules around auto cancellation @ 2023-01-10 01:22 Andres Freund <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Andres Freund @ 2023-01-10 01:22 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: Jeff Davis <[email protected]>; PostgreSQL Hackers <[email protected]> Hi, On 2023-01-08 17:49:20 -0800, Peter Geoghegan wrote: > Teach autovacuum.c to launch "table age" autovacuums at the same point > that it previously triggered antiwraparound autovacuums. Antiwraparound > autovacuums are retained, but are only used as a true option of last > resort, when regular autovacuum has presumably tried and failed to > advance relfrozenxid (likely because the auto-cancel behavior kept > cancelling regular autovacuums triggered based on table age). I've also seen the inverse, with recent versions of postgres: Autovacuum can only ever make progress if it's an anti-wraparound vacuum, because it'll always get cancelled otherwise. I'm worried that substantially increasing the time until an anti-wraparound autovacuum happens will lead to more users running into out-of-xid shutdowns. I don't think it's safe to just increase the time at which anti-wrap vacuums happen to a hardcoded 1 billion. I'm also doubtful that it's ok to just make all autovacuums on relations with an age > 1 billion anti-wraparound ones. For people that use a large autovacuum_freeze_max_age that will be a rude awakening. I am all in favor for adding logic to trigger autovacuum based on the table age, without needing to reach autovacuum_freeze_max_age. It never made sense to me that we get to the "emergency mode" in entirely normal operation. But I'm not in favor of just entirely reinterpreting existing GUCs and adding important thresholds as hardcoded numbers. Greetings, Andres Freund ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-01-10 01:22 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-06-08 18:39 [PATCH v36 4/6] Index skip scan Dmitrii Dolgov <[email protected]> 2023-01-09 01:49 Re: Decoupling antiwraparound autovacuum from special rules around auto cancellation Peter Geoghegan <[email protected]> 2023-01-10 01:22 ` Re: Decoupling antiwraparound autovacuum from special rules around auto cancellation Andres Freund <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox