Skip to content

Commit d00107c

Browse files
petergeogheganpetergeoghegan
committed
Revert "Show index search count in EXPLAIN ANALYZE."
This reverts commit 5ead85f. This commit shows test failures with debug_parallel_query=regress. The underlying issue needs to be debugged, so revert for now.
1 parent 4603903 commit d00107c

File tree

22 files changed

+49
-310
lines changed

22 files changed

+49
-310
lines changed

‎contrib/bloom/blscan.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,6 @@ blgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
116116
bas = GetAccessStrategy(BAS_BULKREAD);
117117
npages = RelationGetNumberOfBlocks(scan->indexRelation);
118118
pgstat_count_index_scan(scan->indexRelation);
119-
scan->nsearches++;
120119

121120
for (blkno = BLOOM_HEAD_BLKNO; blkno < npages; blkno++)
122121
{

‎doc/src/sgml/bloom.sgml

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -173,11 +173,10 @@ CREATE INDEX
173173
Buffers: shared hit=21864
174174
-&gt; Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=1 width=0) (actual time=20.005..20.005 rows=2300.00 loops=1)
175175
Index Cond: ((i2 = 898732) AND (i5 = 123451))
176-
Index Searches: 1
177176
Buffers: shared hit=19608
178177
Planning Time: 0.099 ms
179178
Execution Time: 22.632 ms
180-
(11 rows)
179+
(10 rows)
181180
</programlisting>
182181
</para>
183182

@@ -209,15 +208,13 @@ CREATE INDEX
209208
Buffers: shared hit=6
210209
-&gt; Bitmap Index Scan on btreeidx5 (cost=0.00..4.52 rows=11 width=0) (actual time=0.026..0.026 rows=7.00 loops=1)
211210
Index Cond: (i5 = 123451)
212-
Index Searches: 1
213211
Buffers: shared hit=3
214212
-&gt; Bitmap Index Scan on btreeidx2 (cost=0.00..4.52 rows=11 width=0) (actual time=0.007..0.007 rows=8.00 loops=1)
215213
Index Cond: (i2 = 898732)
216-
Index Searches: 1
217214
Buffers: shared hit=3
218215
Planning Time: 0.264 ms
219216
Execution Time: 0.047 ms
220-
(15 rows)
217+
(13 rows)
221218
</programlisting>
222219
Although this query runs much faster than with either of the single
223220
indexes, we pay a penalty in index size. Each of the single-column

‎doc/src/sgml/monitoring.sgml

Lines changed: 6 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -4234,32 +4234,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
42344234

42354235
<note>
42364236
<para>
4237-
Index scans may sometimes perform multiple index searches per execution.
4238-
Each index search increments <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
4237+
Queries that use certain <acronym>SQL</acronym> constructs to search for
4238+
rows matching any value out of a list or array of multiple scalar values
4239+
(see <xref linkend="functions-comparisons"/>) perform multiple
4240+
<quote>primitive</quote> index scans (up to one primitive scan per scalar
4241+
value) during query execution. Each internal primitive index scan
4242+
increments <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
42394243
so it's possible for the count of index scans to significantly exceed the
42404244
total number of index scan executor node executions.
42414245
</para>
4242-
<para>
4243-
This can happen with queries that use certain <acronym>SQL</acronym>
4244-
constructs to search for rows matching any value out of a list or array of
4245-
multiple scalar values (see <xref linkend="functions-comparisons"/>). It
4246-
can also happen to queries with a
4247-
<literal><replaceable>column_name</replaceable> =
4248-
<replaceable>value1</replaceable> OR
4249-
<replaceable>column_name</replaceable> =
4250-
<replaceable>value2</replaceable> ...</literal> construct, though only
4251-
when the optimizer transforms the construct into an equivalent
4252-
multi-valued array representation.
4253-
</para>
42544246
</note>
4255-
<tip>
4256-
<para>
4257-
<command>EXPLAIN ANALYZE</command> outputs the total number of index
4258-
searches performed by each index scan node. See
4259-
<xref linkend="using-explain-analyze"/> for an example demonstrating how
4260-
this works.
4261-
</para>
4262-
</tip>
42634247

