Bird
Raised Fist0
PostgreSQLquery~10 mins

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

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
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
StepActionIndex Covers Needed Columns?Data SourceRows ReturnedBuffers Read
1Start query executionN/AN/AN/AN/A
2Check index columnsYesN/AN/AN/A
3Perform index-only scanYesIndex pages only4Index: 3, Heap: 0
4Return resultsYesIndex pages only4Index: 3, Heap: 0
5End queryN/AN/A4Index: 3, Heap: 0
💡 Index covers all needed columns, so heap pages are not accessed, making the scan faster.
Variable Tracker
VariableStartAfter Step 2After Step 3Final
Index Covers Needed ColumnsUnknownYesYesYes
Data SourceNoneNoneIndex pages onlyIndex pages only
Rows Returned0044
Buffers Read (Index)0033
Buffers Read (Heap)0000
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
Test your understanding
Look at the execution_table, at which step does PostgreSQL decide to perform an index-only scan?
AStep 1
BStep 3
CStep 2
DStep 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?
A0
B4
C3
DUnknown
💡 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?
ARows returned would be zero
BData source would include heap pages
CBuffers read for index would be zero
DQuery 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.

Practice

(1/5)
1. What is the main advantage of an index-only scan in PostgreSQL?
easy
A. It reads data only from the index without accessing the main table.
B. It updates the index faster than a normal scan.
C. It locks the table to prevent concurrent writes.
D. It creates a new index automatically during query execution.

Solution

  1. Step 1: Understand what an index-only scan does

    An index-only scan uses the index to get all needed data without reading the main table.

  2. Step 2: Compare options to this behavior

    Only It reads data only from the index without accessing the main table. describes reading data solely from the index, which is the key benefit.

  3. Final Answer:

    It reads data only from the index without accessing the main table. -> Option A

  4. Quick Check:

    Index-only scan = reads from index only [OK]
Hint: Index-only scans avoid table reads by using index data only [OK]
Common Mistakes:
  • Thinking index-only scans update data
  • Assuming they lock tables
  • Believing they create indexes automatically
2. Which of the following is a correct condition for PostgreSQL to use an index-only scan?
easy
A. The table has no indexes defined.
B. The index contains all columns needed by the query.
C. The query uses aggregate functions only.
D. The table is empty.

Solution

  1. Step 1: Recall index-only scan requirements

    PostgreSQL can use index-only scans only if the index has all columns the query needs.

  2. Step 2: Evaluate each option

    The index contains all columns needed by the query. matches the requirement; others do not enable index-only scans.

  3. Final Answer:

    The index contains all columns needed by the query. -> Option B

  4. Quick Check:

    Index-only scan requires full column coverage in index [OK]
Hint: Index-only scans need all query columns in the index [OK]
Common Mistakes:
  • Thinking index-only scans work without indexes
  • Assuming aggregates always use index-only scans
  • Believing empty tables affect index-only scans
3. Given a table users(id, name, email) with an index on (id, email), which query can use an index-only scan?
medium
A. SELECT id, email FROM users WHERE id = 10;
B. SELECT name FROM users WHERE id = 10;
C. SELECT email FROM users WHERE name = 'Alice';
D. SELECT * FROM users WHERE email = 'a@example.com';

Solution

  1. Step 1: Check index columns and query columns

    The index covers columns id and email. Query C requests only id and email.

  2. Step 2: Determine if index-only scan is possible

    Query C can use index-only scan because all requested columns are in the index. Others request columns not in the index.

  3. Final Answer:

    SELECT id, email FROM users WHERE id = 10; -> Option A

  4. Quick Check:

    Query columns ⊆ index columns = index-only scan [OK]
Hint: Index-only scan if query columns are subset of index columns [OK]
Common Mistakes:
  • Choosing queries requesting columns not in index
  • Ignoring WHERE clause columns
  • Assuming * always uses index-only scan
4. You have an index on (username, email) but your query SELECT email FROM users WHERE username = 'bob'; is not using an index-only scan. What could be the reason?
medium
A. The query uses a WHERE clause on username, so index-only scan is impossible.
B. The index does not include the email column.
C. The table's visibility map is not updated, so PostgreSQL must check the table.
D. PostgreSQL never uses index-only scans on text columns.

Solution

  1. Step 1: Confirm index covers needed columns

    The index includes username and email, so columns are covered.

  2. Step 2: Understand visibility map role

    Index-only scans require the visibility map to confirm tuples are visible without table access. If not updated, PostgreSQL reads the table.

  3. Final Answer:

    The table's visibility map is not updated, so PostgreSQL must check the table. -> Option C

  4. Quick Check:

    Visibility map must be updated for index-only scan [OK]
Hint: Visibility map must be updated for index-only scans [OK]
Common Mistakes:
  • Assuming index missing columns
  • Believing WHERE clause blocks index-only scan
  • Thinking data type prevents index-only scans
5. You want to optimize a query SELECT id, status FROM orders WHERE status = 'shipped'; for index-only scans. The current index is on (id). What is the best way to enable index-only scans?
hard
A. Add a WHERE clause to filter only 'shipped' status.
B. Create a partial index on (id) where status = 'shipped'.
C. Drop the existing index and rely on sequential scan.
D. Create a new index on (status, id) including both columns.

Solution

  1. Step 1: Identify columns needed for index-only scan

    The query selects id and status, so the index must cover both columns.

  2. Step 2: Choose index that covers all columns

    Creating an index on (status, id) includes both columns, enabling index-only scans.

  3. Final Answer:

    Create a new index on (status, id) including both columns. -> Option D

  4. Quick Check:

    Index covering all query columns enables index-only scan [OK]
Hint: Index must cover all selected columns for index-only scan [OK]
Common Mistakes:
  • Thinking partial index alone enables index-only scan
  • Dropping index reduces performance
  • Adding WHERE clause doesn't affect index structure