PostgreSQL Index-Only Scans: How They Work and Why They Sometimes Fail

When a PostgreSQL query needs only a few columns from a large table, an index can sometimes provide all the data required without reading the table itself. PostgreSQL calls this an index-only scan.

Index-only scans can reduce disk I/O and improve query performance, especially for reporting queries that repeatedly read a small set of columns from a large table. However, creating an index that contains every required column does not guarantee that PostgreSQL will avoid accessing the table.

The reason is PostgreSQL's MVCC visibility rules. Even when an index contains the requested values, PostgreSQL may still need to visit the underlying table to determine whether a row version is visible to the current transaction.

In this article, we will explore how index-only scans work, how to create suitable indexes, how to identify them in execution plans, and why they sometimes perform like ordinary index scans.

What Is an Index-Only Scan in PostgreSQL?

An index-only scan is a query execution strategy in which PostgreSQL retrieves the requested column values directly from an index whenever possible, without reading the corresponding heap tuples.

The heap is PostgreSQL's main table storage. A conventional index scan typically uses the index to locate matching rows and then visits the heap to retrieve the requested data and check visibility.

An index-only scan can skip those heap visits when the index contains all the required values and PostgreSQL can establish that the corresponding rows are visible without checking the heap.

Consider a table that stores customer orders:

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(30) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

Suppose an application frequently retrieves order IDs and statuses for a particular customer.

SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

If PostgreSQL uses an ordinary index on customer_id, it may find matching index entries and visit the heap to retrieve order_id and status.

A covering index can include all three columns needed by the query:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_id, status);

The index key is customer_id. The INCLUDE clause stores order_id and status as non-key payload columns.

This index gives PostgreSQL the opportunity to answer the query directly from the index, provided the visibility requirements are also satisfied.

How PostgreSQL Index-Only Scans Work

An index-only scan depends on three main conditions.

The index must contain the required columns

PostgreSQL must be able to retrieve the columns needed by the query from the selected index.

These include columns used in the output list and any other columns needed for the query's filtering or processing that the chosen plan expects to obtain from the index.

For example:

SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

The covering index contains the customer ID used by the filter and the two requested output columns.

If the query also needs total_amount, PostgreSQL cannot retrieve that value from this index because it is not included.

The index access method must support index-only scans

Index-only scans depend on the capabilities of the index access method.

PostgreSQL's B-tree indexes support index-only scans. Other index types have different capabilities, so do not assume every index can return all required values in the same way.

For common application queries involving equality filters, sorting, and selected columns, B-tree indexes are often the first option to evaluate.

PostgreSQL must establish row visibility

This is the condition that often surprises developers.

PostgreSQL indexes do not generally store enough MVCC visibility information to determine whether every referenced row version is visible to the current transaction.

PostgreSQL therefore maintains a visibility map for heap pages. When a heap page is marked all-visible, PostgreSQL knows that every tuple on that page is visible to all relevant transactions.

For such pages, an index-only scan can return index values without checking each tuple in the heap.

If a page is not marked all-visible, PostgreSQL may visit the heap to verify visibility even though the index contains every requested column.

This means an index-only scan can still perform heap fetches.

Step 1: Create a Covering Index

Let's use the orders table to create an index designed for a common customer-order query.

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_id, status);

The definition separates index columns into two groups.

customer_id is an index key. PostgreSQL uses it to locate matching entries.
order_id and status are included payload columns. They can be returned by an index-only scan but do not affect the index's search ordering.

Now run the query:

SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

PostgreSQL may choose an index-only scan if the index is selective enough, the query benefits from it, and visibility conditions allow it.

The word may matters. PostgreSQL's query planner estimates the cost of available plans. It can choose a sequential scan or another index even when a covering index exists.

For a small table or a query that returns a large proportion of its rows, a sequential scan may be cheaper.

Step 2: Check the Execution Plan

Use EXPLAIN to see which plan PostgreSQL selects.

EXPLAIN
SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

A successful plan may contain an operation similar to:

Index Only Scan using idx_orders_customer_covering on orders
  Index Cond: (customer_id = 5001)

This indicates that PostgreSQL selected an index-only scan.

However, EXPLAIN without ANALYZE shows the estimated plan, not actual execution statistics.

For a real query, use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

This executes the query and reports actual timing, row counts, and buffer activity.

Review the following fields:

