Skip to content

Commit e6dd664

Browse files
tglsfdctglsfdc
committed
Docs: create some user-facing documentation about index-only scans.
We didn't have any real user documentation about how index-only scans work or how to design indexes to exploit them. Remedy that. Per gripe from David Johnston.
1 parent 6f69b96 commit e6dd664

File tree

5 files changed

+217
-27
lines changed

5 files changed

+217
-27
lines changed

‎doc/src/sgml/config.sgml

+2-4
Original file line numberDiff line numberDiff line change
@@ -3406,17 +3406,15 @@ include_dir 'conf.d'
34063406

34073407
<varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
34083408
<term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)
3409-
<indexterm>
3410-
<primary>index-only scan</primary>
3411-
</indexterm>
34123409
<indexterm>
34133410
<primary><varname>enable_indexonlyscan</> configuration parameter</primary>
34143411
</indexterm>
34153412
</term>
34163413
<listitem>
34173414
<para>
34183415
Enables or disables the query planner's use of index-only-scan plan
3419-
types. The default is <literal>on</>.
3416+
types (see <xref linkend="indexes-index-only-scans">).
3417+
The default is <literal>on</>.
34203418
</para>
34213419
</listitem>
34223420
</varlistentry>

‎doc/src/sgml/indexam.sgml

+10-8
Original file line numberDiff line numberDiff line change
@@ -354,10 +354,11 @@ amvacuumcleanup (IndexVacuumInfo *info,
354354
bool
355355
amcanreturn (Relation indexRelation, int attno);
356356
</programlisting>
357-
Check whether the index can support <firstterm>index-only scans</> on the
358-
given column, by returning the indexed column values for an index entry in
359-
the form of an <structname>IndexTuple</structname>. The attribute number
360-
is 1-based, i.e. the first columns attno is 1. Returns TRUE if supported,
357+
Check whether the index can support <link
358+
linkend="indexes-index-only-scans"><firstterm>index-only scans</></link> on
359+
the given column, by returning the indexed column values for an index entry
360+
in the form of an <structname>IndexTuple</structname>. The attribute number
361+
is 1-based, i.e. the first column's attno is 1. Returns TRUE if supported,
361362
else FALSE. If the access method does not support index-only scans at all,
362363
the <structfield>amcanreturn</> field in its <structname>IndexAmRoutine</>
363364
struct can be set to NULL.
@@ -489,8 +490,8 @@ amgettuple (IndexScanDesc scan,
489490
</para>
490491

491492
<para>
492-
If the index supports index-only scans (i.e.,
493-
<function>amcanreturn</function> returns TRUE for it),
493+
If the index supports <link linkend="indexes-index-only-scans">index-only
494+
scans</link> (i.e., <function>amcanreturn</function> returns TRUE for it),
494495
then on success the AM must also check
495496
<literal>scan-&gt;xs_want_itup</>, and if that is true it must return
496497
the original indexed data for the index entry, in the form of an
@@ -700,9 +701,10 @@ amrestrpos (IndexScanDesc scan);
700701

701702
<para>
702703
If the index stores the original indexed data values (and not some lossy
703-
representation of them), it is useful to support index-only scans, in
704+
representation of them), it is useful to
705+
support <link linkend="indexes-index-only-scans">index-only scans</link>, in
704706
which the index returns the actual data not just the TID of the heap tuple.
705-
This will only work if the visibility map shows that the TID is on an
707+
This will only avoid I/O if the visibility map shows that the TID is on an
706708
all-visible page; else the heap tuple must be visited anyway to check
707709
MVCC visibility. But that is no concern of the access method's.
708710
</para>

‎doc/src/sgml/indices.sgml

+193-8
Original file line numberDiff line numberDiff line change
@@ -302,9 +302,16 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
302302
<primary>GIN</primary>
303303
<see>index</see>
304304
</indexterm>
305-
GIN indexes are inverted indexes which can handle values that contain more
306-
than one key, arrays for example. Like GiST and SP-GiST, GIN can support
307-
many different user-defined indexing strategies and the particular
305+
GIN indexes are <quote>inverted indexes</> which are appropriate for
306+
data values that contain multiple component values, such as arrays. An
307+
inverted index contains a separate entry for each component value, and
308+
can efficiently handle queries that test for the presence of specific
309+
component values.
310+
</para>
311+
312+
<para>
313+
Like GiST and SP-GiST, GIN can support
314+
many different user-defined indexing strategies, and the particular
308315
operators with which a GIN index can be used vary depending on the
309316
indexing strategy.
310317
As an example, the standard distribution of
@@ -337,16 +344,16 @@ SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
337344
<primary>BRIN</primary>
338345
<see>index</see>
339346
</indexterm>
340-
BRIN indexes (a shorthand for Block Range indexes)
341-
store summaries about the values stored in consecutive table physical block ranges.
347+
BRIN indexes (a shorthand for Block Range INdexes) store summaries about
348+
the values stored in consecutive physical block ranges of a table.
342349
Like GiST, SP-GiST and GIN,
343350
BRIN can support many different indexing strategies,
344351
and the particular operators with which a BRIN index can be used
345352
vary depending on the indexing strategy.
346353
For data types that have a linear sort order, the indexed data
347354
corresponds to the minimum and maximum values of the
348-
values in the column for each block range,
349-
which support indexed queries using these operators:
355+
values in the column for each block range. This supports indexed queries
356+
using these operators:
350357

351358
<simplelist>
352359
<member><literal>&lt;</literal></member>
@@ -460,7 +467,8 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor);
460467
an index on a single column is sufficient and saves space and time.
461468
Indexes with more than three columns are unlikely to be helpful
462469
unless the usage of the table is extremely stylized. See also
463-
<xref linkend="indexes-bitmap-scans"> for some discussion of the
470+
<xref linkend="indexes-bitmap-scans"> and
471+
<xref linkend="indexes-index-only-scans"> for some discussion of the
464472
merits of different index configurations.
465473
</para>
466474
</sect1>
@@ -1140,6 +1148,183 @@ CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");
11401148
</sect1>
11411149

