Index Only Scan in PostgreSQL EXPLAIN

What it does

An Index Only Scan reads matching entries from an index and returns the values stored there, without fetching the rows from the table (the heap). That saves one table visit per row, which is most of the work in an ordinary Index Scan.

There’s a catch. An index entry doesn’t say whether the row it points to is visible to your transaction: the row might have been deleted, or inserted by a transaction that hasn’t committed. PostgreSQL checks the table’s visibility map, one bit per table page that says “every row on this page is visible to everyone”. If the bit is set, the index value is returned straight away. If not, PostgreSQL fetches the row from the table to check, exactly as an Index Scan would. The plan counts those fetches as Heap Fetches.

VACUUM (and autovacuum) sets the visibility bits. Any insert, update or delete on a page clears its bit again until the next vacuum.

When the planner picks it

• Every column the query uses (in SELECT, WHERE, ORDER BY, joins, aggregates) is in the index. B-tree indexes always support index-only scans; GiST and SP-GiST do for some operator classes.
• The planner expects most pages to be all-visible. It reads relallvisible from pg_class, the number of all-visible pages at the last vacuum or analyse, and costs heap fetches for the rest.
• Typical cases: count(*) with a condition on an indexed column, SELECT DISTINCT on an indexed column, EXISTS checks, and joins that only need the key.

Reading its numbers

Index Only Scan using orders_created_at_idx on orders  (cost=0.42..289.61 rows=9259 width=8) (actual time=0.019..0.644 rows=10080.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=31

• Heap Fetches: rows that had to be checked in the table because their page wasn’t marked all-visible. 0 is the goal. A number close to rows means the scan was an index scan in disguise. See Heap Fetches. It’s a total over all loops.
• Index Cond and Filter: as for an Index Scan. A filter on an index only scan can only use columns in the index.
• Index Searches (PostgreSQL 18): how many times the index was descended.
• Buffers: index pages, plus table pages for heap fetches, plus visibility map pages. With Heap Fetches: 0 it’s almost all index.

When it’s a problem

• Heap Fetches is high. The table changes faster than it’s vacuumed, or it was loaded recently and hasn’t been vacuumed. Run VACUUM <table> and compare. For a busy table, make autovacuum visit it more often, for example ALTER TABLE <table> SET (autovacuum_vacuum_scale_factor = 0.02). Since PostgreSQL 13, inserts alone also trigger autovacuum, so append-only tables get their visibility map set without updates or deletes. Trade-off: more frequent vacuums use more I/O in the background.
• Long-running transactions block it. VACUUM can’t mark a page all-visible while an older transaction might still need to see the previous state. An idle in transaction session left open for hours keeps Heap Fetches high on every busy table. Look for old xact_start values in pg_stat_activity.
• The index is missing one column. If the query also needs total, the planner falls back to an Index Scan. Add it as a payload column: CREATE INDEX ON orders (customer_id) INCLUDE (total). Payload columns aren’t part of the search key, so they can’t be used to find rows, but they make the index wider; only include what the hot queries read.

In our tests, grouping all 1,000,000 orders by customer with sum(total) took 828 ms through an Index Scan on customer_id (one table visit per row) and 236 ms through an Index Only Scan on (customer_id) INCLUDE (total); see GroupAggregate.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
ANALYZE orders;   -- statistics, but no VACUUM yet

(Our test table also had a foreign key to customers; it doesn’t change these plans.)

One week of order timestamps needs only created_at, which is in the index. Before the table’s pages were marked all-visible, every row was checked in the table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-03-08';

Index Only Scan using orders_created_at_idx on orders  (cost=0.42..411.50 rows=10304 width=8) (actual time=0.040..2.396 rows=10080.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 10080
  Index Searches: 1
  Buffers: shared hit=19 read=97
Planning Time: 0.038 ms
Execution Time: 2.719 ms

pg_class.relallvisible for orders was 0 at this point. After a vacuum set the visibility map:

VACUUM (ANALYZE) orders;
SELECT relpages, relallvisible FROM pg_class WHERE oid = 'orders'::regclass;
-- relpages 8334, relallvisible 8334
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-03-08';

Index Only Scan using orders_created_at_idx on orders  (cost=0.42..289.61 rows=9259 width=8) (actual time=0.019..0.644 rows=10080.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=31
Planning Time: 0.050 ms
Execution Time: 0.970 ms

Same rows, 31 pages instead of 116, a third of the time. The planner’s cost estimate fell too, from 411 to 290, because relallvisible now said the pages were all-visible (the fresh ANALYZE also nudged the row estimate).

PostgreSQL 14–17 show the same fields without Index Searches, and print actual rows as whole numbers.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. Its Activity monitor lists sessions, which is a quick way to find the idle in transaction session that’s holding vacuum back.