7.2. Index-Only Scans

Index-only scans (often called index-only access) reduce I/O cost by directly using the index key without accessing table pages. This occurs when the index key includes all target entries of a SELECT statement. Almost all commercial RDBMS, such as DB2 and Oracle, provide this technique. PostgreSQL introduced this option in version 9.2 (2012).

The following example describes how index-only scans perform in PostgreSQL.

The example uses the following assumptions:

• Table definition:
  The table ’tbl’ is defined as follows:

  testdb=# \d tbl
        Table "public.tbl"
   Column |  Type   | Modifiers
  --------+---------+-----------
   id     | integer |
   name   | text    |
   data   | text    |
  Indexes:
      "tbl_idx" btree (id, name)

• Index:
  The index ’tbl_idx’ consists of two columns: ‘id’ and ’name’.

• Tuples:
  The table ’tbl’ contains the following tuples:

  • Tuple_18: id is 18, name is ‘Queen’; stored in page 0.
  • Tuple_19: id is 19, name is ‘BOSTON’; stored in page 1.

• Visibility:
  All tuples in page 0 are visible; the tuples in page 1 are not. The visibility of each page is recorded in the corresponding Visibility Map (VM). (See Section 6.2 for VM details.)

The following SELECT command demonstrates how PostgreSQL reads these tuples:

testdb=# SELECT id, name FROM tbl WHERE id BETWEEN 18 and 19;
 id |  name
----+--------
 18 | Queen
 19 | Boston
(2 rows)

This query retrieves data from the ‘id’ and ’name’ columns. Since ’tbl_idx’ contains both columns, accessing table pages initially seems unnecessary.

In principle, however, PostgreSQL must check tuple visibility. Index tuples do not contain transaction information, such as the t_xmin and t_xmax fields found in heap tuples (described in Section 5.2). Consequently, PostgreSQL must normally access table data to verify the visibility of the index tuples.

To resolve this dilemma, PostgreSQL utilizes the Visibility Map. If all tuples in a page are visible, PostgreSQL uses the index tuple key and skips the table page access. Otherwise, PostgreSQL reads the table tuple to check visibility, which is the standard process.

In this example, PostgreSQL does not access Tuple_18 because page 0 is marked as visible in the VM. In contrast, PostgreSQL must access Tuple_19 to handle concurrency control because page 1 is not marked as fully visible. See Figure 7.7.