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📝 Application Q15 of 15

PostgreSQL - Indexing Strategies

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?

AAdd a WHERE clause to filter only 'shipped' status.

BCreate a partial index on <code>(id)</code> where status = 'shipped'.

CDrop the existing index and rely on sequential scan.

DCreate a new index on <code>(status, id)</code> including both columns.

Step-by-Step Solution

Solution:

Step 1: Identify columns needed for index-only scan
The query selects id and status, so the index must cover both columns.
Step 2: Choose index that covers all columns
Creating an index on (status, id) includes both columns, enabling index-only scans.
Final Answer:
Create a new index on (status, id) including both columns. -> Option D
Quick Check:
Index covering all query columns enables index-only scan [OK]

Quick Trick: 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

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

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?

Step 1: Identify columns needed for index-only scan

Step 2: Choose index that covers all columns

Final Answer:

Quick Check:

Want More Practice?