How can you combine a covering index with a partial index to optimize queries on orders where status = 'pending' and select order_id and customer_name?
hard📝 Application Q9 of 15
PostgreSQL - Indexing Strategies
How can you combine a covering index with a partial index to optimize queries on orders where status = 'pending' and select order_id and customer_name?
ACREATE INDEX idx_pending ON orders(order_id) INCLUDE (customer_name) WHERE status = 'pending';
BCREATE INDEX idx_pending ON orders(order_id, customer_name) WHERE status = 'pending';
CCREATE INDEX idx_pending ON orders(order_id) WHERE status = 'pending' INCLUDE (customer_name);
DCREATE INDEX idx_pending ON orders(order_id) INCLUDE (customer_name);
Step-by-Step Solution
Solution:
Step 1: Understand partial index syntax
Partial indexes use WHERE clause after columns and INCLUDE clause.
Step 2: Combine covering index with partial index
Index order_id with included customer_name and filter WHERE status = 'pending' is correct syntax.
Final Answer:
CREATE INDEX idx_pending ON orders(order_id) INCLUDE (customer_name) WHERE status = 'pending'; -> Option A
Quick Check:
Partial + covering index syntax = CREATE INDEX idx_pending ON orders(order_id) INCLUDE (customer_name) WHERE status = 'pending'; [OK]
Quick Trick:Put INCLUDE before WHERE in partial indexes [OK]
Common Mistakes:
Placing INCLUDE after WHERE clause
Including filter columns instead of indexing them
Omitting WHERE clause for partial index
Master "Indexing Strategies" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
