[Solved] Given the table orders(order_id, customer_id, status, total_amount), which index definition best enables an index-only scan for the query:SELECT customer_id, total_amount FROM orders WHERE status =... | PostgreSQL

PostgreSQL - Indexing Strategies

Given the table orders(order_id, customer_id, status, total_amount), which index definition best enables an index-only scan for the query:

SELECT customer_id, total_amount FROM orders WHERE status = 'completed';

ACREATE INDEX idx_total ON orders(total_amount);

BCREATE INDEX idx_customer ON orders(customer_id);

CCREATE INDEX idx_status ON orders(status);

DCREATE INDEX idx_status_customer_total ON orders(status, customer_id, total_amount);

Step-by-Step Solution

Solution:

Step 1: Analyze query columns
The query filters by status and selects customer_id and total_amount.
Step 2: Determine index coverage
To enable an index-only scan, the index must cover all these columns.
Step 3: Evaluate options
CREATE INDEX idx_status_customer_total ON orders(status, customer_id, total_amount); includes status, customer_id, and total_amount in the index, fully covering the query.
Final Answer:
CREATE INDEX idx_status_customer_total ON orders(status, customer_id, total_amount); -> Option D
Quick Check:
Index covers WHERE and SELECT columns [OK]

Quick Trick: Index must cover WHERE and SELECT columns for index-only scan [OK]

Common Mistakes:

Creating index on only one column
Ignoring columns in SELECT clause
Not including filter column in index

Master "Indexing Strategies" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the table orders(order_id, customer_id, status, total_amount), which index definition best enables an index-only scan for the query:

Step 1: Analyze query columns

Step 2: Determine index coverage

Step 3: Evaluate options

Final Answer:

Quick Check:

Want More Practice?