11421150

1151+
<sect1 id="indexes-index-only-scans">
1152+
<title>Index-Only Scans</title>
1153+
1154+
<indexterm zone="indexes-index-only-scans">
1155+
<primary>index</primary>
1156+
<secondary>index-only scans</secondary>
1157+
</indexterm>
1158+
<indexterm zone="indexes-index-only-scans">
1159+
<primary>index-only scan</primary>
1160+
</indexterm>
1161+
1162+
<para>
1163+
All indexes in <productname>PostgreSQL</> are <firstterm>secondary</>
1164+
indexes, meaning that each index is stored separately from the table's
1165+
main data area (which is called the table's <firstterm>heap</>
1166+
in <productname>PostgreSQL</> terminology). This means that in an
1167+
ordinary index scan, each row retrieval requires fetching data from both
1168+
the index and the heap. Furthermore, while the index entries that match a
1169+
given indexable <literal>WHERE</> condition are usually close together in
1170+
the index, the table rows they reference might be anywhere in the heap.
1171+
The heap-access portion of an index scan thus involves a lot of random
1172+
access into the heap, which can be slow, particularly on traditional
1173+
rotating media. (As described in <xref linkend="indexes-bitmap-scans">,
1174+
bitmap scans try to alleviate this cost by doing the heap accesses in
1175+
sorted order, but that only goes so far.)
1176+
</para>
1177+
1178+
<para>
1179+
To solve this performance problem, <productname>PostgreSQL</>
1180+
supports <firstterm>index-only scans</>, which can answer queries from an
1181+
index alone without any heap access. The basic idea is to return values
1182+
directly out of each index entry instead of consulting the associated heap
1183+
entry. There are two fundamental restrictions on when this method can be
1184+
used:
1185+
1186+
<orderedlist>
1187+
<listitem>
1188+
<para>
1189+
The index type must support index-only scans. B-tree indexes always
1190+
do. GiST and SP-GiST indexes support index-only scans for some
1191+
operator classes but not others. Other index types have no support.
1192+
The underlying requirement is that the index must physically store, or
1193+
else be able to reconstruct, the original data value for each index
1194+
entry. As a counterexample, GIN indexes cannot support index-only
1195+
scans because each index entry typically holds only part of the
1196+
original data value.
1197+
</para>
1198+
</listitem>
1199+
1200+
<listitem>
1201+
<para>
1202+
The query must reference only columns stored in the index. For
1203+
example, given an index on columns <literal>x</> and <literal>y</> of a
1204+
table that also has a column <literal>z</>, these queries could use
1205+
index-only scans:
1206+
<programlisting>
1207+
SELECT x, y FROM tab WHERE x = 'key';
1208+
SELECT x FROM tab WHERE x = 'key' AND y &lt; 42;
1209+
</programlisting>
1210+
but these queries could not:
1211+
<programlisting>
1212+
SELECT x, z FROM tab WHERE x = 'key';
1213+
SELECT x FROM tab WHERE x = 'key' AND z &lt; 42;
1214+
</programlisting>
1215+
(Expression indexes and partial indexes complicate this rule,
1216+
as discussed below.)
1217+
</para>
1218+
</listitem>
1219+
</orderedlist>
1220+
</para>
1221+
1222+
<para>
1223+
If these two fundamental requirements are met, then all the data values
1224+
required by the query are available from the index, so an index-only scan
1225+
is physically possible. But there is an additional requirement for any
1226+
table scan in <productname>PostgreSQL</>: it must verify that each
1227+
retrieved row be <quote>visible</> to the query's MVCC snapshot, as
1228+
discussed in <xref linkend="mvcc">. Visibility information is not stored
1229+
in index entries, only in heap entries; so at first glance it would seem
1230+
that every row retrieval would require a heap access anyway. And this is
1231+
indeed the case, if the table row has been modified recently. However,
1232+
for seldom-changing data there is a way around this
1233+
problem. <productname>PostgreSQL</> tracks, for each page in a table's
1234+
heap, whether all rows stored in that page are old enough to be visible to
1235+
all current and future transactions. This information is stored in a bit
1236+
in the table's <firstterm>visibility map</>. An index-only scan, after
1237+
finding a candidate index entry, checks the visibility map bit for the
1238+
corresponding heap page. If it's set, the row is known visible and so the
1239+
data can be returned with no further work. If it's not set, the heap
1240+
entry must be visited to find out whether it's visible, so no performance
1241+
advantage is gained over a standard index scan. Even in the successful
1242+
case, this approach trades visibility map accesses for heap accesses; but
1243+
since the visibility map is four orders of magnitude smaller than the heap
1244+
it describes, far less physical I/O is needed to access it. In most
1245+
situations the visibility map remains cached in memory all the time.
1246+
</para>
1247+
1248+
<para>
1249+
In short, while an index-only scan is possible given the two fundamental
1250+
requirements, it will be a win only if a significant fraction of the
1251+
table's heap pages have their all-visible map bits set. But tables in
1252+
which a large fraction of the rows are unchanging are common enough to
1253+
make this type of scan very useful in practice.
1254+
</para>
1255+
1256+
<para>
1257+
To make effective use of the index-only scan feature, you might choose to
1258+
create indexes in which only the leading columns are meant to
1259+
match <literal>WHERE</> clauses, while the trailing columns
1260+
hold <quote>payload</> data to be returned by a query. For example, if
1261+
you commonly run queries like
1262+
<programlisting>
1263+
SELECT y FROM tab WHERE x = 'key';
1264+
</programlisting>
1265+
the traditional approach to speeding up such queries would be to create an
1266+
index on <literal>x</> only. However, an index on <literal>(x, y)</>
1267+
would offer the possibility of implementing this query as an index-only
1268+
scan. As previously discussed, such an index would be larger and hence
1269+
more expensive than an index on <literal>x</> alone, so this is attractive
1270+
only if the table is known to be mostly static. Note it's important that
1271+
the index be declared on <literal>(x, y)</> not <literal>(y, x)</>, as for
1272+
most index types (particularly B-trees) searches that do not constrain the
1273+
leading index columns are not very efficient.
1274+
</para>
1275+
1276+
<para>
1277+
In principle, index-only scans can be used with expression indexes.
1278+
For example, given an index on <literal>f(x)</> where <literal>x</> is a
1279+
table column, it should be possible to execute
1280+
<programlisting>
1281+
SELECT f(x) FROM tab WHERE f(x) &lt; 1;
1282+
</programlisting>
1283+
as an index-only scan; and this is very attractive if <literal>f()</> is
1284+
an expensive-to-compute function. However, <productname>PostgreSQL</>'s
1285+
planner is currently not very smart about such cases. It considers a
1286+
query to be potentially executable by index-only scan only when
1287+
all <emphasis>columns</> needed by the query are available from the index.
1288+
In this example, <literal>x</> is not needed except in the
1289+
context <literal>f(x)</>, but the planner does not notice that and
1290+
concludes that an index-only scan is not possible. If an index-only scan
1291+
seems sufficiently worthwhile, this can be worked around by declaring the
1292+
index to be on <literal>(f(x), x)</>, where the second column is not
1293+
expected to be used in practice but is just there to convince the planner
1294+
that an index-only scan is possible. An additional caveat, if the goal is
1295+
to avoid recalculating <literal>f(x)</>, is that the planner won't
1296+
necessarily match uses of <literal>f(x)</> that aren't in
1297+
indexable <literal>WHERE</> clauses to the index column. It will usually
1298+
get this right in simple queries such as shown above, but not in queries
1299+
that involve joins. These deficiencies may be remedied in future versions
1300+
of <productname>PostgreSQL</>.
1301+
</para>
1302+
1303+
<para>
1304+
Partial indexes also have interesting interactions with index-only scans.
1305+
Consider the partial index shown in <xref linkend="indexes-partial-ex3">:
1306+
<programlisting>
1307+
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
1308+
WHERE success;
1309+
</programlisting>
1310+
In principle, we could do an index-only scan on this index to satisfy a
1311+
query like
1312+
<programlisting>
1313+
SELECT target FROM tests WHERE subject = 'some-subject' AND success;
1314+
</programlisting>
1315+
But there's a problem: the <literal>WHERE</> clause refers
1316+
to <literal>success</> which is not available as a result column of the
1317+
index. Nonetheless, an index-only scan is possible because the plan does
1318+
not need to recheck that part of the <literal>WHERE</> clause at runtime:
1319+
all entries found in the index necessarily have <literal>success = true</>
1320+
so this need not be explicitly checked in the
1321+
plan. <productname>PostgreSQL</> versions 9.6 and later will recognize
1322+
such cases and allow index-only scans to be generated, but older versions
1323+
will not.
1324+
</para>
1325+
</sect1>
1326+
1327+
11431328
<sect1 id="indexes-examine">
11441329
<title>Examining Index Usage</title>
11451330

