Index-only scans mental model in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an index-only scan in PostgreSQL?

An index-only scan is a way PostgreSQL reads data directly from an index without accessing the main table, making queries faster when all needed data is in the index.

beginner

Why does PostgreSQL sometimes need to access the main table even when using an index?

Because the index might not have all the information needed, so PostgreSQL checks the main table to get missing data or confirm visibility of rows.

intermediate

What condition must be met for PostgreSQL to use an index-only scan?

The index must contain all the columns needed by the query, and the visibility map must show that the data is visible without checking the main table.

intermediate

How does the visibility map help index-only scans?

The visibility map tracks which pages have only visible rows, so PostgreSQL can skip reading the main table pages during an index-only scan.

beginner

What is a practical benefit of index-only scans for database performance?

They reduce disk reads by avoiding the main table, which speeds up queries and lowers system load, especially for read-heavy workloads.

What does an index-only scan avoid reading?

A The main table data pages

B The index pages

C The query plan

D The database logs

An index-only scan reads data from the index without accessing the main table data pages.

Which PostgreSQL feature helps index-only scans skip table access?

A Query planner

B Write-ahead log

C Vacuum process

D Visibility map

The visibility map tracks which pages have all visible rows, allowing index-only scans to skip reading the main table.

When will PostgreSQL NOT use an index-only scan?

A When the index lacks needed columns

B When the query is simple

C When the table is small

D When the index is unique

If the index does not contain all columns required by the query, PostgreSQL must access the main table.

What is a key advantage of index-only scans?

A More accurate query results

B Faster query execution by reducing disk reads

C Automatic index creation

D Improved data insertion speed

Index-only scans speed up queries by reading only the index, reducing disk I/O.

What must be true about the visibility map for an index-only scan to work?

A It contains all table rows

B It is empty

C It shows pages have only visible rows

D It is disabled

The visibility map must indicate that pages contain only visible rows so the main table can be skipped.

Explain in your own words how an index-only scan improves query speed in PostgreSQL.

Think about how skipping the main table saves time.

Describe the conditions needed for PostgreSQL to use an index-only scan.

Focus on what the index and visibility map must provide.