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.