‎doc/src/sgml/maintenance.sgml

+8-5
Original file line numberDiff line numberDiff line change
@@ -102,8 +102,9 @@
102102
</listitem>
103103

104104
<listitem>
105-
<simpara>To update the visibility map, which speeds up index-only
106-
scans.</simpara>
105+
<simpara>To update the visibility map, which speeds
106+
up <link linkend="indexes-index-only-scans">index-only
107+
scans</link>.</simpara>
107108
</listitem>
108109

109110
<listitem>
@@ -363,9 +364,11 @@
363364
Since <productname>PostgreSQL</productname> indexes don't contain tuple
364365
visibility information, a normal index scan fetches the heap tuple for each
365366
matching index entry, to check whether it should be seen by the current
366-
transaction. An <firstterm>index-only scan</>, on the other hand, checks
367-
the visibility map first. If it's known that all tuples on the page are
368-
visible, the heap fetch can be skipped. This is most noticeable on
367+
transaction.
368+
An <link linkend="indexes-index-only-scans"><firstterm>index-only
369+
scan</></link>, on the other hand, checks the visibility map first.
370+
If it's known that all tuples on the page are
371+
visible, the heap fetch can be skipped. This is most useful on
369372
large data sets where the visibility map can prevent disk accesses.
370373
The visibility map is vastly smaller than the heap, so it can easily be
371374
cached even when the heap is very large.

‎doc/src/sgml/storage.sgml

+4-2
Original file line numberDiff line numberDiff line change
@@ -636,9 +636,11 @@ Note that indexes do not have VMs.
636636
The visibility map stores two bits per heap page. The first bit, if set,
637637
indicates that the page is all-visible, or in other words that the page does
638638
not contain any tuples that need to be vacuumed.
639-
This information can also be used by <firstterm>index-only scans</> to answer
640-
queries using only the index tuple.
639+
This information can also be used
640+
by <link linkend="indexes-index-only-scans"><firstterm>index-only
641+
scans</></link> to answer queries using only the index tuple.
641642
The second bit, if set, means that all tuples on the page have been frozen.
643+
That means that even an anti-wraparound vacuum need not revisit the page.
642644
</para>
643645

644646
<para>

0 commit comments

Comments
 (0)