From f6fb654d92073de6c8141bfad022278817b7920e Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Wed, 14 Aug 2024 13:50:23 -0400 Subject: [PATCH v1] Show primitive scan count in EXPLAIN ANALYZE. Also stop counting the case where nbtree detects contradictory quals as a distinct primitive index scan (do so neither in EXPLAIN ANALYZE nor in the pg_stat_*_indexes.idx_scan stats). --- src/include/access/relscan.h | 3 + src/backend/access/brin/brin.c | 1 + src/backend/access/gin/ginscan.c | 1 + src/backend/access/gist/gistget.c | 2 + src/backend/access/hash/hashsearch.c | 1 + src/backend/access/index/genam.c | 1 + src/backend/access/nbtree/nbtree.c | 13 +++++ src/backend/access/nbtree/nbtsearch.c | 9 ++- src/backend/access/spgist/spgscan.c | 1 + src/backend/commands/explain.c | 39 +++++++++++++ doc/src/sgml/bloom.sgml | 2 + doc/src/sgml/perform.sgml | 8 +++ doc/src/sgml/ref/explain.sgml | 1 + doc/src/sgml/rules.sgml | 1 + src/test/regress/expected/brin_multi.out | 27 ++++++--- src/test/regress/expected/memoize.out | 50 +++++++++++----- src/test/regress/expected/partition_prune.out | 57 ++++++++++++++----- src/test/regress/expected/select.out | 3 +- src/test/regress/sql/memoize.sql | 6 +- src/test/regress/sql/partition_prune.sql | 4 ++ 20 files changed, 189 insertions(+), 41 deletions(-) diff --git a/src/include/access/relscan.h b/src/include/access/relscan.h index 521043304..87619ec55 100644 --- a/src/include/access/relscan.h +++ b/src/include/access/relscan.h @@ -130,6 +130,9 @@ typedef struct IndexScanDescData bool xactStartedInRecovery; /* prevents killing/seeing killed * tuples */ + /* index access method instrumentation output state */ + uint64 nprimscans; /* # of primitive index scans */ + /* index access method's private state */ void *opaque; /* access-method-specific info */ diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index 6467bed60..7cd4c2e57 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -581,6 +581,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm) opaque = (BrinOpaque *) scan->opaque; bdesc = opaque->bo_bdesc; pgstat_count_index_scan(idxRel); + scan->nprimscans++; /* * We need to know the size of the table so that we know how long to diff --git a/src/backend/access/gin/ginscan.c b/src/backend/access/gin/ginscan.c index af24d3854..b031d8228 100644 --- a/src/backend/access/gin/ginscan.c +++ b/src/backend/access/gin/ginscan.c @@ -436,6 +436,7 @@ ginNewScanKey(IndexScanDesc scan) MemoryContextSwitchTo(oldCtx); pgstat_count_index_scan(scan->indexRelation); + scan->nprimscans++; } void diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c index b35b8a975..e452008e4 100644 --- a/src/backend/access/gist/gistget.c +++ b/src/backend/access/gist/gistget.c @@ -625,6 +625,7 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir) GISTSearchItem fakeItem; pgstat_count_index_scan(scan->indexRelation); + scan->nprimscans++; so->firstCall = false; so->curPageData = so->nPageData = 0; @@ -750,6 +751,7 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm) return 0; pgstat_count_index_scan(scan->indexRelation); + scan->nprimscans++; /* Begin the scan by processing the root page */ so->curPageData = so->nPageData = 0; diff --git a/src/backend/access/hash/hashsearch.c b/src/backend/access/hash/hashsearch.c index 0d99d6abc..a827c4052 100644 --- a/src/backend/access/hash/hashsearch.c +++ b/src/backend/access/hash/hashsearch.c @@ -298,6 +298,7 @@ _hash_first(IndexScanDesc scan, ScanDirection dir) HashScanPosItem *currItem; pgstat_count_index_scan(rel); + scan->nprimscans++; /* * We do not support hash scans with no index qualification, because we diff --git a/src/backend/access/index/genam.c b/src/backend/access/index/genam.c index de751e8e4..58a447494 100644 --- a/src/backend/access/index/genam.c +++ b/src/backend/access/index/genam.c @@ -116,6 +116,7 @@ RelationGetIndexScan(Relation indexRelation, int nkeys, int norderbys) scan->xactStartedInRecovery = TransactionStartedDuringRecovery(); scan->ignore_killed_tuples = !scan->xactStartedInRecovery; + scan->nprimscans = 0; /* not reset by index_rescan */ scan->opaque = NULL; scan->xs_itup = NULL; diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 686a3206f..906b1be51 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -70,6 +70,7 @@ typedef struct BTParallelScanDescData BTPS_State btps_pageStatus; /* indicates whether next page is * available for scan. see above for * possible states of parallel scan. */ + uint64 btps_nprimscans; /* instrumentation */ slock_t btps_mutex; /* protects above variables, btps_arrElems */ ConditionVariable btps_cv; /* used to synchronize parallel scan */ @@ -551,6 +552,7 @@ btinitparallelscan(void *target) SpinLockInit(&bt_target->btps_mutex); bt_target->btps_scanPage = InvalidBlockNumber; bt_target->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED; + bt_target->btps_nprimscans = 0; ConditionVariableInit(&bt_target->btps_cv); } @@ -576,6 +578,7 @@ btparallelrescan(IndexScanDesc scan) SpinLockAcquire(&btscan->btps_mutex); btscan->btps_scanPage = InvalidBlockNumber; btscan->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED; + /* deliberately don't reset btps_nprimscans (matches index_rescan) */ SpinLockRelease(&btscan->btps_mutex); } @@ -680,6 +683,11 @@ _bt_parallel_seize(IndexScanDesc scan, BlockNumber *pageno, bool first) * We have successfully seized control of the scan for the purpose * of advancing it to a new page! */ + if (first && btscan->btps_pageStatus == BTPARALLEL_NOT_INITIALIZED) + { + /* count the first primitive scan for this btrescan */ + btscan->btps_nprimscans++; + } btscan->btps_pageStatus = BTPARALLEL_ADVANCING; *pageno = btscan->btps_scanPage; exit_loop = true; @@ -747,11 +755,15 @@ _bt_parallel_done(IndexScanDesc scan) * already */ SpinLockAcquire(&btscan->btps_mutex); + btscan->btps_nprimscans += scan->nprimscans; + scan->nprimscans = btscan->btps_nprimscans; if (btscan->btps_pageStatus != BTPARALLEL_DONE) { btscan->btps_pageStatus = BTPARALLEL_DONE; status_changed = true; } + /* Copy the authoritative shared primitive scan counter to local field */ + scan->nprimscans = btscan->btps_nprimscans; SpinLockRelease(&btscan->btps_mutex); /* wake up all the workers associated with this parallel scan */ @@ -785,6 +797,7 @@ _bt_parallel_primscan_schedule(IndexScanDesc scan, BlockNumber prev_scan_page) { btscan->btps_scanPage = InvalidBlockNumber; btscan->btps_pageStatus = BTPARALLEL_NEED_PRIMSCAN; + btscan->btps_nprimscans++; /* Serialize scan's current array keys */ for (int i = 0; i < so->numArrayKeys; i++) diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 2551df8a6..596de2889 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -896,8 +896,6 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) Assert(!BTScanPosIsValid(so->currPos)); - pgstat_count_index_scan(rel); - /* * Examine the scan keys and eliminate any redundant keys; also mark the * keys that must be matched to continue the scan. @@ -960,6 +958,13 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) _bt_start_array_keys(scan, dir); } + /* + * We've established that we'll either call _bt_search or _bt_endpoint. + * Count this as a primitive index scan. + */ + pgstat_count_index_scan(rel); + scan->nprimscans++; + /*---------- * Examine the scan keys to discover where we need to start the scan. * diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c index 03293a781..6b8486ae9 100644 --- a/src/backend/access/spgist/spgscan.c +++ b/src/backend/access/spgist/spgscan.c @@ -423,6 +423,7 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, /* count an indexscan for stats */ pgstat_count_index_scan(scan->indexRelation); + scan->nprimscans++; } void diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 5771aabf4..39e7234e3 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -13,6 +13,7 @@ */ #include "postgres.h" +#include "access/relscan.h" #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/createas.h" @@ -89,6 +90,7 @@ static void show_plan_tlist(PlanState *planstate, List *ancestors, static void show_expression(Node *node, const char *qlabel, PlanState *planstate, List *ancestors, bool useprefix, ExplainState *es); +static void show_indexscan_nprimscans(PlanState *planstate, ExplainState *es); static void show_qual(List *qual, const char *qlabel, PlanState *planstate, List *ancestors, bool useprefix, ExplainState *es); @@ -1980,6 +1982,8 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + if (es->analyze) + show_indexscan_nprimscans(planstate, es); break; case T_IndexOnlyScan: show_scan_qual(((IndexOnlyScan *) plan)->indexqual, @@ -1994,12 +1998,17 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); if (es->analyze) + { ExplainPropertyFloat("Heap Fetches", NULL, planstate->instrument->ntuples2, 0, es); + show_indexscan_nprimscans(planstate, es); + } break; case T_BitmapIndexScan: show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig, "Index Cond", planstate, ancestors, es); + if (es->analyze) + show_indexscan_nprimscans(planstate, es); break; case T_BitmapHeapScan: show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig, @@ -2509,6 +2518,36 @@ show_expression(Node *node, const char *qlabel, ExplainPropertyText(qlabel, exprstr, es); } +/* + * Show the number of primitive index scans within an IndexScan node, + * IndexOnlyScan node, or BitmapIndexScan node + */ +static void +show_indexscan_nprimscans(PlanState *planstate, ExplainState *es) +{ + Plan *plan = planstate->plan; + struct IndexScanDescData *scanDesc = NULL; + + switch (nodeTag(plan)) + { + case T_IndexScan: + scanDesc = ((IndexScanState *) planstate)->iss_ScanDesc; + break; + case T_IndexOnlyScan: + scanDesc = ((IndexOnlyScanState *) planstate)->ioss_ScanDesc; + break; + case T_BitmapIndexScan: + scanDesc = ((BitmapIndexScanState *) planstate)->biss_ScanDesc; + break; + default: + break; + } + + if (scanDesc && scanDesc->nprimscans > 0) + ExplainPropertyUInteger("Primitive Index Scans", NULL, + scanDesc->nprimscans, es); +} + /* * Show a qualifier expression (which is a List with implicit AND semantics) */ diff --git a/doc/src/sgml/bloom.sgml b/doc/src/sgml/bloom.sgml index 19f2b172c..5f06030d8 100644 --- a/doc/src/sgml/bloom.sgml +++ b/doc/src/sgml/bloom.sgml @@ -170,6 +170,7 @@ CREATE INDEX Heap Blocks: exact=28 -> Bitmap Index Scan on bloomidx (cost=0.00..1792.00 rows=2 width=0) (actual time=0.356..0.356 rows=29 loops=1) Index Cond: ((i2 = 898732) AND (i5 = 123451)) + Primitive Index Scans: 1 Planning Time: 0.099 ms Execution Time: 0.408 ms (8 rows) @@ -202,6 +203,7 @@ CREATE INDEX -> BitmapAnd (cost=24.34..24.34 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1) -> Bitmap Index Scan on btreeidx5 (cost=0.00..12.04 rows=500 width=0) (actual time=0.026..0.026 rows=0 loops=1) Index Cond: (i5 = 123451) + Primitive Index Scans: 1 -> Bitmap Index Scan on btreeidx2 (cost=0.00..12.04 rows=500 width=0) (never executed) Index Cond: (i2 = 898732) Planning Time: 0.491 ms diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index ff689b652..987e5b47f 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -702,8 +702,10 @@ WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; Heap Blocks: exact=10 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1) Index Cond: (unique1 < 10) + Primitive Index Scans: 1 -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10) Index Cond: (unique2 = t1.unique2) + Primitive Index Scans: 1 Planning Time: 0.485 ms Execution Time: 0.073 ms @@ -754,6 +756,7 @@ WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; Heap Blocks: exact=90 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1) Index Cond: (unique1 < 100) + Primitive Index Scans: 1 Planning Time: 0.187 ms Execution Time: 3.036 ms @@ -819,6 +822,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; -------------------------------------------------------------------&zwsp;------------------------------------------------------- Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) + Primitive Index Scans: 1 Rows Removed by Index Recheck: 1 Planning Time: 0.039 ms Execution Time: 0.098 ms @@ -848,9 +852,11 @@ EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique Buffers: shared hit=4 read=3 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1) Index Cond: (unique1 < 100) + Primitive Index Scans: 1 Buffers: shared hit=2 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1) Index Cond: (unique2 > 9000) + Primitive Index Scans: 1 Buffers: shared hit=2 read=3 Planning: Buffers: shared hit=3 @@ -883,6 +889,7 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; Heap Blocks: exact=90 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1) Index Cond: (unique1 < 100) + Primitive Index Scans: 1 Planning Time: 0.151 ms Execution Time: 1.856 ms @@ -1019,6 +1026,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 + Primitive Index Scans: 1 Planning Time: 0.077 ms Execution Time: 0.086 ms diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index db9d3a854..859007ca0 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -502,6 +502,7 @@ EXPLAIN ANALYZE EXECUTE query(100, 200); Batches: 1 Memory Usage: 24kB -> Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1) Index Cond: ((id > 100) AND (id < 200)) + Primitive Index Scans: 1 Planning Time: 0.244 ms Execution Time: 0.073 ms (7 rows) diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 7a928bd7b..873132c30 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1046,6 +1046,7 @@ SELECT count(*) FROM words WHERE word = 'caterpiler'; -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: (word = 'caterpiler'::text) Heap Fetches: 0 + Primitive Index Scans: 1 Planning time: 0.164 ms Execution time: 0.117 ms diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index ae9ce9d8e..7e5c0b456 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -853,7 +853,8 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Recheck Cond: (a = '2023-01-01'::date) -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '2023-01-01'::date) -(4 rows) + Primitive Index Scans: 1 +(5 rows) DROP TABLE brin_date_test; RESET enable_seqscan; @@ -872,7 +873,8 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp; Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) -(4 rows) + Primitive Index Scans: 1 +(5 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; @@ -882,7 +884,8 @@ SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone) -> Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone) -(4 rows) + Primitive Index Scans: 1 +(5 rows) DROP TABLE brin_timestamp_test; RESET enable_seqscan; @@ -900,7 +903,8 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date; Recheck Cond: (a = '2023-01-01'::date) -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '2023-01-01'::date) -(4 rows) + Primitive Index Scans: 1 +(5 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; @@ -910,7 +914,8 @@ SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; Recheck Cond: (a = '1900-01-01'::date) -> Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '1900-01-01'::date) -(4 rows) + Primitive Index Scans: 1 +(5 rows) DROP TABLE brin_date_test; RESET enable_seqscan; @@ -929,7 +934,8 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Recheck Cond: (a = '@ 30 years ago'::interval) -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '@ 30 years ago'::interval) -(4 rows) + Primitive Index Scans: 1 +(5 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; @@ -939,7 +945,8 @@ SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; Recheck Cond: (a = '@ 30 years'::interval) -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '@ 30 years'::interval) -(4 rows) + Primitive Index Scans: 1 +(5 rows) DROP TABLE brin_interval_test; RESET enable_seqscan; @@ -957,7 +964,8 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval; Recheck Cond: (a = '@ 30 years ago'::interval) -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '@ 30 years ago'::interval) -(4 rows) + Primitive Index Scans: 1 +(5 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; @@ -967,7 +975,8 @@ SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; Recheck Cond: (a = '@ 30 years'::interval) -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1) Index Cond: (a = '@ 30 years'::interval) -(4 rows) + Primitive Index Scans: 1 +(5 rows) DROP TABLE brin_interval_test; RESET enable_seqscan; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 96906104d..ecad4b67e 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -22,8 +22,10 @@ begin ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero'); ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N'); ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N'); - ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); - ln := regexp_replace(ln, 'loops=\d+', 'loops=N'); + ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); + ln := regexp_replace(ln, 'loops=\d+', 'loops=N'); + ln := regexp_replace(ln, 'Primitive Index Scans: 0', 'Primitive Index Scans: Zero'); + ln := regexp_replace(ln, 'Primitive Index Scans: \d+', 'Primitive Index Scans: N'); return next ln; end loop; end; @@ -49,7 +51,8 @@ WHERE t2.unique1 < 1000;', false); -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.twenty) Heap Fetches: N -(12 rows) + Primitive Index Scans: N +(13 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -80,7 +83,8 @@ WHERE t1.unique1 < 1000;', false); -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N) Index Cond: (unique1 = t1.twenty) Heap Fetches: N -(12 rows) + Primitive Index Scans: N +(13 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -106,6 +110,7 @@ WHERE t1.unique1 < 10;', false); -> Nested Loop Left Join (actual rows=20 loops=N) -> Index Scan using tenk1_unique1 on tenk1 t1 (actual rows=10 loops=N) Index Cond: (unique1 < 10) + Primitive Index Scans: N -> Memoize (actual rows=2 loops=N) Cache Key: t1.two Cache Mode: binary @@ -115,7 +120,8 @@ WHERE t1.unique1 < 10;', false); Rows Removed by Filter: 2 -> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4 loops=N) Index Cond: (unique1 < 4) -(13 rows) + Primitive Index Scans: N +(15 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN @@ -149,7 +155,8 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false); Filter: ((t1.two + 1) = unique1) Rows Removed by Filter: 9999 Heap Fetches: N -(13 rows) + Primitive Index Scans: N +(14 rows) -- And check we get the expected results. SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN @@ -249,7 +256,8 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); Index Cond: (x = (t1.t)::numeric) Filter: (t1.x = (t)::numeric) Heap Fetches: N -(10 rows) + Primitive Index Scans: N +(11 rows) DROP TABLE expr_key; -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions @@ -276,7 +284,8 @@ WHERE t2.unique1 < 1200;', true); -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N) Index Cond: (unique1 = t2.thousand) Heap Fetches: N -(12 rows) + Primitive Index Scans: N +(13 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); @@ -291,6 +300,7 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false); Nested Loop (actual rows=4 loops=N) -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N) Heap Fetches: N + Primitive Index Scans: N -> Memoize (actual rows=2 loops=N) Cache Key: f1.f Cache Mode: logical @@ -298,7 +308,8 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false); -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N) Index Cond: (f = f1.f) Heap Fetches: N -(10 rows) + Primitive Index Scans: N +(12 rows) -- Ensure memoize operates in binary mode SELECT explain_memoize(' @@ -308,6 +319,7 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false); Nested Loop (actual rows=4 loops=N) -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N) Heap Fetches: N + Primitive Index Scans: N -> Memoize (actual rows=2 loops=N) Cache Key: f1.f Cache Mode: binary @@ -315,7 +327,8 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false); -> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N) Index Cond: (f <= f1.f) Heap Fetches: N -(10 rows) + Primitive Index Scans: N +(12 rows) DROP TABLE flt; -- Exercise Memoize in binary mode with a large fixed width type and a @@ -340,7 +353,8 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false); Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N) Index Cond: (n <= s1.n) -(8 rows) + Primitive Index Scans: N +(9 rows) -- Ensure we get 3 hits and 3 misses SELECT explain_memoize(' @@ -355,7 +369,8 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false); Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB -> Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N) Index Cond: (t <= s1.t) -(8 rows) + Primitive Index Scans: N +(9 rows) DROP TABLE strtest; -- Ensure memoize works with partitionwise join @@ -376,6 +391,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Nested Loop (actual rows=16 loops=N) -> Index Only Scan using iprt_p1_a on prt_p1 t1_1 (actual rows=4 loops=N) Heap Fetches: N + Primitive Index Scans: N -> Memoize (actual rows=4 loops=N) Cache Key: t1_1.a Cache Mode: logical @@ -383,9 +399,11 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4 loops=N) Index Cond: (a = t1_1.a) Heap Fetches: N + Primitive Index Scans: N -> Nested Loop (actual rows=16 loops=N) -> Index Only Scan using iprt_p2_a on prt_p2 t1_2 (actual rows=4 loops=N) Heap Fetches: N + Primitive Index Scans: N -> Memoize (actual rows=4 loops=N) Cache Key: t1_2.a Cache Mode: logical @@ -393,7 +411,8 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4 loops=N) Index Cond: (a = t1_2.a) Heap Fetches: N -(21 rows) + Primitive Index Scans: N +(25 rows) -- Ensure memoize works with parameterized union-all Append path SET enable_partitionwise_join TO off; @@ -406,6 +425,7 @@ ON t1.a = t2.a;', false); Nested Loop (actual rows=16 loops=N) -> Index Only Scan using iprt_p1_a on prt_p1 t1 (actual rows=4 loops=N) Heap Fetches: N + Primitive Index Scans: N -> Memoize (actual rows=4 loops=N) Cache Key: t1.a Cache Mode: logical @@ -414,10 +434,12 @@ ON t1.a = t2.a;', false); -> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4 loops=N) Index Cond: (a = t1.a) Heap Fetches: N + Primitive Index Scans: N -> Index Only Scan using iprt_p2_a on prt_p2 (actual rows=0 loops=N) Index Cond: (a = t1.a) Heap Fetches: N -(14 rows) + Primitive Index Scans: N +(17 rows) DROP TABLE prt; RESET enable_partitionwise_join; diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7a03b4e36..8f5a16f93 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -2340,6 +2340,10 @@ begin ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); + perform regexp_matches(ln, 'Primitive Index Scans: \d+'); + if found then + continue; + end if; return next ln; end loop; end; @@ -2692,12 +2696,13 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1) Index Cond: (a = (InitPlan 1).col1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) -(52 rows) +(53 rows) -- Test run-time partition pruning with UNION ALL parents explain (analyze, costs off, summary off, timing off) @@ -2713,6 +2718,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) @@ -2741,7 +2747,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) Filter: (b = (InitPlan 1).col1) -(37 rows) +(38 rows) -- A case containing a UNION ALL with a non-partitioned child. explain (analyze, costs off, summary off, timing off) @@ -2757,6 +2763,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) @@ -2787,7 +2794,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) Filter: (b = (InitPlan 1).col1) -(39 rows) +(40 rows) -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. create table xy_1 (x int, y int); @@ -2858,16 +2865,19 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;'); Recheck Cond: (a = 1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Materialize (actual rows=1 loops=1) Storage: Memory Maximum Storage: NkB -> Append (actual rows=1 loops=1) @@ -2875,17 +2885,20 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;'); Recheck Cond: (a = 1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) + Primitive Index Scans: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) -(37 rows) + Primitive Index Scans: 1 +(43 rows) table ab; a | b @@ -2961,8 +2974,10 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -> Append (actual rows=3 loops=2) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2) Index Cond: (col1 < tbl1.col1) + Primitive Index Scans: 2 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1) Index Cond: (col1 < tbl1.col1) + Primitive Index Scans: 1 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 < tbl1.col1) -> Index Scan using tprt4_idx on tprt_4 (never executed) @@ -2971,7 +2986,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Index Cond: (col1 < tbl1.col1) -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) -(15 rows) +(17 rows) explain (analyze, costs off, summary off, timing off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; @@ -2984,6 +2999,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) Index Cond: (col1 = tbl1.col1) + Primitive Index Scans: 2 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt4_idx on tprt_4 (never executed) @@ -2992,7 +3008,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) -(15 rows) +(16 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3027,17 +3043,20 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -> Append (actual rows=5 loops=5) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5) Index Cond: (col1 < tbl1.col1) + Primitive Index Scans: 5 -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4) Index Cond: (col1 < tbl1.col1) + Primitive Index Scans: 4 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2) Index Cond: (col1 < tbl1.col1) + Primitive Index Scans: 2 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 < tbl1.col1) -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 < tbl1.col1) -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) -(15 rows) +(18 rows) explain (analyze, costs off, summary off, timing off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; @@ -3050,15 +3069,17 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) Index Cond: (col1 = tbl1.col1) + Primitive Index Scans: 2 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3) Index Cond: (col1 = tbl1.col1) + Primitive Index Scans: 3 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) -(15 rows) +(17 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3122,7 +3143,8 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1; Index Cond: (col1 > tbl1.col1) -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1) Index Cond: (col1 > tbl1.col1) -(15 rows) + Primitive Index Scans: 1 +(16 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 < tprt.col1 @@ -3484,10 +3506,12 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15); -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 + Primitive Index Scans: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 -(9 rows) + Primitive Index Scans: 1 +(11 rows) execute mt_q1(15); a @@ -3505,7 +3529,8 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25); -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 -(6 rows) + Primitive Index Scans: 1 +(7 rows) execute mt_q1(25); a @@ -3553,13 +3578,16 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher -> Limit (actual rows=1 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1) Index Cond: (b IS NOT NULL) + Primitive Index Scans: 1 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) Filter: (a >= (InitPlan 2).col1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1) Filter: (a >= (InitPlan 2).col1) + Primitive Index Scans: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1) Filter: (a >= (InitPlan 2).col1) -(14 rows) + Primitive Index Scans: 1 +(17 rows) reset enable_seqscan; reset enable_sort; @@ -4130,13 +4158,16 @@ select * from rangep where b IN((select 1),(select 2)) order by a; Sort Key: rangep_2.a -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Primitive Index Scans: 1 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Primitive Index Scans: 1 -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) -(15 rows) + Primitive Index Scans: 1 +(18 rows) reset enable_sort; drop table rangep; diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 33a6dceb0..905252092 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -764,7 +764,8 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; Index Scan using onek2_u2_prtl on onek2 (actual rows=1 loops=1) Index Cond: (unique2 = 11) Filter: (stringu1 = 'ATAAAA'::name) -(3 rows) + Primitive Index Scans: 1 +(4 rows) explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 059bec5f4..303be8f69 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -23,8 +23,10 @@ begin ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero'); ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N'); ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N'); - ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); - ln := regexp_replace(ln, 'loops=\d+', 'loops=N'); + ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); + ln := regexp_replace(ln, 'loops=\d+', 'loops=N'); + ln := regexp_replace(ln, 'Primitive Index Scans: 0', 'Primitive Index Scans: Zero'); + ln := regexp_replace(ln, 'Primitive Index Scans: \d+', 'Primitive Index Scans: N'); return next ln; end loop; end; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 442428d93..1b2c89274 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -573,6 +573,10 @@ begin ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N'); ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N'); + perform regexp_matches(ln, 'Primitive Index Scans: \d+'); + if found then + continue; + end if; return next ln; end loop; end; -- 2.45.2