Home Explore Blog CI



postgresql

13th chunk of `doc/src/sgml/perform.sgml`
a69edc6c3b392cb2dbe0fc6dc938d514d1a0708d466feacc0000000100000fb0
 shown.)
   </para>

   <para>
    Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes)
    show an <quote>Index Searches</quote> line that reports the total number
    of searches across <emphasis>all</emphasis> node
    executions/<literal>loops</literal>:

<screen>
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999);
                                                            QUERY PLAN
-------------------------------------------------------------------&zwsp;---------------------------------------------------------------
 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)
   Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
   Heap Blocks: exact=39
   Buffers: shared hit=47
   ->  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)
         Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[]))
         Index Searches: 4
         Buffers: shared hit=8
 Planning Time: 0.029 ms
 Execution Time: 0.034 ms
</screen>

    Here we see a Bitmap Index Scan node that needed 4 separate index
    searches.  The scan had to search the index from the
    <structname>tenk1_thous_tenthous</structname> index root page once per
    <type>integer</type> value from the predicate's <literal>IN</literal>
    construct.  However, the number of index searches often won't have such a
    simple correspondence to the query predicate:

<screen>
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4);
                                                            QUERY PLAN
-------------------------------------------------------------------&zwsp;---------------------------------------------------------------
 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)
   Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
   Heap Blocks: exact=38
   Buffers: shared hit=40
   ->  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)
         Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[]))
         Index Searches: 1
         Buffers: shared hit=2
 Planning Time: 0.029 ms
 Execution Time: 0.026 ms
</screen>

    This variant of our <literal>IN</literal> query performed only 1 index
    search.  It spent less time traversing the index (compared to the original
    query) because its <literal>IN</literal> construct uses values matching
    index tuples stored next to each other, on the same
    <structname>tenk1_thous_tenthous</structname> index leaf page.
   </para>

   <para>
    The <quote>Index Searches</quote> line is also useful with B-tree index
    scans that apply the <firstterm>skip scan</firstterm> optimization to
    more efficiently traverse through an index:
<screen>
EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42;
                                                              QUERY PLAN
-------------------------------------------------------------------&zwsp;---------------------------------------------------------------
 Index Only Scan using tenk1_four_unique1_idx on tenk1  (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
   Index Cond: ((four &gt;= 1) AND (four &lt;= 3) AND (unique1 = 42))
   Heap Fetches: 0
   Index Searches: 3
   Buffers: shared hit=7
 Planning Time: 0.029 ms
 Execution Time: 0.012 ms
</screen>

    Here we see an Index-Only Scan node using
    <structname>tenk1_four_unique1_idx</structname>, a multi-column index on the
    <structname>tenk1</structname> table's <structfield>four</structfield> and
    <structfield>unique1</structfield> columns.  The scan performs 3 searches
    that each read a single index leaf page:
    <quote><literal>four = 1 AND unique1 = 42</literal></quote>,
    <quote><literal>four = 2 AND unique1 = 42</literal></quote>,

Title: Understanding Index Searches in Query Plans
Summary
The 'Index Searches' line in EXPLAIN ANALYZE output reports the total number of searches across all node executions, providing insight into index usage and optimization opportunities, such as skip scan optimization and index leaf page traversal, for various query types including IN constructs and range queries.