Index-only scans mental model in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of an index-only scan query

Consider a PostgreSQL table employees with columns id, name, and department. An index exists on department and name. Which query will most likely produce an index-only scan result?

PostgreSQL

SELECT name FROM employees WHERE department = 'Sales';

A SELECT name FROM employees WHERE department = 'Sales';

B SELECT id FROM employees WHERE department = 'Sales';

C SELECT * FROM employees WHERE department = 'Sales';

D SELECT name FROM employees WHERE id = 10;

💡 Hint

Index-only scans work best when the query can be answered using only the indexed columns.

Option A queries only columns name and department, which are both in the index. This allows PostgreSQL to use an index-only scan without accessing the table data.

🧠 Conceptual

intermediate

Understanding visibility map role in index-only scans

What role does the visibility map play in PostgreSQL's index-only scans?

A It tracks which table pages are all-visible to avoid heap access during index-only scans.

B It stores the index data for faster lookups.

C It manages user permissions for index access.

D It logs query execution times for optimization.

💡 Hint

Think about how PostgreSQL avoids reading the main table during index-only scans.

The visibility map marks pages where all tuples are visible to all transactions. This allows index-only scans to skip heap access for those pages, improving performance.

📝 Syntax

advanced

Identify the query that forces a heap access despite index-only scan

Given a table orders with an index on customer_id and order_date, which query will NOT use an index-only scan and will force heap access?

A SELECT customer_id FROM orders WHERE order_date > '2023-01-01';

B SELECT customer_id FROM orders WHERE customer_id = 123;

C SELECT customer_id, total_amount FROM orders WHERE order_date > '2023-01-01';

D SELECT order_date FROM orders WHERE customer_id = 123;

💡 Hint

Index-only scans require all selected columns to be in the index.

Option C selects total_amount, which is not in the index. This forces PostgreSQL to access the heap to retrieve that column, preventing an index-only scan.

❓ optimization

advanced

Improving index-only scan effectiveness

You notice that your index-only scan queries still access the heap frequently. What is the best way to reduce heap access and improve index-only scan performance?

A Add more columns to the table to reduce index size.

B Run <code>VACUUM</code> to update the visibility map and mark pages as all-visible.

C Drop and recreate the index frequently.

D Disable autovacuum to prevent interference.

💡 Hint

Think about how PostgreSQL knows which pages are safe to skip.

Running VACUUM updates the visibility map, marking pages as all-visible. This allows index-only scans to skip heap access on those pages, improving performance.

🔧 Debug

expert

Diagnosing why an index-only scan is not used

You created a multi-column index on products(category, price). You run this query:

SELECT category FROM products WHERE price < 100;

But PostgreSQL does not use an index-only scan. What is the most likely reason?

A The index columns are in the wrong order for this query to use an index-only scan.

B The query selects only <code>category</code>, which is not in the index.

C PostgreSQL does not support index-only scans on numeric columns.

D The visibility map is not updated, so heap access is needed.

💡 Hint

Consider what PostgreSQL needs to skip heap access.

Even if the index covers the columns, if the visibility map is not updated (e.g., no recent VACUUM), PostgreSQL must access the heap to check tuple visibility, preventing index-only scans.

Practice

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 = '[e-mail]';

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