EXPLAIN and Index Usage
How do you use EXPLAIN/query plans to see if a query uses an index? What warning signs do you look for?
Answers use simple, clear English.
Audio N/AQuick interview answer
Run EXPLAIN (ANALYZE) and look for Index Scan/Seek vs Seq Scan, estimated vs actual rows, and sort/hash costs. Warning signs: seq scan on huge tables unexpectedly, nested loop with huge row estimates, and missing predicates that prevent index use (wrapping columns in functions).
Detailed answer
Run EXPLAIN (ANALYZE) and look for Index Scan/Seek vs Seq Scan, estimated vs actual rows, and sort/hash costs. Warning signs: seq scan on huge tables unexpectedly, nested loop with huge row estimates, and missing predicates that prevent index use (wrapping columns in functions).
Real example & use case
A slow admin report switches from seq scan to index scan after adding (customer_id, created_at).
Pros & cons
Pros: evidence-based tuning. Cons: plans differ by stats/data — test on prod-like volumes.
Code example
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= NOW() - INTERVAL '30 days';Practice code · sql (view only · no execution)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= NOW() - INTERVAL '30 days';