Index-only scans mental model in PostgreSQL - Step-by-Step Execution

Concept Flow - Index-only scans mental model

Start Query

↓

Check if index covers all needed columns

Yes↓

Perform Index-only Scan

↓

Fetch data from index pages only

↓

Return results

↓

End

No↓

Perform regular Index Scan or Seq Scan

↓

Fetch data from table heap

↓

Return results

↓

End

The query checks if the index contains all needed columns. If yes, it reads only the index pages, skipping the table data, making it faster. Otherwise, it reads the table data as usual.

Execution Sample

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) SELECT id, name FROM users WHERE id < 5;

This query uses an index-only scan if the index covers columns id and name, fetching data only from the index.

Execution Table

Step	Action	Index Covers Needed Columns?	Data Source	Rows Returned	Buffers Read
1	Start query execution	N/A	N/A	N/A	N/A
2	Check index columns	Yes	N/A	N/A	N/A
3	Perform index-only scan	Yes	Index pages only	4	Index: 3, Heap: 0
4	Return results	Yes	Index pages only	4	Index: 3, Heap: 0
5	End query	N/A	N/A	4	Index: 3, Heap: 0

💡 Index covers all needed columns, so heap pages are not accessed, making the scan faster.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
Index Covers Needed Columns	Unknown	Yes	Yes	Yes
Data Source	None	None	Index pages only	Index pages only
Rows Returned	0	0	4	4
Buffers Read (Index)	0	0	3	3
Buffers Read (Heap)	0	0	0	0

Key Moments - 2 Insights

Why does the index-only scan not read heap pages?

Because the index contains all columns needed by the query, so PostgreSQL can get all data from the index itself, as shown in execution_table rows 3 and 4.

What happens if the index does not cover all needed columns?

PostgreSQL must read heap pages to get missing columns, performing a regular index scan or sequential scan, as indicated in the concept_flow under the 'No' branch.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does PostgreSQL decide to perform an index-only scan?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Check the 'Index Covers Needed Columns?' column in execution_table at Step 2.

According to variable_tracker, how many heap buffers are read during the index-only scan?

A 0

B 4

C 3

D Unknown

💡 Hint

Look at 'Buffers Read (Heap)' values after Step 3 and final in variable_tracker.

If the index did not cover all needed columns, what would change in the execution_table?

A Rows returned would be zero

B Data source would include heap pages

C Buffers read for index would be zero

D Query would end immediately

💡 Hint

Refer to concept_flow where the 'No' branch leads to reading heap pages.

Concept Snapshot

Index-only scans read data directly from index pages without accessing table heap.
They work only if the index contains all columns needed by the query.
This reduces disk I/O and speeds up queries.
If columns are missing, PostgreSQL falls back to regular index or sequential scans.
Use EXPLAIN ANALYZE to check if index-only scan is used.

Full Transcript

An index-only scan happens when PostgreSQL can get all requested columns from the index itself, without reading the main table data (heap). The process starts by checking if the index covers all needed columns. If yes, PostgreSQL reads only the index pages, which is faster because it avoids extra disk reads. If not, it reads the table heap as usual. This is shown in the execution table where the decision is made at step 2, and the scan reads only index buffers. The variable tracker shows no heap buffers read, confirming the efficiency. Beginners often wonder why heap pages are skipped; it's because the index has all data needed. If the index lacks columns, PostgreSQL must read heap pages, slowing the query. This mental model helps understand when and why index-only scans speed up queries.