Index Only Scan: Indicates that PostgreSQL selected the index-only scan node.
Heap Fetches: Reports how many heap visibility checks were needed.
Buffers: Shows buffer activity associated with the plan.
actual time: Reports measured execution time for the node.
rows: Shows the actual number of rows produced.

A plan with Heap Fetches: 0 is a strong indication that PostgreSQL was able to avoid heap visits for that scan.

A plan can still say Index Only Scan while reporting many heap fetches. The node type describes the selected strategy, not a guarantee that every row will be returned without touching the heap.

EXPLAIN ANALYZE runs the statement. Use it carefully for expensive queries and never assume it is a harmless planning-only operation.

Step 3: Understand Why Index-Only Scans Sometimes Fail

There are several reasons why PostgreSQL may not use an index-only scan or may still access the heap.

Reason 1: The visibility map is not sufficiently up to date

The most common reason for unexpected heap fetches is that relevant heap pages are not marked all-visible.

Consider a table that receives frequent updates:

UPDATE orders
SET status = 'Shipped'
WHERE order_id = 90001;

This change affects the heap page containing the row. PostgreSQL may need to clear that page's all-visible status because tuple visibility has changed.

Until the page becomes eligible for all-visible status again, an index-only scan may need to visit the heap to check visibility.

Running VACUUM can help update visibility information when conditions allow it:

VACUUM (ANALYZE) orders;

After vacuuming, test the query again:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

The number of heap fetches may decrease if the relevant pages can now be marked all-visible.

However, vacuuming cannot make every page all-visible in every situation. Active transactions, recently changed rows, and other visibility constraints may prevent that.

Reason 2: The index does not cover the query

Suppose the index includes order_id and status, but the query requests total_amount.

SELECT
    order_id,
    status,
    total_amount
FROM orders
WHERE customer_id = 5001;

The existing index does not contain total_amount. PostgreSQL cannot return that column from the index alone.

You could create another covering index:

CREATE INDEX idx_orders_customer_amount
ON orders (customer_id)
INCLUDE (order_id, status, total_amount);

But do not automatically add every selected column to an index. Wider indexes consume more disk space, increase write overhead, and require additional maintenance.

Create covering indexes for queries that justify their cost.

Reason 3: PostgreSQL chooses another plan

Even when an index supports an index-only scan, PostgreSQL may decide that another plan is cheaper.

For example, if a query returns most rows in a table, scanning the entire table sequentially can be less expensive than reading many index pages and performing additional work.

You may see a sequential scan:

Seq Scan on orders
  Filter: (customer_id = 5001)

This is not automatically a problem. The right plan depends on table size, data distribution, cache state, row estimates, and the number of rows returned.

Use EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual behavior before changing indexes or planner settings.

Reason 4: The query requires expressions or columns that are not available

A query may need a value that is not directly stored in the index, or its expression may not be supported by the available index definition.

For example:

SELECT
    order_id,
    total_amount * 1.18 AS amount_with_tax
FROM orders
WHERE customer_id = 5001;

The calculation itself can be performed using values retrieved from an index, but the index must provide the underlying values needed for the expression. If total_amount is missing, PostgreSQL must obtain it elsewhere.

The same principle applies to joins, sorting, filters, and other expressions: the complete execution plan determines which values must be retrieved and where they come from.

Step 4: Use INCLUDE to Build Better Covering Indexes

PostgreSQL supports the INCLUDE clause for B-tree indexes and certain other supported index methods.

Included columns are stored in the index as payload. They are not part of the index's search key and do not influence the ordering of index entries.

For example:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC)
INCLUDE (order_id, status, total_amount);

This index may help a query that retrieves a customer's most recent orders:

SELECT
    order_id,
    status,
    total_amount,
    created_at
FROM orders
WHERE customer_id = 5001
ORDER BY created_at DESC
LIMIT 20;

The index key supports filtering by customer and ordering by creation time. The included columns provide additional values needed by the query.

Whether this index is worthwhile depends on the query workload and data distribution.

Keep these points in mind:

Put columns used for filtering and ordering in the key portion when the index needs to support those operations.
Use INCLUDE for additional columns that are needed in the result but do not need to affect index ordering.
Avoid adding large text or binary values without evaluating the resulting index size.
Remember that included columns increase storage and write costs.
Verify the actual execution plan after creating the index.

A covering index is a workload-specific optimization, not a general rule that every query should have its own index.

Step 5: Measure the Effect of Vacuuming