42644248
</sect2>
42654249

‎doc/src/sgml/perform.sgml

Lines changed: 0 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -729,11 +729,9 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
729729
Buffers: shared hit=3 read=5 written=4
730730
-&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1)
731731
Index Cond: (unique1 &lt; 10)
732-
Index Searches: 1
733732
Buffers: shared hit=2
734733
-&gt; 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)
735734
Index Cond: (unique2 = t1.unique2)
736-
Index Searches: 10
737735
Buffers: shared hit=24 read=6
738736
Planning:
739737
Buffers: shared hit=15 dirtied=9
@@ -792,7 +790,6 @@ WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
792790
Buffers: shared hit=92
793791
-&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1)
794792
Index Cond: (unique1 &lt; 100)
795-
Index Searches: 1
796793
Buffers: shared hit=2
797794
Planning:
798795
Buffers: shared hit=12
@@ -808,58 +805,6 @@ WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
808805
shown.)
809806
</para>
810807

811-
<para>
812-
Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes)
813-
show an <quote>Index Searches</quote> line that reports the total number
814-
of searches across <emphasis>all</emphasis> node
815-
executions/<literal>loops</literal>:
816-
817-
<screen>
818-
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
819-
QUERY PLAN
820-
-------------------------------------------------------------------&zwsp;---------------------------------------------------------
821-
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.012..0.028 rows=40.00 loops=1)
822-
Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
823-
Heap Blocks: exact=39
824-
Buffers: shared hit=47
825-
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.009..0.009 rows=40.00 loops=1)
826-
Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
827-
Index Searches: 4
828-
Buffers: shared hit=8
829-
Planning Time: 0.037 ms
830-
Execution Time: 0.034 ms
831-
</screen>
832-
833-
Here we see a Bitmap Index Scan node that needed 4 separate index
834-
searches. The scan had to search the index from the
835-
<structname>tenk1_thous_tenthous</structname> index root page once per
836-
<type>integer</type> value from the predicate's <literal>IN</literal>
837-
construct. However, the number of index searches often won't have such a
838-
simple correspondence to the query predicate:
839-
840-
<screen>
841-
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
842-
QUERY PLAN
843-
----------------------------------------------------------------------------------------------------------------------------------
844-
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.009..0.019 rows=40.00 loops=1)
845-
Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
846-
Heap Blocks: exact=38
847-
Buffers: shared hit=40
848-
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.005..0.005 rows=40.00 loops=1)
849-
Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
850-
Index Searches: 1
851-
Buffers: shared hit=2
852-
Planning Time: 0.029 ms
853-
Execution Time: 0.026 ms
854-
</screen>
855-
856-
This variant of our <literal>IN</literal> query performed only 1 index
857-
search. It spent less time traversing the index (compared to the original
858-
query) because its <literal>IN</literal> construct uses values matching
859-
index tuples stored next to each other, on the same
860-
<structname>tenk1_thous_tenthous</structname> index leaf page.
861-
</para>
862-
863808
<para>
864809
Another type of extra information is the number of rows removed by a
865810
filter condition:
@@ -916,7 +861,6 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
916861
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1)
917862
Index Cond: (f1 @&gt; '((0.5,2))'::polygon)
918863
Rows Removed by Index Recheck: 1
919-
Index Searches: 1
920864
Buffers: shared hit=1
921865
Planning Time: 0.039 ms
922866
Execution Time: 0.098 ms
@@ -950,10 +894,8 @@ EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND un
950894
-&gt; BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1)
951895
-&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1)
952896
Index Cond: (unique1 &lt; 100)
953-
Index Searches: 1
954897
-&gt; Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1)
955898
Index Cond: (unique2 &gt; 9000)
956-
Index Searches: 1
957899
Planning Time: 0.162 ms
958900
Execution Time: 0.143 ms
959901
</screen>
@@ -981,7 +923,6 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 &lt; 100;
981923
Buffers: shared hit=4 read=2
982924
-&gt; Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)
983925
Index Cond: (unique1 &lt; 100)
984-
Index Searches: 1
985926
Buffers: shared read=2
986927
Planning Time: 0.151 ms
987928
Execution Time: 1.856 ms
@@ -1120,7 +1061,6 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000
11201061
Index Cond: (unique2 &gt; 9000)
11211062
Filter: (unique1 &lt; 100)
11221063
Rows Removed by Filter: 287
1123-
Index Searches: 1
11241064
Buffers: shared hit=16
11251065
Planning Time: 0.077 ms
11261066
Execution Time: 0.086 ms

