Skip to content

Commit a2822fb

Browse files
tglsfdctglsfdc
committed
Support index-only scans using the visibility map to avoid heap fetches.
When a btree index contains all columns required by the query, and the visibility map shows that all tuples on a target heap page are visible-to-all, we don't need to fetch that heap page. This patch depends on the previous patches that made the visibility map reliable. There's a fair amount left to do here, notably trying to figure out a less chintzy way of estimating the cost of an index-only scan, but the core functionality seems ready to commit. Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.
1 parent caa1054 commit a2822fb

File tree

34 files changed

+704
-203
lines changed

34 files changed

+704
-203
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,13 @@
476476
<entry>Does the access method support multicolumn indexes?</entry>
477477
</row>
478478

479+
<row>
480+
<entry><structfield>amcanreturn</structfield></entry>
481+
<entry><type>bool</type></entry>
482+
<entry></entry>
483+
<entry>Can the access method return the contents of index entries?</entry>
484+
</row>
485+
479486
<row>
480487
<entry><structfield>amoptionalkey</structfield></entry>
481488
<entry><type>bool</type></entry>

‎doc/src/sgml/config.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2404,6 +2404,22 @@ SET ENABLE_SEQSCAN TO OFF;
24042404
</listitem>
24052405
</varlistentry>
24062406

2407+
<varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
2408+
<term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)</term>
2409+
<indexterm>
2410+
<primary>index-only scan</primary>
2411+
</indexterm>
2412+
<indexterm>
2413+
<primary><varname>enable_indexonlyscan</> configuration parameter</primary>
2414+
</indexterm>
2415+
<listitem>
2416+
<para>
2417+
Enables or disables the query planner's use of index-only-scan plan
2418+
types. The default is <literal>on</>.
2419+
</para>
2420+
</listitem>
2421+
</varlistentry>
2422+
24072423
<varlistentry id="guc-enable-material" xreflabel="enable_material">
24082424
<term><varname>enable_material</varname> (<type>boolean</type>)</term>
24092425
<indexterm>
@@ -6353,7 +6369,7 @@ LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
63536369
<row>
63546370
<entry>
63556371
<option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
6356-
<option>-fm</option>, <option>-fn</option>,
6372+
<option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
63576373
<option>-fs</option>, <option>-ft</option>
63586374
</entry>
63596375
<entry>
@@ -6362,6 +6378,7 @@ LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
63626378
<literal>enable_indexscan = off</>,
63636379
<literal>enable_mergejoin = off</>,
63646380
<literal>enable_nestloop = off</>,
6381+
<literal>enable_indexonlyscan = off</>,
63656382
<literal>enable_seqscan = off</>,
63666383
<literal>enable_tidscan = off</>
63676384
</entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -134,6 +134,11 @@
134134
<structfield>amsearchnulls</structfield>, indicating that it supports
135135
<literal>IS NULL</> and <literal>IS NOT NULL</> clauses as search
136136
conditions.
137+
An index method can also set <structfield>amcanreturn</structfield>,
138+
indicating that it can support <firstterm>index-only scans</> by returning
139+
the indexed column values for an index entry in the form of an IndexTuple.
140+
(An example of an index AM that cannot do this is hash, which stores only
141+
the hash values not the original data.)
137142
</para>
138143

139144
</sect1>
@@ -385,6 +390,18 @@ amgettuple (IndexScanDesc scan,
385390
callers.
386391
</para>
387392

393+
<para>
394+
If the access method supports index-only scans (i.e.,
395+
<structfield>amcanreturn</structfield> is TRUE in its <structname>pg_am</>
396+
row), then on success it must also check
397+
<literal>scan-&gt;xs_want_itup</>, and if that is true it should return
398+
the original indexed data for the index entry, in the form of an
399+
<structname>IndexTuple</> stored at <literal>scan-&gt;xs_itup</>. However,
400+
it is permissible for the access method to sometimes fail to provide this
401+
data, in which case it must set <literal>scan-&gt;xs_itup</> to NULL. That
402+
will result in a regular heap fetch occurring.
403+
</para>
404+
388405
<para>
389406
The <function>amgettuple</> function need only be provided if the access
390407
method supports <quote>plain</> index scans. If it doesn't, the
@@ -581,6 +598,15 @@ amrestrpos (IndexScanDesc scan);
581598
deleted.
582599
</para>
583600

601+
<para>
602+
If the index stores the original indexed data values (and not some lossy
603+
representation of them), it is useful to support index-only scans, in
604+
which the index returns the actual data not just the TID of the heap tuple.
605+
This will only work if the visibility map shows that the TID is on an
606+
all-visible page; else the heap tuple must be visited anyway to check
607+
MVCC visibility. But that is no concern of the access method's.
608+
</para>
609+
584610
<para>
585611
Instead of using <function>amgettuple</>, an index scan can be done with
586612
<function>amgetbitmap</> to fetch all tuples in one call. This can be
@@ -593,7 +619,11 @@ amrestrpos (IndexScanDesc scan);
593619
supported. Secondly, the tuples are returned in a bitmap which doesn't
594620
have any specific ordering, which is why <function>amgetbitmap</> doesn't
595621
take a <literal>direction</> argument. (Ordering operators will never be
596-
supplied for such a scan, either.) Finally, <function>amgetbitmap</>
622+
supplied for such a scan, either.)
623+
Also, there is no provision for index-only scans with
624+
<function>amgetbitmap</>, since there is no way to return the contents of
625+
index tuples.
626+
Finally, <function>amgetbitmap</>
597627
does not guarantee any locking of the returned tuples, with implications
598628
spelled out in <xref linkend="index-locking">.
599629
</para>

‎doc/src/sgml/ref/postgres-ref.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -376,12 +376,15 @@ PostgreSQL documentation
376376

377377
<variablelist>
378378
<varlistentry>
379-
<term><option>-f</option> <literal>{ s | i | m | n | h }</literal></term>
379+
<term><option>-f</option> <literal>{ s | i | o | b | t | n | m | h }</literal></term>
380380
<listitem>
381381
<para>
382382
Forbids the use of particular scan and join methods:
383383
<literal>s</literal> and <literal>i</literal>
384-
disable sequential and index scans respectively, while
384+
disable sequential and index scans respectively,
385+
<literal>o</literal>, <literal>b</literal> and <literal>t</literal>
386+
disable index-only scans, bitmap index scans, and TID scans
387+
respectively, while
385388
<literal>n</literal>, <literal>m</literal>, and <literal>h</literal>
386389
disable nested-loop, merge and hash joins respectively.
387390
</para>

‎src/backend/access/index/genam.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,8 @@ RelationGetIndexScan(Relation indexRelation, int nkeys, int norderbys)
9393
else
9494
scan->orderByData = NULL;
9595

96+
scan->xs_want_itup = false; /* may be set later */
97+
9698
/*
9799
* During recovery we ignore killed tuples and don't bother to kill them
98100
* either. We do this because the xmin on the primary node could easily be
@@ -109,6 +111,8 @@ RelationGetIndexScan(Relation indexRelation, int nkeys, int norderbys)
109111

110112
scan->opaque = NULL;
111113

114+
scan->xs_itup = NULL;
115+
112116
ItemPointerSetInvalid(&scan->xs_ctup.t_self);
113117
scan->xs_ctup.t_data = NULL;
114118
scan->xs_cbuf = InvalidBuffer;

0 commit comments

Comments
 (0)