| Server IP : 172.67.134.114 / Your IP : 162.159.115.41 Web Server : Apache/2.4.37 System : Linux almalinux.duckdns.org 4.18.0-553.111.1.el8_10.x86_64 #1 SMP Sun Mar 8 20:06:07 EDT 2026 x86_64 User : ricodeal ( 1046) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /usr/share/doc/postgresql-docs/html/ |
Upload File : |
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.11. Index-Only Scans</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="[email protected]" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="indexes-collations.html" title="11.10. Indexes and Collations" /><link rel="next" href="indexes-examine.html" title="11.12. Examining Index Usage" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.11. Index-Only Scans</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-collations.html" title="11.10. Indexes and Collations">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 10.23 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-examine.html" title="11.12. Examining Index Usage">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="INDEXES-INDEX-ONLY-SCANS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.11. Index-Only Scans</h2></div></div></div><a id="id-1.5.10.14.2" class="indexterm"></a><a id="id-1.5.10.14.3" class="indexterm"></a><p> All indexes in <span class="productname">PostgreSQL</span> are <em class="firstterm">secondary</em>
indexes, meaning that each index is stored separately from the table's
main data area (which is called the table's <em class="firstterm">heap</em>
in <span class="productname">PostgreSQL</span> terminology). This means that in an
ordinary index scan, each row retrieval requires fetching data from both
the index and the heap. Furthermore, while the index entries that match a
given indexable <code class="literal">WHERE</code> condition are usually close together in
the index, the table rows they reference might be anywhere in the heap.
The heap-access portion of an index scan thus involves a lot of random
access into the heap, which can be slow, particularly on traditional
rotating media. (As described in <a class="xref" href="indexes-bitmap-scans.html" title="11.5. Combining Multiple Indexes">Section 11.5</a>,
bitmap scans try to alleviate this cost by doing the heap accesses in
sorted order, but that only goes so far.)
</p><p> To solve this performance problem, <span class="productname">PostgreSQL</span>
supports <em class="firstterm">index-only scans</em>, which can answer queries from an
index alone without any heap access. The basic idea is to return values
directly out of each index entry instead of consulting the associated heap
entry. There are two fundamental restrictions on when this method can be
used:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p> The index type must support index-only scans. B-tree indexes always
do. GiST and SP-GiST indexes support index-only scans for some
operator classes but not others. Other index types have no support.
The underlying requirement is that the index must physically store, or
else be able to reconstruct, the original data value for each index
entry. As a counterexample, GIN indexes cannot support index-only
scans because each index entry typically holds only part of the
original data value.
</p></li><li class="listitem"><p> The query must reference only columns stored in the index. For
example, given an index on columns <code class="literal">x</code> and <code class="literal">y</code> of a
table that also has a column <code class="literal">z</code>, these queries could use
index-only scans:
</p><pre class="programlisting">SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;</pre><p>
but these queries could not:
</p><pre class="programlisting">SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;</pre><p>
(Expression indexes and partial indexes complicate this rule,
as discussed below.)
</p></li></ol></div><p>
</p><p> If these two fundamental requirements are met, then all the data values
required by the query are available from the index, so an index-only scan
is physically possible. But there is an additional requirement for any
table scan in <span class="productname">PostgreSQL</span>: it must verify that each
retrieved row be <span class="quote">“<span class="quote">visible</span>”</span> to the query's MVCC snapshot, as
discussed in <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>. Visibility information is not stored
in index entries, only in heap entries; so at first glance it would seem
that every row retrieval would require a heap access anyway. And this is
indeed the case, if the table row has been modified recently. However,
for seldom-changing data there is a way around this
problem. <span class="productname">PostgreSQL</span> tracks, for each page in a table's
heap, whether all rows stored in that page are old enough to be visible to
all current and future transactions. This information is stored in a bit
in the table's <em class="firstterm">visibility map</em>. An index-only scan, after
finding a candidate index entry, checks the visibility map bit for the
corresponding heap page. If it's set, the row is known visible and so the
data can be returned with no further work. If it's not set, the heap
entry must be visited to find out whether it's visible, so no performance
advantage is gained over a standard index scan. Even in the successful
case, this approach trades visibility map accesses for heap accesses; but
since the visibility map is four orders of magnitude smaller than the heap
it describes, far less physical I/O is needed to access it. In most
situations the visibility map remains cached in memory all the time.
</p><p> In short, while an index-only scan is possible given the two fundamental
requirements, it will be a win only if a significant fraction of the
table's heap pages have their all-visible map bits set. But tables in
which a large fraction of the rows are unchanging are common enough to
make this type of scan very useful in practice.
</p><p> To make effective use of the index-only scan feature, you might choose to
create indexes in which only the leading columns are meant to
match <code class="literal">WHERE</code> clauses, while the trailing columns
hold <span class="quote">“<span class="quote">payload</span>”</span> data to be returned by a query. For example, if
you commonly run queries like
</p><pre class="programlisting">SELECT y FROM tab WHERE x = 'key';</pre><p>
the traditional approach to speeding up such queries would be to create an
index on <code class="literal">x</code> only. However, an index on <code class="literal">(x, y)</code>
would offer the possibility of implementing this query as an index-only
scan. As previously discussed, such an index would be larger and hence
more expensive than an index on <code class="literal">x</code> alone, so this is attractive
only if the table is known to be mostly static. Note it's important that
the index be declared on <code class="literal">(x, y)</code> not <code class="literal">(y, x)</code>, as for
most index types (particularly B-trees) searches that do not constrain the
leading index columns are not very efficient.
</p><p> In principle, index-only scans can be used with expression indexes.
For example, given an index on <code class="literal">f(x)</code> where <code class="literal">x</code> is a
table column, it should be possible to execute
</p><pre class="programlisting">SELECT f(x) FROM tab WHERE f(x) < 1;</pre><p>
as an index-only scan; and this is very attractive if <code class="literal">f()</code> is
an expensive-to-compute function. However, <span class="productname">PostgreSQL</span>'s
planner is currently not very smart about such cases. It considers a
query to be potentially executable by index-only scan only when
all <span class="emphasis"><em>columns</em></span> needed by the query are available from the index.
In this example, <code class="literal">x</code> is not needed except in the
context <code class="literal">f(x)</code>, but the planner does not notice that and
concludes that an index-only scan is not possible. If an index-only scan
seems sufficiently worthwhile, this can be worked around by declaring the
index to be on <code class="literal">(f(x), x)</code>, where the second column is not
expected to be used in practice but is just there to convince the planner
that an index-only scan is possible. An additional caveat, if the goal is
to avoid recalculating <code class="literal">f(x)</code>, is that the planner won't
necessarily match uses of <code class="literal">f(x)</code> that aren't in
indexable <code class="literal">WHERE</code> clauses to the index column. It will usually
get this right in simple queries such as shown above, but not in queries
that involve joins. These deficiencies may be remedied in future versions
of <span class="productname">PostgreSQL</span>.
</p><p> Partial indexes also have interesting interactions with index-only scans.
Consider the partial index shown in <a class="xref" href="indexes-partial.html#INDEXES-PARTIAL-EX3" title="Example 11.3. Setting up a Partial Unique Index">Example 11.3</a>:
</p><pre class="programlisting">CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;</pre><p>
In principle, we could do an index-only scan on this index to satisfy a
query like
</p><pre class="programlisting">SELECT target FROM tests WHERE subject = 'some-subject' AND success;</pre><p>
But there's a problem: the <code class="literal">WHERE</code> clause refers
to <code class="literal">success</code> which is not available as a result column of the
index. Nonetheless, an index-only scan is possible because the plan does
not need to recheck that part of the <code class="literal">WHERE</code> clause at run time:
all entries found in the index necessarily have <code class="literal">success = true</code>
so this need not be explicitly checked in the
plan. <span class="productname">PostgreSQL</span> versions 9.6 and later will recognize
such cases and allow index-only scans to be generated, but older versions
will not.
</p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-collations.html" title="11.10. Indexes and Collations">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-examine.html" title="11.12. Examining Index Usage">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.10. Indexes and Collations </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 10.23 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 11.12. Examining Index Usage</td></tr></table></div></body></html>