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, reading every table row can be expensive. An index can help PostgreSQL find the required data faster, but a regular index scan may still need to visit the table to retrieve columns that are not stored in the index.

An index-only scan can avoid many of those table visits. When the index contains all the columns required by a query and PostgreSQL can verify that the relevant rows are visible, it can return results directly from the index.

However, an index-only scan does not always perform as expected. A query may use an index-only scan but still read many heap pages because PostgreSQL needs to check row visibility.

In this article, you will learn how index-only scans work, how to create suitable indexes, how to read execution plans, and how to troubleshoot cases where an index-only scan does not deliver the expected performance improvement.

What Is an Index-Only Scan in PostgreSQL?

An index-only scan is a query execution method that allows PostgreSQL to retrieve the required columns directly from an index without visiting the table for every matching row.

Consider an employees table with these columns:

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    full_name TEXT,
    department_id INT,
    salary NUMERIC(10, 2)
);

Suppose an application frequently retrieves employee IDs and salaries for a particular department.

SELECT employee_id, salary
FROM employees
WHERE department_id = 10;

Without a suitable index, PostgreSQL may need to scan the table or use another access method that requires additional table reads.

A normal index on department_id can help locate matching rows, but it does not contain salary. PostgreSQL may therefore need to visit the table to retrieve each matching employee's salary.

An index-only scan can avoid those visits if the index contains all the columns required by the query and the visibility conditions are satisfied.

How Index-Only Scans Work

Two conditions are important for an index-only scan.

1. The Index Must Contain the Required Columns

PostgreSQL needs to find all the columns used by the query in the index.

These can include columns used in the SELECT list, as well as columns required by conditions or other parts of the query.

For example, an index on department_id alone cannot provide salary unless that column is also stored in the index.

2. PostgreSQL Must Verify Row Visibility

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage different versions of rows.

An index entry does not normally contain all the information needed to determine whether its corresponding row version is visible to the current transaction.

PostgreSQL maintains a visibility map for table pages. When a page is marked as all-visible, PostgreSQL can rely on that information to avoid checking individual row visibility in the table.

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

This is why an index-only scan can still perform table reads.

How to Create an Index for an Index-Only Scan

Let's create an index that supports the earlier employee query.

CREATE INDEX idx_employees_department_salary
ON employees (department_id)
INCLUDE (salary);

This index contains:

department_id as the indexed key column.
salary as an included column stored in the index.

The query can now obtain both the filtering column and the requested salary from the index.

SELECT employee_id, salary
FROM employees
WHERE department_id = 10;

There is one important detail: employee_id is not explicitly included in this index definition. PostgreSQL's B-tree index on a table with a primary key does not automatically make every other index contain that primary-key column as a usable payload for an index-only scan.

To support this query reliably, include employee_id as well:

CREATE INDEX idx_employees_department_salary
ON employees (department_id)
INCLUDE (employee_id, salary);

Now the index contains all three columns required by the query.

The INCLUDE clause stores additional columns in the index without making them part of the index's search key. This can help PostgreSQL return the required data directly from the index.

Included columns are not used to determine the index's ordering or search structure, but they still occupy storage and add maintenance overhead when their values change.

How to Check Whether PostgreSQL Uses an Index-Only Scan

Use EXPLAIN to inspect the query plan.

EXPLAIN
SELECT employee_id, salary
FROM employees
WHERE department_id = 10;

If PostgreSQL chooses an index-only scan, the plan may contain a line similar to this:

Index Only Scan using idx_employees_department_salary
  on employees
  Index Cond: (department_id = 10)

The exact plan depends on the table size, data distribution, statistics, available indexes, and cost settings.

To inspect actual execution behavior, use EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id, salary
FROM employees
WHERE department_id = 10;

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

Pay attention to these details:

Index Only Scan: PostgreSQL selected an index-only scan.
Heap Fetches: The number of times PostgreSQL had to visit the table to verify visibility.
Buffers: Information about shared buffer activity and, when applicable, reads from storage.
Actual Rows: The number of rows actually returned by the plan node.
Execution Time: The time PostgreSQL took to execute the query, excluding some client-side overhead.

A plan that reports Index Only Scan is not automatically efficient. You must also inspect heap fetches, buffer activity, and execution time.

Why Index-Only Scans Sometimes Fail

There are several reasons PostgreSQL may not choose an index-only scan or may use one without delivering the expected benefit.

1. The Index Does Not Contain Every Required Column

Consider this query:

SELECT employee_id, salary
FROM employees
WHERE department_id = 10;

An index that contains only department_id cannot provide both requested columns.

PostgreSQL may choose a regular index scan and visit the table for the missing data.

How to fix it: Review the query's filtering and output columns, then consider whether an index with appropriate included columns is justified.

Avoid adding every table column to an index. Large indexes increase storage usage and make inserts, updates, and deletes more expensive.

2. Too Many Heap Fetches Occur

A query may use an index-only scan but still report a high number of heap fetches.

