PostgreSQL Index-Only Scans: Why They Work and When They Fail

Indexes help PostgreSQL find rows without scanning an entire table. However, even when a query uses an index, PostgreSQL may still need to visit the table to retrieve the requested columns or confirm that a row is visible to the current transaction.

An index-only scan can avoid many of these table accesses. When the required columns are available in the index and PostgreSQL can determine row visibility without checking the table, it can return results directly from the index.

This can reduce disk reads and improve query performance, especially for reporting queries that retrieve many rows from a relatively stable table. However, an index-only scan does not guarantee that PostgreSQL will avoid all table access. Its effectiveness depends on the index definition, the query, and the visibility map maintained by PostgreSQL's vacuum process.

This article explains how index-only scans work, how to create suitable indexes, and how to investigate cases where PostgreSQL still performs heap fetches.

What Is an Index-Only Scan?

Consider a table that stores product information:

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_name TEXT NOT NULL,
    category_id INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Suppose an application frequently retrieves product IDs and prices for a particular category:

SELECT
    product_id,
    price
FROM products
WHERE category_id = 10;

An ordinary index scan may use an index to find matching product rows and then visit the table, also called the heap, to retrieve the requested values.

If PostgreSQL has an index containing category_id, product_id, and price, it may be able to return the requested columns directly from the index.

Create an index that covers the query:

CREATE INDEX idx_products_category_price
ON products (category_id)
INCLUDE (price);

The primary key already provides an index on product_id. However, that does not mean PostgreSQL can combine the two indexes automatically to produce the desired index-only scan.

In this example, the new index contains category_id as its key and price as an included column. PostgreSQL B-tree indexes also store the table's tuple identifier and can make the indexed row's primary-key value available through that identifier only as a locator, not as the product_id column itself. Therefore, this index alone does not cover the query's product_id requirement.

To cover both selected columns, include product_id as an indexed or included column:

CREATE INDEX idx_products_category_covering
ON products (category_id)
INCLUDE (product_id, price);

Now the index contains the category used for filtering and the values requested by the query. PostgreSQL can consider an index-only scan for this query, subject to its cost estimates and visibility requirements.

How PostgreSQL Determines Row Visibility

Having every required column in an index is only part of the process. PostgreSQL must also determine whether each row is visible to the current transaction.

PostgreSQL uses Multi-Version Concurrency Control (MVCC). An index entry can point to a row version that is no longer visible to the current query. The index alone does not always contain enough information to establish visibility.

PostgreSQL maintains a visibility map for each table. It records whether all tuples on a heap page are visible to all transactions.

When a page is marked all-visible, PostgreSQL can often return the relevant index entry without visiting the heap to check visibility. When a page is not marked all-visible, PostgreSQL may need to fetch the corresponding heap row.

This is why two queries using the same covering index can show different numbers of heap fetches.

Tables that are updated frequently may have fewer all-visible pages because changes can clear the relevant visibility-map bits. Vacuum can restore all-visible status when the required conditions are met.

Check Whether PostgreSQL Uses an Index-Only Scan

Use EXPLAIN to inspect the query plan:

EXPLAIN
SELECT
    product_id,
    price
FROM products
WHERE category_id = 10;

The plan may contain a node named Index Only Scan if PostgreSQL determines that the index-only strategy is appropriate.

To measure actual execution behavior, use EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    product_id,
    price
FROM products
WHERE category_id = 10;

The output includes the selected plan, actual execution time, row counts, and buffer activity.

For an index-only scan, pay attention to the Heap Fetches value.

A low heap-fetch count can indicate that PostgreSQL was able to verify visibility using the visibility map for most matching entries. A high count means PostgreSQL had to visit the heap for many entries despite using an index-only scan.

The exact performance benefit also depends on the number of matching rows, cache state, index size, and storage behavior. A plan labelled Index Only Scan is not automatically faster than every alternative.

Why Index-Only Scans Sometimes Perform Poorly

Several factors can prevent an index-only scan from delivering the expected benefit.

The Index Does Not Cover the Query

Consider this query:

SELECT
    product_id,
    product_name,
    price
FROM products
WHERE category_id = 10;

The earlier covering index contains category_id, product_id, and price, but not product_name.

PostgreSQL cannot retrieve product_name directly from that index. It must access the heap or choose a different execution plan.

If this query is frequent and the table is suitable for a covering index, you could create an index that includes the missing column:

