diff options
author | Peter Geoghegan <pg@bowt.ie> | 2025-03-05 10:27:31 -0500 |
---|---|---|
committer | Peter Geoghegan <pg@bowt.ie> | 2025-03-05 10:27:31 -0500 |
commit | d00107cd63e780753aa25563fa37603369997d0c (patch) | |
tree | 66f156ea5785fe6bf79e3b0861aced63ed484c20 /src | |
parent | 4603903d294bbdd644afecf9b5970827db6d1ff5 (diff) | |
download | postgresql-d00107cd63e780753aa25563fa37603369997d0c.tar.gz postgresql-d00107cd63e780753aa25563fa37603369997d0c.zip |
Revert "Show index search count in EXPLAIN ANALYZE."
This reverts commit 5ead85fbc81162ab1594f656b036a22e814f96b3.
This commit shows test failures with debug_parallel_query=regress. The
underlying issue needs to be debugged, so revert for now.
Diffstat (limited to 'src')
-rw-r--r-- | src/backend/access/brin/brin.c | 1 | ||||
-rw-r--r-- | src/backend/access/gin/ginscan.c | 1 | ||||
-rw-r--r-- | src/backend/access/gist/gistget.c | 2 | ||||
-rw-r--r-- | src/backend/access/hash/hashsearch.c | 1 | ||||
-rw-r--r-- | src/backend/access/index/genam.c | 1 | ||||
-rw-r--r-- | src/backend/access/nbtree/nbtree.c | 15 | ||||
-rw-r--r-- | src/backend/access/nbtree/nbtsearch.c | 1 | ||||
-rw-r--r-- | src/backend/access/spgist/spgscan.c | 1 | ||||
-rw-r--r-- | src/backend/commands/explain.c | 40 | ||||
-rw-r--r-- | src/include/access/relscan.h | 7 | ||||
-rw-r--r-- | src/test/regress/expected/brin_multi.out | 27 | ||||
-rw-r--r-- | src/test/regress/expected/memoize.out | 49 | ||||
-rw-r--r-- | src/test/regress/expected/partition_prune.out | 100 | ||||
-rw-r--r-- | src/test/regress/expected/select.out | 3 | ||||
-rw-r--r-- | src/test/regress/sql/memoize.sql | 5 | ||||
-rw-r--r-- | src/test/regress/sql/partition_prune.sql | 4 |
16 files changed, 40 insertions, 218 deletions
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index 9f146c12a85..75a65ec9c75 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -591,7 +591,6 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm) opaque = (BrinOpaque *) scan->opaque; bdesc = opaque->bo_bdesc; pgstat_count_index_scan(idxRel); - scan->nsearches++; /* * 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 8c1bbf36644..63ded6301e2 100644 --- a/src/backend/access/gin/ginscan.c +++ b/src/backend/access/gin/ginscan.c @@ -437,7 +437,6 @@ ginNewScanKey(IndexScanDesc scan) MemoryContextSwitchTo(oldCtx); pgstat_count_index_scan(scan->indexRelation); - scan->nsearches++; } void diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c index 609e85fdae1..cc40e928e0a 100644 --- a/src/backend/access/gist/gistget.c +++ b/src/backend/access/gist/gistget.c @@ -625,7 +625,6 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir) GISTSearchItem fakeItem; pgstat_count_index_scan(scan->indexRelation); - scan->nsearches++; so->firstCall = false; so->curPageData = so->nPageData = 0; @@ -751,7 +750,6 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm) return 0; pgstat_count_index_scan(scan->indexRelation); - scan->nsearches++; /* 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 c4f73043772..a3a1fccf33b 100644 --- a/src/backend/access/hash/hashsearch.c +++ b/src/backend/access/hash/hashsearch.c @@ -298,7 +298,6 @@ _hash_first(IndexScanDesc scan, ScanDirection dir) HashScanPosItem *currItem; pgstat_count_index_scan(rel); - scan->nsearches++; /* * 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 0cabfa5de5d..07bae342e25 100644 --- a/src/backend/access/index/genam.c +++ b/src/backend/access/index/genam.c @@ -119,7 +119,6 @@ RelationGetIndexScan(Relation indexRelation, int nkeys, int norderbys) scan->ignore_killed_tuples = !scan->xactStartedInRecovery; scan->opaque = NULL; - scan->nsearches = 0; scan->xs_itup = NULL; scan->xs_itupdesc = NULL; diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index eabd54b8c3d..45ea6afba1d 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -70,7 +70,6 @@ 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_nsearches; /* tracked for IndexScanDescData.nsearches */ slock_t btps_mutex; /* protects above variables, btps_arrElems */ ConditionVariable btps_cv; /* used to synchronize parallel scan */ @@ -558,7 +557,6 @@ btinitparallelscan(void *target) bt_target->btps_nextScanPage = InvalidBlockNumber; bt_target->btps_lastCurrPage = InvalidBlockNumber; bt_target->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED; - bt_target->btps_nsearches = 0; ConditionVariableInit(&bt_target->btps_cv); } @@ -585,7 +583,6 @@ btparallelrescan(IndexScanDesc scan) btscan->btps_nextScanPage = InvalidBlockNumber; btscan->btps_lastCurrPage = InvalidBlockNumber; btscan->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED; - /* deliberately don't reset btps_nsearches (matches index_rescan) */ SpinLockRelease(&btscan->btps_mutex); } @@ -679,7 +676,6 @@ _bt_parallel_seize(IndexScanDesc scan, BlockNumber *next_scan_page, { /* Can start scheduled primitive scan right away, so do so */ btscan->btps_pageStatus = BTPARALLEL_ADVANCING; - btscan->btps_nsearches++; for (int i = 0; i < so->numArrayKeys; i++) { BTArrayKeyInfo *array = &so->arrayKeys[i]; @@ -716,11 +712,6 @@ _bt_parallel_seize(IndexScanDesc scan, BlockNumber *next_scan_page, */ btscan->btps_pageStatus = BTPARALLEL_ADVANCING; Assert(btscan->btps_nextScanPage != P_NONE); - if (btscan->btps_nextScanPage == InvalidBlockNumber) - { - Assert(first); - btscan->btps_nsearches++; - } *next_scan_page = btscan->btps_nextScanPage; *last_curr_page = btscan->btps_lastCurrPage; exit_loop = true; @@ -819,12 +810,6 @@ _bt_parallel_done(IndexScanDesc scan) btscan->btps_pageStatus = BTPARALLEL_DONE; status_changed = true; } - - /* - * Don't use local nsearches counter -- overwrite it with the nsearches - * counter that we've been maintaining in shared memory - */ - scan->nsearches = btscan->btps_nsearches; SpinLockRelease(&btscan->btps_mutex); /* wake up all the workers associated with this parallel scan */ diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 941b4eaaf18..472ce06f190 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -950,7 +950,6 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * _bt_search/_bt_endpoint below */ pgstat_count_index_scan(rel); - scan->nsearches++; /*---------- * 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 8554b453555..53f910e9d89 100644 --- a/src/backend/access/spgist/spgscan.c +++ b/src/backend/access/spgist/spgscan.c @@ -421,7 +421,6 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, /* count an indexscan for stats */ pgstat_count_index_scan(scan->indexRelation); - scan->nsearches++; } void diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 78d5d0b67c4..d8a7232cedb 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -13,7 +13,6 @@ */ #include "postgres.h" -#include "access/relscan.h" #include "access/xact.h" #include "catalog/pg_type.h" #include "commands/createas.h" @@ -126,7 +125,6 @@ static void show_recursive_union_info(RecursiveUnionState *rstate, static void show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es); static void show_hashagg_info(AggState *aggstate, ExplainState *es); -static void show_indexsearches_info(PlanState *planstate, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); static void show_instrumentation_count(const char *qlabel, int which, @@ -2098,7 +2096,6 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); - show_indexsearches_info(planstate, es); break; case T_IndexOnlyScan: show_scan_qual(((IndexOnlyScan *) plan)->indexqual, @@ -2115,12 +2112,10 @@ ExplainNode(PlanState *planstate, List *ancestors, if (es->analyze) ExplainPropertyFloat("Heap Fetches", NULL, planstate->instrument->ntuples2, 0, es); - show_indexsearches_info(planstate, es); break; case T_BitmapIndexScan: show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig, "Index Cond", planstate, ancestors, es); - show_indexsearches_info(planstate, es); break; case T_BitmapHeapScan: show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig, @@ -3861,41 +3856,6 @@ show_hashagg_info(AggState *aggstate, ExplainState *es) } /* - * Show the total number of index searches performed by a - * IndexScan/IndexOnlyScan/BitmapIndexScan node - */ -static void -show_indexsearches_info(PlanState *planstate, ExplainState *es) -{ - Plan *plan = planstate->plan; - struct IndexScanDescData *scanDesc = NULL; - uint64 nsearches = 0; - - if (!es->analyze) - return; - - 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) - nsearches = scanDesc->nsearches; - - ExplainPropertyUInteger("Index Searches", NULL, nsearches, es); -} - -/* * Show exact/lossy pages for a BitmapHeapScan node */ static void diff --git a/src/include/access/relscan.h b/src/include/access/relscan.h index 8e0b08b0d94..dc6e0184284 100644 --- a/src/include/access/relscan.h +++ b/src/include/access/relscan.h @@ -151,13 +151,6 @@ typedef struct IndexScanDescData void *opaque; /* access-method-specific info */ /* - * Instrumentation counters that are maintained by every index access - * method, for all scan types. These go here because there is no standard - * way to access PlanState.instrument during amgettuple calls. - */ - uint64 nsearches; /* total # of index searches */ - - /* * In an index-only scan, a successful amgettuple call must fill either * xs_itup (and xs_itupdesc) or xs_hitup (and xs_hitupdesc) to provide the * data returned by the scan. It can fill both, in which case the heap diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out index cb5b5e53e86..991b7eacada 100644 --- a/src/test/regress/expected/brin_multi.out +++ b/src/test/regress/expected/brin_multi.out @@ -853,8 +853,7 @@ 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.00 loops=1) Index Cond: (a = '2023-01-01'::date) - Index Searches: 1 -(5 rows) +(4 rows) DROP TABLE brin_date_test; RESET enable_seqscan; @@ -873,8 +872,7 @@ 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.00 loops=1) Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone) - Index Searches: 1 -(5 rows) +(4 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp; @@ -884,8 +882,7 @@ 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.00 loops=1) Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone) - Index Searches: 1 -(5 rows) +(4 rows) DROP TABLE brin_timestamp_test; RESET enable_seqscan; @@ -903,8 +900,7 @@ 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.00 loops=1) Index Cond: (a = '2023-01-01'::date) - Index Searches: 1 -(5 rows) +(4 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date; @@ -914,8 +910,7 @@ 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.00 loops=1) Index Cond: (a = '1900-01-01'::date) - Index Searches: 1 -(5 rows) +(4 rows) DROP TABLE brin_date_test; RESET enable_seqscan; @@ -934,8 +929,7 @@ 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.00 loops=1) Index Cond: (a = '@ 30 years ago'::interval) - Index Searches: 1 -(5 rows) +(4 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; @@ -945,8 +939,7 @@ 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.00 loops=1) Index Cond: (a = '@ 30 years'::interval) - Index Searches: 1 -(5 rows) +(4 rows) DROP TABLE brin_interval_test; RESET enable_seqscan; @@ -964,8 +957,7 @@ 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.00 loops=1) Index Cond: (a = '@ 30 years ago'::interval) - Index Searches: 1 -(5 rows) +(4 rows) EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM brin_interval_test WHERE a = '30 years'::interval; @@ -975,8 +967,7 @@ 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.00 loops=1) Index Cond: (a = '@ 30 years'::interval) - Index Searches: 1 -(5 rows) +(4 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 38dfaf021c9..22f2d32845e 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -22,9 +22,8 @@ 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, 'Index Searches: \d+', 'Index Searches: N'); + ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); + ln := regexp_replace(ln, 'loops=\d+', 'loops=N'); return next ln; end loop; end; @@ -50,8 +49,7 @@ WHERE t2.unique1 < 1000;', false); -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N) Index Cond: (unique1 = t2.twenty) Heap Fetches: N - Index Searches: N -(13 rows) +(12 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 @@ -82,8 +80,7 @@ WHERE t1.unique1 < 1000;', false); -> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N) Index Cond: (unique1 = t1.twenty) Heap Fetches: N - Index Searches: N -(13 rows) +(12 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1, @@ -109,7 +106,6 @@ WHERE t1.unique1 < 10;', false); -> Nested Loop Left Join (actual rows=20.00 loops=N) -> Index Scan using tenk1_unique1 on tenk1 t1 (actual rows=10.00 loops=N) Index Cond: (unique1 < 10) - Index Searches: N -> Memoize (actual rows=2.00 loops=N) Cache Key: t1.two Cache Mode: binary @@ -119,8 +115,7 @@ WHERE t1.unique1 < 10;', false); Rows Removed by Filter: 2 -> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N) Index Cond: (unique1 < 4) - Index Searches: N -(15 rows) +(13 rows) -- And check we get the expected results. SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN @@ -154,8 +149,7 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false); Filter: ((t1.two + 1) = unique1) Rows Removed by Filter: 9999 Heap Fetches: N - Index Searches: N -(14 rows) +(13 rows) -- And check we get the expected results. SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN @@ -225,8 +219,7 @@ 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 - Index Searches: N -(11 rows) +(10 rows) DROP TABLE expr_key; -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions @@ -253,8 +246,7 @@ WHERE t2.unique1 < 1200;', true); -> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N) Index Cond: (unique1 = t2.thousand) Heap Fetches: N - Index Searches: N -(13 rows) +(12 rows) CREATE TABLE flt (f float); CREATE INDEX flt_f_idx ON flt (f); @@ -269,7 +261,6 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false); Nested Loop (actual rows=4.00 loops=N) -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2.00 loops=N) Heap Fetches: N - Index Searches: N -> Memoize (actual rows=2.00 loops=N) Cache Key: f1.f Cache Mode: logical @@ -277,8 +268,7 @@ 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.00 loops=N) Index Cond: (f = f1.f) Heap Fetches: N - Index Searches: N -(12 rows) +(10 rows) -- Ensure memoize operates in binary mode SELECT explain_memoize(' @@ -288,7 +278,6 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false); Nested Loop (actual rows=4.00 loops=N) -> Index Only Scan using flt_f_idx on flt f1 (actual rows=2.00 loops=N) Heap Fetches: N - Index Searches: N -> Memoize (actual rows=2.00 loops=N) Cache Key: f1.f Cache Mode: binary @@ -296,8 +285,7 @@ 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.00 loops=N) Index Cond: (f <= f1.f) Heap Fetches: N - Index Searches: N -(12 rows) +(10 rows) DROP TABLE flt; -- Exercise Memoize in binary mode with a large fixed width type and a @@ -323,8 +311,7 @@ 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.00 loops=N) Index Cond: (n <= s1.n) - Index Searches: N -(10 rows) +(9 rows) -- Ensure we get 3 hits and 3 misses SELECT explain_memoize(' @@ -340,8 +327,7 @@ 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.00 loops=N) Index Cond: (t <= s1.t) - Index Searches: N -(10 rows) +(9 rows) DROP TABLE strtest; -- Ensure memoize works with partitionwise join @@ -362,7 +348,6 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false); -> Nested Loop (actual rows=16.00 loops=N) -> Index Only Scan using iprt_p1_a on prt_p1 t1_1 (actual rows=4.00 loops=N) Heap Fetches: N - Index Searches: N -> Memoize (actual rows=4.00 loops=N) Cache Key: t1_1.a Cache Mode: logical @@ -370,11 +355,9 @@ 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.00 loops=N) Index Cond: (a = t1_1.a) Heap Fetches: N - Index Searches: N -> Nested Loop (actual rows=16.00 loops=N) -> Index Only Scan using iprt_p2_a on prt_p2 t1_2 (actual rows=4.00 loops=N) Heap Fetches: N - Index Searches: N -> Memoize (actual rows=4.00 loops=N) Cache Key: t1_2.a Cache Mode: logical @@ -382,8 +365,7 @@ 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.00 loops=N) Index Cond: (a = t1_2.a) Heap Fetches: N - Index Searches: N -(25 rows) +(21 rows) -- Ensure memoize works with parameterized union-all Append path SET enable_partitionwise_join TO off; @@ -396,7 +378,6 @@ ON t1.a = t2.a;', false); Nested Loop (actual rows=16.00 loops=N) -> Index Only Scan using iprt_p1_a on prt_p1 t1 (actual rows=4.00 loops=N) Heap Fetches: N - Index Searches: N -> Memoize (actual rows=4.00 loops=N) Cache Key: t1.a Cache Mode: logical @@ -405,12 +386,10 @@ ON t1.a = t2.a;', false); -> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N) Index Cond: (a = t1.a) Heap Fetches: N - Index Searches: N -> Index Only Scan using iprt_p2_a on prt_p2 (actual rows=0.00 loops=N) Index Cond: (a = t1.a) Heap Fetches: N - Index Searches: N -(17 rows) +(14 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 34f2b0b8dbd..d95d2395d48 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -2369,10 +2369,6 @@ begin ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+(?:\.\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, 'Index Searches: \d+'); - if found then - continue; - end if; return next ln; end loop; end; @@ -2690,56 +2686,47 @@ 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_a1_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0.00 loops=1) Recheck Cond: (a = (InitPlan 1).col1) Filter: (b = (InitPlan 2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0.00 loops=1) Index Cond: (a = (InitPlan 1).col1) - Index Searches: 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) - Index Searches: 0 -(61 rows) +(52 rows) -- Test run-time partition pruning with UNION ALL parents explain (analyze, costs off, summary off, timing off, buffers off) @@ -2755,19 +2742,16 @@ 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.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) - Index Searches: 0 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) Filter: (b = (InitPlan 1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) @@ -2786,7 +2770,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) -(40 rows) +(37 rows) -- A case containing a UNION ALL with a non-partitioned child. explain (analyze, costs off, summary off, timing off, buffers off) @@ -2802,19 +2786,16 @@ 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.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) - Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) Filter: (b = (InitPlan 1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) - Index Searches: 0 -> Result (actual rows=0.00 loops=1) One-Time Filter: (5 = (InitPlan 1).col1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) @@ -2835,7 +2816,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) -(42 rows) +(39 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); @@ -2906,19 +2887,16 @@ 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.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Materialize (actual rows=1.00 loops=1) Storage: Memory Maximum Storage: NkB -> Append (actual rows=1.00 loops=1) @@ -2926,20 +2904,17 @@ 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.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1.00 loops=1) Index Cond: (a = 1) - Index Searches: 1 -(43 rows) +(37 rows) table ab; a | b @@ -3015,23 +2990,17 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -> Append (actual rows=3.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=2) Index Cond: (col1 < tbl1.col1) - Index Searches: 2 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2.00 loops=1) Index Cond: (col1 < tbl1.col1) - Index Searches: 1 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -(21 rows) +(15 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; @@ -3042,23 +3011,17 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Append (actual rows=1.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1.00 loops=2) Index Cond: (col1 = tbl1.col1) - Index Searches: 2 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -(21 rows) +(15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3093,23 +3056,17 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -> Append (actual rows=4.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=5) Index Cond: (col1 < tbl1.col1) - Index Searches: 5 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2.75 loops=4) Index Cond: (col1 < tbl1.col1) - Index Searches: 4 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1.00 loops=2) Index Cond: (col1 < tbl1.col1) - Index Searches: 2 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) - Index Searches: 0 -(21 rows) +(15 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; @@ -3120,23 +3077,17 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; -> Append (actual rows=0.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1.00 loops=2) Index Cond: (col1 = tbl1.col1) - Index Searches: 2 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0.33 loops=3) Index Cond: (col1 = tbl1.col1) - Index Searches: 3 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -(21 rows) +(15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3190,23 +3141,17 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1; -> Append (actual rows=1.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 > tbl1.col1) - Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (never executed) Index Cond: (col1 > tbl1.col1) - Index Searches: 0 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 > tbl1.col1) - Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 > tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 > tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (actual rows=1.00 loops=1) Index Cond: (col1 > tbl1.col1) - Index Searches: 1 -(21 rows) +(15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 < tprt.col1 @@ -3228,23 +3173,17 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Append (actual rows=0.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt2_idx on tprt_2 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt3_idx on tprt_3 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt4_idx on tprt_4 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt5_idx on tprt_5 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) - Index Searches: 0 -(21 rows) +(15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 = tprt.col1 @@ -3574,12 +3513,10 @@ explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 - Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 - Index Searches: 1 -(11 rows) +(9 rows) execute mt_q1(15); a @@ -3597,8 +3534,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1.00 loops=1) Filter: ((a >= $1) AND ((a % 10) = 5)) Rows Removed by Filter: 9 - Index Searches: 1 -(7 rows) +(6 rows) execute mt_q1(25); a @@ -3646,17 +3582,13 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -> Limit (actual rows=1.00 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1) Index Cond: (b IS NOT NULL) - Index Searches: 1 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) Filter: (a >= (InitPlan 2).col1) - Index Searches: 0 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1) Filter: (a >= (InitPlan 2).col1) - Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1) Filter: (a >= (InitPlan 2).col1) - Index Searches: 1 -(18 rows) +(14 rows) reset enable_seqscan; reset enable_sort; @@ -4227,17 +4159,13 @@ 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.00 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) - Index Searches: 1 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) - Index Searches: 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 Searches: 0 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1) Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) - Index Searches: 1 -(19 rows) +(15 rows) reset enable_sort; drop table rangep; diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index bab0cc93ff5..cd79abc35db 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -764,8 +764,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; Index Scan using onek2_u2_prtl on onek2 (actual rows=1.00 loops=1) Index Cond: (unique2 = 11) Filter: (stringu1 = 'ATAAAA'::name) - Index Searches: 1 -(4 rows) +(3 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 c0d47fa875a..d5aab4e5666 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -23,9 +23,8 @@ 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, 'Index Searches: \d+', 'Index Searches: N'); + ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N'); + ln := regexp_replace(ln, 'loops=\d+', 'loops=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 4a2c74b0899..5f36d589b6b 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -588,10 +588,6 @@ begin ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N'); ln := regexp_replace(ln, 'actual rows=\d+(?:\.\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, 'Index Searches: \d+'); - if found then - continue; - end if; return next ln; end loop; end; |