v8-0001-Show-index-search-count-in-EXPLAIN-ANALYZE.patch
application/octet-stream
Filename: v8-0001-Show-index-search-count-in-EXPLAIN-ANALYZE.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v8-0001
Subject: Show index search count in EXPLAIN ANALYZE.
| File | + | − |
|---|---|---|
| doc/src/sgml/bloom.sgml | 4 | 2 |
| doc/src/sgml/monitoring.sgml | 9 | 3 |
| doc/src/sgml/perform.sgml | 8 | 0 |
| doc/src/sgml/ref/explain.sgml | 2 | 1 |
| doc/src/sgml/rules.sgml | 1 | 0 |
| src/backend/access/brin/brin.c | 1 | 0 |
| src/backend/access/gin/ginscan.c | 1 | 0 |
| src/backend/access/gist/gistget.c | 2 | 0 |
| src/backend/access/hash/hashsearch.c | 1 | 0 |
| src/backend/access/index/genam.c | 1 | 0 |
| src/backend/access/nbtree/nbtree.c | 11 | 0 |
| src/backend/access/nbtree/nbtsearch.c | 1 | 0 |
| src/backend/access/spgist/spgscan.c | 1 | 0 |
| src/backend/commands/explain.c | 38 | 0 |
| src/include/access/relscan.h | 3 | 0 |
| src/test/regress/expected/brin_multi.out | 18 | 9 |
| src/test/regress/expected/memoize.out | 36 | 14 |
| src/test/regress/expected/partition_prune.out | 44 | 13 |
| src/test/regress/expected/select.out | 2 | 1 |
| src/test/regress/sql/memoize.sql | 4 | 2 |
| src/test/regress/sql/partition_prune.sql | 4 | 0 |
From 7e3050c3442c6d553c199be2046241dd80f362b7 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 14 Aug 2024 13:50:23 -0400
Subject: [PATCH v8 1/3] Show index search count in EXPLAIN ANALYZE.
Also stop counting the case where nbtree detects contradictory quals as
a distinct index search (do so neither in EXPLAIN ANALYZE nor in the
pg_stat_*_indexes.idx_scan stats).
Author: Peter Geoghegan <pg@bowt.ie>
Discussion: https://postgr.es/m/CAH2-WzkRqvaqR2CTNqTZP0z6FuL4-3ED6eQB0yx38XBNj1v-4Q@mail.gmail.com
---
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 | 11 ++++
src/backend/access/nbtree/nbtsearch.c | 1 +
src/backend/access/spgist/spgscan.c | 1 +
src/backend/commands/explain.c | 38 +++++++++++++
doc/src/sgml/bloom.sgml | 6 +-
doc/src/sgml/monitoring.sgml | 12 +++-
doc/src/sgml/perform.sgml | 8 +++
doc/src/sgml/ref/explain.sgml | 3 +-
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 ++
21 files changed, 192 insertions(+), 45 deletions(-)
diff --git a/src/include/access/relscan.h b/src/include/access/relscan.h
index 114a85dc4..361c33fca 100644
--- a/src/include/access/relscan.h
+++ b/src/include/access/relscan.h
@@ -131,6 +131,9 @@ typedef struct IndexScanDescData
bool xactStartedInRecovery; /* prevents killing/seeing killed
* tuples */
+ /* index access method instrumentation output state */
+ uint64 nsearches; /* # of index searches */
+
/* 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 60853a0f6..879d5589d 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -582,6 +582,7 @@ 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 f2fd62afb..5e423e155 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->nsearches++;
}
void
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index b35b8a975..36f1435cb 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->nsearches++;
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->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 0d99d6abc..927ba1039 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->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 4ec43e3c0..466d766b0 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->nsearches = 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 56e502c4f..b413433d9 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_nsearches; /* instrumentation */
slock_t btps_mutex; /* protects above variables, btps_arrElems */
ConditionVariable btps_cv; /* used to synchronize parallel scan */
@@ -550,6 +551,7 @@ btinitparallelscan(void *target)
SpinLockInit(&bt_target->btps_mutex);
bt_target->btps_scanPage = InvalidBlockNumber;
bt_target->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED;
+ bt_target->btps_nsearches = 0;
ConditionVariableInit(&bt_target->btps_cv);
}
@@ -575,6 +577,7 @@ btparallelrescan(IndexScanDesc scan)
SpinLockAcquire(&btscan->btps_mutex);
btscan->btps_scanPage = InvalidBlockNumber;
btscan->btps_pageStatus = BTPARALLEL_NOT_INITIALIZED;
+ /* deliberately don't reset btps_nsearches (matches index_rescan) */
SpinLockRelease(&btscan->btps_mutex);
}
@@ -683,6 +686,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_nsearches++;
+ }
btscan->btps_pageStatus = BTPARALLEL_ADVANCING;
*pageno = btscan->btps_scanPage;
exit_loop = true;
@@ -764,6 +772,8 @@ _bt_parallel_done(IndexScanDesc scan)
btscan->btps_pageStatus = BTPARALLEL_DONE;
status_changed = true;
}
+ /* Copy the authoritative shared primitive scan counter to local field */
+ scan->nsearches = btscan->btps_nsearches;
SpinLockRelease(&btscan->btps_mutex);
/* wake up all the workers associated with this parallel scan */
@@ -797,6 +807,7 @@ _bt_parallel_primscan_schedule(IndexScanDesc scan, BlockNumber prev_scan_page)
{
btscan->btps_scanPage = InvalidBlockNumber;
btscan->btps_pageStatus = BTPARALLEL_NEED_PRIMSCAN;
+ btscan->btps_nsearches++;
/* 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 fff7c89ea..b11112539 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -963,6 +963,7 @@ _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 301786185..be668abf2 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -421,6 +421,7 @@ 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 aaec43989..6f6d5a8c2 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_nsearches(PlanState *planstate, ExplainState *es);
static void show_qual(List *qual, const char *qlabel,
PlanState *planstate, List *ancestors,
bool useprefix, ExplainState *es);
@@ -1989,6 +1991,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_IndexScan:
show_scan_qual(((IndexScan *) plan)->indexqualorig,
"Index Cond", planstate, ancestors, es);
+ if (es->analyze)
+ show_indexscan_nsearches(planstate, es);
if (((IndexScan *) plan)->indexqualorig)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
@@ -2002,6 +2006,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_IndexOnlyScan:
show_scan_qual(((IndexOnlyScan *) plan)->indexqual,
"Index Cond", planstate, ancestors, es);
+ if (es->analyze)
+ show_indexscan_nsearches(planstate, es);
if (((IndexOnlyScan *) plan)->recheckqual)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
@@ -2018,6 +2024,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_BitmapIndexScan:
show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
"Index Cond", planstate, ancestors, es);
+ if (es->analyze)
+ show_indexscan_nsearches(planstate, es);
break;
case T_BitmapHeapScan:
show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig,
@@ -2528,6 +2536,36 @@ show_expression(Node *node, const char *qlabel,
ExplainPropertyText(qlabel, exprstr, es);
}
+/*
+ * Show the number of index searches within an IndexScan node, IndexOnlyScan
+ * node, or BitmapIndexScan node
+ */
+static void
+show_indexscan_nsearches(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->nsearches > 0)
+ ExplainPropertyUInteger("Index Searches", NULL,
+ scanDesc->nsearches, 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..92b13f539 100644
--- a/doc/src/sgml/bloom.sgml
+++ b/doc/src/sgml/bloom.sgml
@@ -170,9 +170,10 @@ 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))
+ Index Searches: 1
Planning Time: 0.099 ms
Execution Time: 0.408 ms
-(8 rows)
+(9 rows)
</programlisting>
</para>
@@ -202,11 +203,12 @@ 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)
+ Index Searches: 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
Execution Time: 0.055 ms
-(9 rows)
+(10 rows)
</programlisting>
Although this query runs much faster than with either of the single
indexes, we pay a penalty in index size. Each of the single-column
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a2fda4677..6b21bb85f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4153,12 +4153,18 @@ description | Waiting for a newly initialized WAL file to reach durable storage
Queries that use certain <acronym>SQL</acronym> constructs to search for
rows matching any value out of a list or array of multiple scalar values
(see <xref linkend="functions-comparisons"/>) perform multiple
- <quote>primitive</quote> index scans (up to one primitive scan per scalar
- value) during query execution. Each internal primitive index scan
- increments <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
+ index searches (up to one index search per scalar value) during query
+ execution. Each internal index search increments
+ <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
so it's possible for the count of index scans to significantly exceed the
total number of index scan executor node executions.
</para>
+ <para>
+ <command>EXPLAIN ANALYZE</command> breaks down the total number of index
+ searches performed by each index scan node. <literal>Index Searches: N</literal>
+ indicates the total number of searches across <emphasis>all</emphasis>
+ executor node executions/loops.
+ </para>
</note>
</sect2>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index ff689b652..1f2172960 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)
+ Index Searches: 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)
+ Index Searches: 1
Planning Time: 0.485 ms
Execution Time: 0.073 ms
</screen>
@@ -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)
+ Index Searches: 1
Planning Time: 0.187 ms
Execution Time: 3.036 ms
</screen>
@@ -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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 1
Planning Time: 0.151 ms
Execution Time: 1.856 ms
@@ -1017,6 +1024,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000
Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1)
Index Cond: (unique2 > 9000)
+ Index Searches: 1
Filter: (unique1 < 100)
Rows Removed by Filter: 287
Planning Time: 0.077 ms
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index db9d3a854..e042638b7 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -502,9 +502,10 @@ 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))
+ Index Searches: 1
Planning Time: 0.244 ms
Execution Time: 0.073 ms
-(7 rows)
+(8 rows)
</programlisting>
</para>
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7a928bd7b..7a00e4c0e 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1045,6 +1045,7 @@ SELECT count(*) FROM words WHERE word = 'caterpiler';
Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
-> 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)
+ Index Searches: 1
Heap Fetches: 0
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..c24d56007 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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 9ee09fe2f..1448179fb 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, 'Index Searches: 0', 'Index Searches: Zero');
+ ln := regexp_replace(ln, 'Index Searches: \d+', 'Index Searches: N');
return next ln;
end loop;
end;
@@ -48,8 +50,9 @@ WHERE t2.unique1 < 1000;', false);
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.twenty)
+ Index Searches: N
Heap Fetches: N
-(12 rows)
+(13 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -79,8 +82,9 @@ WHERE t1.unique1 < 1000;', false);
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
Index Cond: (unique1 = t1.twenty)
+ Index Searches: N
Heap Fetches: N
-(12 rows)
+(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)
+ Index Searches: 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)
+ Index Searches: N
+(15 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -146,10 +152,11 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
Cache Mode: binary
Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
+ Index Searches: N
Filter: ((t1.two + 1) = unique1)
Rows Removed by Filter: 9999
Heap Fetches: N
-(13 rows)
+(14 rows)
-- And check we get the expected results.
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -217,9 +224,10 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2 loops=N)
Index Cond: (x = (t1.t)::numeric)
+ Index Searches: N
Filter: (t1.x = (t)::numeric)
Heap Fetches: N
-(10 rows)
+(11 rows)
DROP TABLE expr_key;
-- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -245,8 +253,9 @@ WHERE t2.unique1 < 1200;', true);
Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.thousand)
+ Index Searches: N
Heap Fetches: N
-(12 rows)
+(13 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -260,6 +269,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)
+ Index Searches: N
Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
@@ -267,8 +277,9 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f = f1.f)
+ Index Searches: N
Heap Fetches: N
-(10 rows)
+(12 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -277,6 +288,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)
+ Index Searches: N
Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
@@ -284,8 +296,9 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f <= f1.f)
+ Index Searches: N
Heap Fetches: N
-(10 rows)
+(12 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -312,7 +325,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)
-(10 rows)
+ Index Searches: N
+(11 rows)
-- Ensure we get 3 hits and 3 misses
SELECT explain_memoize('
@@ -329,7 +343,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)
-(10 rows)
+ Index Searches: N
+(11 rows)
DROP TABLE strtest;
-- Ensure memoize works with partitionwise join
@@ -349,6 +364,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Append (actual rows=32 loops=N)
-> Nested Loop (actual rows=16 loops=N)
-> Index Only Scan using iprt_p1_a on prt_p1 t1_1 (actual rows=4 loops=N)
+ Index Searches: N
Heap Fetches: N
-> Memoize (actual rows=4 loops=N)
Cache Key: t1_1.a
@@ -356,9 +372,11 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4 loops=N)
Index Cond: (a = t1_1.a)
+ Index Searches: N
Heap Fetches: 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)
+ Index Searches: N
Heap Fetches: N
-> Memoize (actual rows=4 loops=N)
Cache Key: t1_2.a
@@ -366,8 +384,9 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4 loops=N)
Index Cond: (a = t1_2.a)
+ Index Searches: N
Heap Fetches: N
-(21 rows)
+(25 rows)
-- Ensure memoize works with parameterized union-all Append path
SET enable_partitionwise_join TO off;
@@ -379,6 +398,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)
+ Index Searches: N
Heap Fetches: N
-> Memoize (actual rows=4 loops=N)
Cache Key: t1.a
@@ -387,11 +407,13 @@ ON t1.a = t2.a;', false);
-> Append (actual rows=4 loops=N)
-> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4 loops=N)
Index Cond: (a = t1.a)
+ Index Searches: N
Heap Fetches: N
-> Index Only Scan using iprt_p2_a on prt_p2 (actual rows=0 loops=N)
Index Cond: (a = t1.a)
+ Index Searches: N
Heap Fetches: N
-(14 rows)
+(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..18ea272b6 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, 'Index Searches: \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)
+ 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)
-(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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 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)
+ Index Searches: 2
-> Index Scan using tprt2_idx on tprt_2 (actual rows=2 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 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)
+ Index Searches: 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)
+ Index Searches: 5
-> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
Index Cond: (col1 < tbl1.col1)
+ Index Searches: 4
-> Index Scan using tprt3_idx on tprt_3 (actual rows=1 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 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)
+ Index Searches: 2
-> Index Scan using tprt3_idx on tprt_3 (actual rows=0 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 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)
+ Index Searches: 1
+(16 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 < tprt.col1
@@ -3482,12 +3504,14 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
Sort Key: ma_test.b
Subplans Removed: 1
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
+ Index Searches: 1
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
+ Index Searches: 1
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
-(9 rows)
+(11 rows)
execute mt_q1(15);
a
@@ -3503,9 +3527,10 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
Sort Key: ma_test.b
Subplans Removed: 2
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
+ Index Searches: 1
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
-(6 rows)
+(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)
+ 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 Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1)
+ Index Searches: 1
Filter: (a >= (InitPlan 2).col1)
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1)
+ Index Searches: 1
Filter: (a >= (InitPlan 2).col1)
-(14 rows)
+(17 rows)
reset enable_seqscan;
reset enable_sort;
@@ -4129,14 +4157,17 @@ select * from rangep where b IN((select 1),(select 2)) order by a;
-> Merge Append (actual rows=0 loops=1)
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)
+ Index Searches: 1
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
-> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1)
+ Index Searches: 1
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
-> 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)
+ Index Searches: 1
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
-(15 rows)
+(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..b7cf35b9a 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -763,8 +763,9 @@ 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)
+ Index Searches: 1
Filter: (stringu1 = 'ATAAAA'::name)
-(3 rows)
+(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 2eaeb1477..9afe205e0 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, 'Index Searches: 0', 'Index Searches: Zero');
+ ln := regexp_replace(ln, 'Index Searches: \d+', 'Index Searches: 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..085e746af 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, 'Index Searches: \d+');
+ if found then
+ continue;
+ end if;
return next ln;
end loop;
end;
--
2.45.2