If a frequently updated table has many heap fetches during index-only scans, compare its behavior before and after appropriate maintenance.

First, inspect the execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    status
FROM orders
WHERE customer_id = 5001;

Record the heap fetches, execution time, and buffer statistics.

Next, run maintenance when appropriate:

VACUUM (ANALYZE) orders;

Then repeat the same query under comparable conditions.

If the relevant pages are now all-visible, heap fetches may drop. If they remain high, investigate whether the table is being modified continuously or whether long-running transactions are preventing cleanup.

Do not treat a single timing result as proof of improvement. Execution time can vary because of caching, concurrent workloads, and I/O conditions. Compare several representative runs and focus on the plan details as well as latency.

Step 6: Design Index-Only Scans for Real Workloads

Index-only scans are especially useful for queries that repeatedly read a limited set of columns from tables with relatively stable data.

For example, a reporting query may retrieve order IDs, statuses, and dates from a table that receives most of its writes during the day but is read heavily overnight.

A covering index may reduce heap access for this workload, particularly after vacuuming has marked relevant pages all-visible.

By contrast, a table that is continuously updated may have fewer all-visible pages. Even with a covering index, PostgreSQL may need to check the heap frequently.

Before creating a new index, answer these questions:

Which query is slow, and how often does it run?
Which columns are used for filtering, ordering, joining, and output?
How many rows does the query actually return?
Does the execution plan show an index-only scan?
If so, how many heap fetches occur?
Is autovacuum keeping up with updates and deletes?
Will the new index create significant storage or write overhead?

These questions help distinguish an indexing problem from a vacuuming problem or a query-planning issue.

Index Scan vs. Index-Only Scan vs. Sequential Scan

Feature	Index scan	Index-only scan	Sequential scan
Uses an index to locate rows	Yes	Yes	No
Can return values directly from the index	Not generally for all requested values	Yes, when conditions allow	No
May visit the heap	Yes	Yes, for visibility checks when needed	Reads table pages directly
Requires a covering index	No	Yes, for the values the plan needs	No
Often useful for	Selective lookups	Selective lookups and reports with covering indexes	Small tables or queries returning many rows

No single scan type is always best. PostgreSQL chooses the plan based on its cost estimates and available statistics.

Common Mistakes to Avoid

Assuming INCLUDE guarantees zero heap fetches: Included columns solve the column-availability requirement, not MVCC visibility.
Creating unnecessarily wide indexes: Every additional indexed value increases storage and can make inserts and updates more expensive.
Running VACUUM FULL to fix every index-only scan: Ordinary vacuuming is usually the first maintenance step to investigate. VACUUM FULL rewrites the table, requires a strong lock, and is not a general solution for visibility-map issues.
Ignoring autovacuum: A busy table may repeatedly lose all-visible status as rows change. Review maintenance behavior rather than relying on occasional manual vacuuming.
Trusting estimated plans alone: Use EXPLAIN (ANALYZE, BUFFERS) when it is safe to execute the query and you need actual runtime evidence.
Assuming heap fetches mean the index is useless: An index-only scan can still be beneficial even when some heap checks occur. Evaluate the overall plan and workload.

Best Practices for PostgreSQL Index-Only Scans

Start with the actual slow query and inspect its execution plan.
Use INCLUDE for output columns that do not need to be part of the index key.
Keep index keys aligned with the query's filtering and ordering requirements.
Monitor heap fetches to determine whether visibility-map coverage is limiting the benefit.
Keep autovacuum working effectively, especially on frequently updated tables.
Use table-level vacuum tuning only when monitoring shows that the default behavior is insufficient.
Evaluate the storage and write costs of covering indexes before deploying them.
Test performance using representative data and repeatable conditions.

Summary

PostgreSQL index-only scans can improve query performance by returning data directly from an index rather than repeatedly accessing the heap. To benefit from them, the index must contain the values the query needs, the index access method must support the operation, and PostgreSQL must be able to establish row visibility efficiently.

The visibility map explains why an index-only scan can still report heap fetches. Frequently updated tables may have fewer all-visible pages, while effective vacuuming can help PostgreSQL mark eligible pages as all-visible again.

Use EXPLAIN (ANALYZE, BUFFERS) to check actual execution behavior, design covering indexes for specific queries, and monitor vacuuming rather than adding indexes blindly.

When index design, visibility-map maintenance, and query planning work together, index-only scans can reduce unnecessary heap access and make PostgreSQL workloads more efficient.