CREATE INDEX idx_products_category_full
ON products (category_id)
INCLUDE (product_id, product_name, price);

However, do not automatically include every selected column in an index. Large included values increase index size and add maintenance work to inserts and updates.

Choose index columns based on real query patterns and measured performance.

The Visibility Map Is Not Up to Date

Frequently updated tables may have many pages that are not marked all-visible. PostgreSQL then needs heap fetches to confirm visibility.

Run VACUUM when appropriate:

VACUUM (ANALYZE) products;

Vacuum removes reclaimable dead row versions and can update visibility-map information. ANALYZE refreshes planner statistics.

After vacuuming, rerun the query with EXPLAIN (ANALYZE, BUFFERS) and compare heap fetches and execution time.

Vacuum does not guarantee that every page becomes all-visible. Active transactions, ongoing modifications, and other conditions can affect visibility-map coverage.

The Table Changes Frequently

An index-only scan may be highly effective for a table that changes infrequently. The same index may provide less benefit for a table that receives continuous updates.

For example, a reporting query over historical records may benefit from index-only access, while a query over actively changing inventory data may still need frequent heap visits.

This does not mean index-only scans are unsuitable for changing tables. It means the expected benefit depends on the workload and the visibility state of the table.

PostgreSQL Chooses Another Plan

Even when an index covers the query, PostgreSQL may choose a sequential scan or a regular index scan.

If a query returns a large percentage of the table, reading the table sequentially may be cheaper than traversing an index and fetching many rows.

The optimizer considers estimated row counts, table statistics, index costs, and other factors. Use execution plans to understand the decision instead of forcing an index without evidence.

Use INCLUDE Columns Carefully

PostgreSQL supports INCLUDE columns in B-tree indexes. Included columns are stored in the index but do not participate in the index's search ordering.

For example:

CREATE INDEX idx_orders_customer
ON orders (customer_id)
INCLUDE (order_date, total_amount);

This index can help queries that filter by customer_id and return order_date and total_amount:

SELECT
    order_date,
    total_amount
FROM orders
WHERE customer_id = 100;

The customer_id column determines the index's search order. The included columns provide additional values for retrieval.

Included columns do not replace key columns when the query needs to search or sort by those values. If an application frequently filters by order_date or needs a particular ordering, evaluate whether that column should be part of the index key instead.

Large included columns can also make indexes substantially bigger. Updates to indexed or included values may require index maintenance, increasing write costs.

Measure the Benefit Before Keeping an Index

Before creating a covering index, establish a baseline using representative data and realistic parameters.

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    product_id,
    price
FROM products
WHERE category_id = 10;

Record the execution time, buffer activity, row counts, and plan structure.

Then create the candidate index and rerun the query under comparable conditions. Check whether the plan changes, whether heap fetches decrease, and whether the query becomes faster.

For a production database, also consider the cost of creating the index, additional storage, and slower writes. A covering index that helps one reporting query can negatively affect other operations if it becomes unnecessarily large.

Do not rely on a single test run. Cached data, concurrent activity, and differences in parameter values can change the observed result.

Common Mistakes to Avoid

Assuming every index-only scan avoids heap access

PostgreSQL may still perform heap fetches when visibility cannot be established from the visibility map.

Including too many columns

A very wide index consumes storage and increases the cost of writes and maintenance. Include only the columns justified by important query patterns.

Running VACUUM without measuring the result

Vacuum is essential maintenance, but it does not guarantee an immediate performance improvement for every query. Compare execution plans and buffer activity before and after maintenance.

Ignoring write-heavy workloads

Additional indexes improve some reads but increase work for inserts, updates, and deletes. Evaluate the overall workload, not only the target query.

Forcing an index prematurely

A sequential scan may be the correct choice for queries that retrieve a large portion of a table. Investigate the plan and its estimates before changing optimizer settings.

Conclusion

PostgreSQL index-only scans can improve query performance by returning requested values directly from an index without visiting the heap for every matching row.

To make them effective, build indexes that cover the query's filtering and output columns, keep table maintenance healthy, and inspect actual execution plans for heap fetches. The visibility map is particularly important because PostgreSQL must still confirm row visibility when it cannot establish it from the index alone.

Use EXPLAIN (ANALYZE, BUFFERS) to measure the real benefit, and balance read performance against index size and write overhead. A carefully designed covering index can reduce unnecessary table access, but the best result comes from testing it against the workload your application actually runs.