This often happens when the table is frequently modified. Updated rows can make pages no longer eligible to remain marked all-visible until vacuuming establishes that the pages satisfy the visibility requirements again.

For example, an employee table that receives frequent salary updates may experience more heap fetches than a mostly static reference table.

How to investigate: Run the query with EXPLAIN (ANALYZE, BUFFERS) and check its heap-fetch count.

Then inspect vacuum activity and the table's update rate.

SELECT
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'employees';

The statistics are estimates and timestamps, so use them as diagnostic signals rather than exact measurements.

Regular vacuuming can help restore all-visible information when the relevant pages become eligible. It cannot mark pages all-visible while transactions still prevent that conclusion.

3. The Table Changes Frequently

Index-only scans tend to be especially useful for tables that are read often but change relatively little.

Tables with frequent updates may repeatedly lose all-visible status on affected pages. This can increase heap fetches and reduce the advantage of index-only scans.

How to fix it: Check whether the query needs the additional index and whether the workload benefits from it. Also review autovacuum behavior and transaction duration.

For heavily updated tables, a regular index scan may sometimes be a reasonable choice.

4. PostgreSQL Chooses a Sequential Scan

Even when a suitable index exists, PostgreSQL may choose a sequential scan.

For example:

SELECT employee_id, salary
FROM employees
WHERE department_id BETWEEN 1 AND 100;

If the condition matches a large portion of the table, reading the table sequentially may cost less than scanning the index and processing many matching entries.

PostgreSQL's planner estimates the cost of different execution methods using table statistics, estimated row counts, and configuration settings.

How to fix it: Start with the actual execution plan and statistics.

ANALYZE employees;

Then inspect the plan again. Updated statistics can help PostgreSQL make a better decision when its previous estimates were inaccurate.

Do not force index usage simply because an index-only scan sounds faster. Test the query with representative data and realistic workloads.

5. The Index Is Too Large

Adding included columns can make an index significantly larger.

Large indexes require more disk space and memory and can increase write costs. If the index is too expensive to maintain, the performance gain from index-only scans may not justify the trade-off.

How to fix it: Include only columns that are useful for frequently executed queries. Measure index size, write activity, and query performance before expanding an index.

Index-Only Scan vs. Index Scan

These execution methods both use indexes, but they differ in how they obtain row data.

Feature	Index Scan	Index-Only Scan
Uses an index	Yes	Yes
Can return values directly from the index	Sometimes, for index-covered values	Yes, when visibility requirements allow
May visit table pages	Usually for matching rows	When visibility checks require it
Requires all needed columns in the index	No	Yes
Benefits from all-visible pages	Not in the same way	Yes
Works well for every workload	No	No

An index-only scan can reduce table access, but it is not always the fastest plan. The best choice depends on selectivity, table layout, index size, visibility information, and the query's actual workload.

Best Practices for Better Index-Only Scan Performance

1. Design Indexes Around Real Queries

Start with frequently executed queries and identify their filtering, sorting, and output columns.

Use key columns for searching and ordering. Consider INCLUDE for additional output columns that are useful to cover a query.

2. Keep Indexes Small

Every additional index consumes storage and must be maintained when relevant data changes.

Avoid creating overlapping indexes without measuring their value.

3. Monitor Heap Fetches

Use EXPLAIN (ANALYZE, BUFFERS) to check whether the query actually avoids table access.

A high heap-fetch count can explain why an index-only scan is not as fast as expected.

4. Maintain Accurate Statistics

Run ANALYZE when major data changes make planner estimates unreliable. Keep autovacuum and automatic analysis enabled unless you have a specific, evidence-based reason to change their behavior.

5. Consider the Table's Update Pattern

Index-only scans are often a strong fit for relatively stable data, reporting tables, and lookup workloads.

For tables with frequent updates, measure the benefits rather than assuming that covering every query with an index is the best design.

6. Benchmark Before and After

Record execution time, buffer activity, heap fetches, and index size before changing an index.

Test with realistic parameter values and representative data. A query that performs well on a small development table may behave differently against a large production table.

Common Mistakes to Avoid

Assuming INCLUDE columns are search keys: Included columns store payload data; they do not become ordinary index key columns.
Ignoring heap fetches: An index-only scan can still visit many table pages.
Creating oversized covering indexes: Extra columns increase storage and write overhead.
Forcing index usage too early: PostgreSQL may correctly prefer a sequential scan when a query matches many rows.
Running VACUUM FULL to improve every index-only scan: This is a disruptive table-rewriting operation and is not a general fix for heap fetches.
Judging performance from the plan name alone: Check actual timing, buffer activity, and rows processed.

Conclusion

PostgreSQL index-only scans can reduce table access by returning query results directly from an index. They work best when the index contains every required column and the visibility map allows PostgreSQL to verify row visibility without visiting the heap.

To get the most from index-only scans, design indexes around real queries, keep them reasonably small, maintain accurate statistics, and investigate heap fetches when performance falls short.

The goal is not to make every query use an index-only scan. It is to choose an index design and execution plan that provide the best overall performance for your workload.