‎doc/src/sgml/ref/explain.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -506,11 +506,10 @@ EXPLAIN ANALYZE EXECUTE query(100, 200);
506506
Buffers: shared hit=4
507507
-&gt; Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)
508508
Index Cond: ((id &gt; 100) AND (id &lt; 200))
509-
Index Searches: 1
510509
Buffers: shared hit=4
511510
Planning Time: 0.244 ms
512511
Execution Time: 0.073 ms
513-
(10 rows)
512+
(9 rows)
514513
</programlisting>
515514
</para>
516515

‎doc/src/sgml/rules.sgml

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1046,7 +1046,6 @@ SELECT count(*) FROM words WHERE word = 'caterpiler';
10461046
-&gt; 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.00 loops=1)
10471047
Index Cond: (word = 'caterpiler'::text)
10481048
Heap Fetches: 0
1049-
Index Searches: 1
10501049
Planning time: 0.164 ms
10511050
Execution time: 0.117 ms
10521051
</programlisting>
@@ -1091,7 +1090,6 @@ SELECT word FROM words ORDER BY word &lt;-&gt; 'caterpiler' LIMIT 10;
10911090
Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10.00 loops=1)
10921091
-&gt; Index Scan using wrd_trgm on wrd (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10.00 loops=1)
10931092
Order By: (word &lt;-&gt; 'caterpiler'::text)
1094-
Index Searches: 1
10951093
Planning time: 0.196 ms
10961094
Execution time: 198.640 ms
10971095
</programlisting>

‎src/backend/access/brin/brin.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -591,7 +591,6 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
591591
opaque = (BrinOpaque *) scan->opaque;
592592
bdesc = opaque->bo_bdesc;
593593
pgstat_count_index_scan(idxRel);
594-
scan->nsearches++;
595594

596595
/*
597596
* We need to know the size of the table so that we know how long to

‎src/backend/access/gin/ginscan.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -437,7 +437,6 @@ ginNewScanKey(IndexScanDesc scan)
437437
MemoryContextSwitchTo(oldCtx);
438438

439439
pgstat_count_index_scan(scan->indexRelation);
440-
scan->nsearches++;
441440
}
442441

443442
void

‎src/backend/access/gist/gistget.c

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -625,7 +625,6 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir)
625625
GISTSearchItem fakeItem;
626626

627627
pgstat_count_index_scan(scan->indexRelation);
628-
scan->nsearches++;
629628

630629
so->firstCall = false;
631630
so->curPageData = so->nPageData = 0;
@@ -751,7 +750,6 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
751750
return 0;
752751

753752
pgstat_count_index_scan(scan->indexRelation);
754-
scan->nsearches++;
755753

756754
/* Begin the scan by processing the root page */
757755
so->curPageData = so->nPageData = 0;

‎src/backend/access/hash/hashsearch.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -298,7 +298,6 @@ _hash_first(IndexScanDesc scan, ScanDirection dir)
298298
HashScanPosItem *currItem;
299299

300300
pgstat_count_index_scan(rel);
301-
scan->nsearches++;
302301

303302
/*
304303
* We do not support hash scans with no index qualification, because we

0 commit comments

Comments
 (0)