Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases PostgreSQL

How Index-Only Scans Work in PostgreSQL

PostgreSQL stores table rows in a heap, the table's main storage, and keeps indexes separately. When a query needs only values stored in an index, PostgreSQL can use an index-only scan to return those values from the index instead of fetching each row from the heap. That can reduce table reads, especially when the query touches many rows.

The scan still has to respect MVCC, PostgreSQL’s rules for deciding which row versions a transaction can see. A visibility map records whether each heap page contains only rows known to be visible. PostgreSQL skips heap checks for pages marked all-visible; otherwise, it checks the heap even when the plan says Index Only Scan. Knowing how the index, visibility map, and query plan work together helps you decide whether a covering index is useful and measure whether it actually avoids heap visits. The PostgreSQL 18 documentation on index-only scans describes the mechanism and its limits.

What PostgreSQL reads during an index-only scan

An ordinary index scan uses index entries to find rows that match the index conditions, then reads the heap to fetch any values the query needs and check whether each row is visible. An index-only scan reads the needed values directly from index entries. It can skip the heap visibility check when the visibility map says the corresponding heap page is all-visible.

The visibility map tracks this status page by page. PostgreSQL stores two bits for each heap page: one marks it all-visible, and another marks it all-frozen. The all-visible bit matters for index-only scans. When the bit is set, PostgreSQL knows the rows on that page are visible to all current and future transactions. When the bit is unset, PostgreSQL checks the heap because the bit alone does not establish whether a row is visible. The visibility map documentation explains how these bits work.

Vacuum sets visibility-map bits, while data modifications clear the relevant bits. As a result, a frequently updated table can have a suitable index but still require heap visits. Vacuum helps maintain the map, but later updates can clear bits again.

What makes a query eligible

PostgreSQL needs both conditions to use an index-only scan. First, the index type must support the feature. B-tree indexes support it; GiST and SP-GiST support it for some operator classes, which define how an index type handles particular data types and operators. GIN indexes do not support index-only scans.

Second, the index must provide the values the query needs. That includes values returned by the query and values needed to evaluate its conditions. For example, an index containing x and y could cover a query that filters on x and returns y. A query that also needs z, which the index does not contain, generally must read the heap. PostgreSQL’s planner also has limitations with some indexes built on expressions. So even if an expression appears derivable from an index value, PostgreSQL is not guaranteed to choose an index-only plan.

Even when both conditions hold, PostgreSQL chooses a plan based on estimated costs. A query can qualify for an index-only scan and still use another plan if the planner estimates that plan will cost less.

Add payload columns with INCLUDE

Use INCLUDE to store a column in an index without adding it to the search key. In this example, x helps PostgreSQL find matching rows, while y supplies the query output:

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

SELECT y
FROM tab
WHERE x = 'key';

An included column does not determine index ordering or uniqueness. For a unique index, uniqueness applies to the key columns, not the included columns. PostgreSQL 18 supports INCLUDE with B-tree, GiST, and SP-GiST indexes, as described in the CREATE INDEX documentation.

Add only columns that a query you expect to benefit needs. Included values duplicate table data and make the index larger, which can slow searches. PostgreSQL also warns that an oversized index tuple can cause an insert to fail. In PostgreSQL 18, B-tree deduplication is not used for indexes with included columns.

Check whether the scan avoids heap visits

Run the query with EXPLAIN (ANALYZE, BUFFERS) to see the plan and the work performed for that execution:

EXPLAIN (ANALYZE, BUFFERS)
SELECT y
FROM tab
WHERE x = 'key';

Look for an Index Only Scan node and its Heap Fetches count. Zero means PostgreSQL made no heap fetches for visibility checks during that run. A count above zero means some candidate rows required heap access. The plan can still be an index-only scan even when that count is above zero.

Then compare buffer counts, which show page reads and cache hits, and execution time for representative queries. An index-only scan does not guarantee a faster result: the planner may choose another plan, and the index itself adds storage and maintenance costs. EXPLAIN ANALYZE executes the statement, so take care when running it on a statement that changes data. PostgreSQL’s EXPLAIN documentation shows how to interpret plan output.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *