PolarDB:Index-only scans and covering indexes

How it works

All indexes in PostgreSQL are *secondary indexes*: each index is stored separately from the heap. A standard index scan therefore fetches data from two places — the index to find matching rows, then the heap to retrieve column values. Because matching index entries are usually clustered together while the corresponding heap rows can be scattered anywhere, that second fetch involves significant random I/O.

An index-only scan eliminates the heap fetch entirely by returning values directly from index entries. Instead of two reads, it needs only one.

Requirements

Two conditions must be met before the query planner can use an index-only scan.

The index type must support index-only scans.

The underlying requirement is that the index must physically store, or else be able to reconstruct, the original data value for each entry. GIN indexes cannot meet this requirement because each entry typically holds only part of the original value.

The query must reference only columns stored in the index.

Given an index on columns x and y of a table that also has column z, the following queries can use index-only scans:

SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;

These queries cannot, because they reference z, which is not in the index:

SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;

The visibility map check

Meeting the two requirements above makes an index-only scan *physically possible*, but there is one more factor: PostgreSQL must confirm that each retrieved row is visible to the current Multi-Version Concurrency Control (MVCC) snapshot. Visibility information lives only in heap entries, not in index entries.

To avoid a full heap fetch just for visibility checks, PostgreSQL maintains a *visibility map* — a compact bitmap that tracks, per heap page, whether all rows on that page are visible to all current and future transactions. The visibility map is four orders of magnitude smaller than the heap, so it stays cached in memory almost all the time.

When an index-only scan finds a matching entry, it checks the visibility map for the corresponding heap page:

• Bit set — all rows on that page are known visible. The value is returned from the index with no heap access.

• Bit not set — the heap page must be visited to determine visibility. The query falls back to a standard index scan for that row, with no performance gain.

Index-only scans are most effective on tables where most heap pages have their all-visible bit set — that is, tables whose rows change infrequently.

Verify that index-only scans are being used

Run EXPLAIN on your query and look for Index Only Scan in the output:

EXPLAIN SELECT x, y FROM tab WHERE x = 'key';

Index Only Scan using tab_x_y on tab  (cost=0.29..8.31 rows=1 width=8)
  Index Cond: (x = 'key'::text)

The cost values above are representative examples. Actual values depend on your table statistics and PostgreSQL configuration.

If you see Index Scan instead, the query is fetching from the heap. Check whether the query references columns outside the index, or whether the table has recently had many updates (which would clear visibility map bits).

Create a covering index with INCLUDE

A *covering index* is an index that contains all the columns a query needs, enabling an index-only scan for that query. Use the INCLUDE clause to add *payload columns* — columns returned by the query but not used for filtering — without making them part of the index key.

For a query that frequently runs as:

SELECT y FROM tab WHERE x = 'key';

An index on x alone would require a heap fetch to get y. A covering index avoids that:

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

With this index, the query planner can serve the entire query from the index.

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

Suffix truncation

B-tree indexes apply *suffix truncation* automatically: non-key (INCLUDE) columns are never stored in upper B-tree levels, only at the leaf level. The truncation process may also remove trailing key columns from upper levels when the remaining prefix is sufficient to navigate the tree.

This means that covering indexes without an INCLUDE clause — created by listing payload columns as ordinary key columns — may behave similarly in practice. However, defining payload columns with INCLUDE is the reliable way to keep upper-level tuples small and, critically, the only way to enforce uniqueness on the key columns alone.

Pre-INCLUDE workaround

Before the INCLUDE feature was available, a common workaround was to list payload columns as trailing key columns:

CREATE INDEX tab_x_y ON tab(x, y);

This works for index-only scans as long as y is a trailing column — making payload columns leading columns is unwise. However, it enforces uniqueness on both x and y if you use a UNIQUE index, which is usually not the intent.

Edge cases

CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

SELECT target FROM tests WHERE subject = 'some-subject' AND success;