SQL interview question: Partial Index
Index Only What You Query
The ops dashboard polls pending orders by date every few seconds. Pending orders are a tiny slice of the table - indexing everything is waste.
Create a partial index named exactly idx_pending_orders on order_date, covering only rows WHERE status = 'pending'.
Note: The partial predicate is verified from the catalog. Rolled back afterwards.
This challenge changes data. Your statement runs on a private copy of the database, then we check the resulting table.
Tables
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |