What is EXPLAIN and how do you read it?
SQLLevel: IntermediateDatabase Design
What is EXPLAIN and how do you read it?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
EXPLAIN shows the query execution plan — what the database plans to do. EXPLAIN ANALYZE actually runs the query and shows real execution times.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;
Key things to look at:
- Seq Scan: full table scan — might indicate a missing index
- Index Scan / Index Only Scan: using an index — good for selective queries
- Cost: planner's estimate (not real time)
- Actual rows vs Estimated rows: large discrepancy means stale statistics (run ANALYZE)
- Rows Removed by Filter: high numbers suggest a missing index
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT