Skip to content
Quantoros

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

Related questions