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.

4 files changed, 61 insertions, 4 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index d6baf84248e..0495bd03bd5 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -477,6 +477,13 @@
      </row>
 
      <row>
+      <entry><structfield>amcanreturn</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>Can the access method return the contents of index entries?</entry>
+     </row>
+
+     <row>
       <entry><structfield>amoptionalkey</structfield></entry>
       <entry><type>bool</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fbcd455694b..d3a8b2648d8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2404,6 +2404,22 @@ SET ENABLE_SEQSCAN TO OFF;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-indexonlyscan" xreflabel="enable_indexonlyscan">
+      <term><varname>enable_indexonlyscan</varname> (<type>boolean</type>)</term>
+      <indexterm>
+       <primary>index-only scan</primary>
+      </indexterm>
+      <indexterm>
+       <primary><varname>enable_indexonlyscan</> configuration parameter</primary>
+      </indexterm>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of index-only-scan plan
+        types. The default is <literal>on</>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-enable-material" xreflabel="enable_material">
       <term><varname>enable_material</varname> (<type>boolean</type>)</term>
       <indexterm>
@@ -6353,7 +6369,7 @@ LOG:  CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
        <row>
         <entry>
           <option>-fb</option>, <option>-fh</option>, <option>-fi</option>,
-          <option>-fm</option>, <option>-fn</option>,
+          <option>-fm</option>, <option>-fn</option>, <option>-fo</option>,
           <option>-fs</option>, <option>-ft</option>
          </entry>
          <entry>
@@ -6362,6 +6378,7 @@ LOG:  CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
           <literal>enable_indexscan = off</>,
           <literal>enable_mergejoin = off</>,
           <literal>enable_nestloop = off</>,
+          <literal>enable_indexonlyscan = off</>,
           <literal>enable_seqscan = off</>,
           <literal>enable_tidscan = off</>
          </entry>
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index bb942583853..724b413755d 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -134,6 +134,11 @@
    <structfield>amsearchnulls</structfield>, indicating that it supports
    <literal>IS NULL</> and <literal>IS NOT NULL</> clauses as search
    conditions.
+   An index method can also set <structfield>amcanreturn</structfield>,
+   indicating that it can support <firstterm>index-only scans</> by returning
+   the indexed column values for an index entry in the form of an IndexTuple.
+   (An example of an index AM that cannot do this is hash, which stores only
+   the hash values not the original data.)
   </para>
 
  </sect1>
@@ -386,6 +391,18 @@ amgettuple (IndexScanDesc scan,
   </para>
 
   <para>
+   If the access method supports index-only scans (i.e.,
+   <structfield>amcanreturn</structfield> is TRUE in its <structname>pg_am</>
+   row), then on success it must also check
+   <literal>scan-&gt;xs_want_itup</>, and if that is true it should return
+   the original indexed data for the index entry, in the form of an
+   <structname>IndexTuple</> stored at <literal>scan-&gt;xs_itup</>.  However,
+   it is permissible for the access method to sometimes fail to provide this
+   data, in which case it must set <literal>scan-&gt;xs_itup</> to NULL.  That
+   will result in a regular heap fetch occurring.
+  </para>
+
+  <para>
    The <function>amgettuple</> function need only be provided if the access
    method supports <quote>plain</> index scans.  If it doesn't, the
    <structfield>amgettuple</> field in its <structname>pg_am</> row must
@@ -582,6 +599,15 @@ amrestrpos (IndexScanDesc scan);
   </para>
 
   <para>
+   If the index stores the original indexed data values (and not some lossy
+   representation of them), it is useful to support index-only scans, in
+   which the index returns the actual data not just the TID of the heap tuple.
+   This will only work if the visibility map shows that the TID is on an
+   all-visible page; else the heap tuple must be visited anyway to check
+   MVCC visibility.  But that is no concern of the access method's.
+  </para>
+
+  <para>
    Instead of using <function>amgettuple</>, an index scan can be done with
    <function>amgetbitmap</> to fetch all tuples in one call.  This can be
    noticeably more efficient than <function>amgettuple</> because it allows
@@ -593,7 +619,11 @@ amrestrpos (IndexScanDesc scan);
    supported. Secondly, the tuples are returned in a bitmap which doesn't
    have any specific ordering, which is why <function>amgetbitmap</> doesn't
    take a <literal>direction</> argument.  (Ordering operators will never be
-   supplied for such a scan, either.)  Finally, <function>amgetbitmap</>
+   supplied for such a scan, either.)
+   Also, there is no provision for index-only scans with
+   <function>amgetbitmap</>, since there is no way to return the contents of
+   index tuples.
+   Finally, <function>amgetbitmap</>
    does not guarantee any locking of the returned tuples, with implications
    spelled out in <xref linkend="index-locking">.
   </para>
diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml
index 3807f4090a8..9869a1f5262 100644
--- a/doc/src/sgml/ref/postgres-ref.sgml
+++ b/doc/src/sgml/ref/postgres-ref.sgml
@@ -376,12 +376,15 @@ PostgreSQL documentation
 
     <variablelist>
      <varlistentry>
-      <term><option>-f</option> <literal>{ s | i | m | n | h }</literal></term>
+      <term><option>-f</option> <literal>{ s | i | o | b | t | n | m | h }</literal></term>
       <listitem>
        <para>
         Forbids the use of particular scan and join methods:
         <literal>s</literal> and <literal>i</literal>
-        disable sequential and index scans respectively, while
+        disable sequential and index scans respectively,
+        <literal>o</literal>, <literal>b</literal> and <literal>t</literal>
+        disable index-only scans, bitmap index scans, and TID scans
+        respectively, while
         <literal>n</literal>, <literal>m</literal>, and <literal>h</literal>
         disable nested-loop, merge and hash joins respectively.
        </para>