SQL EXPLAIN Plan — Reading and Optimizing Query Execution Plans
Fast recall
Quick revision
Core answer
Whether you can read an execution plan, identify the bottleneck node, and propose a specific fix — not just know that EXPLAIN exists.
Running EXPLAIN without ANALYZE and making optimization decisions based on estimated costs rather than actual execution times — estimates can be wildly inaccurate when table statistics are stale.
Explain the BUFFERS option, which shows how many pages were read from cache vs disk, and how to use pg_stat_statements to find the highest-impact queries to optimize.
Interview-ready guide
Interview answer
EXPLAIN ANALYZE is the most important tool for SQL performance work. It shows exactly how the database executes a query — not how you think it does — and those two things are often different.
EXPLAIN vs EXPLAIN ANALYZE
EXPLAINshows the planner's estimated execution plan without running the query — row counts and costs are estimates based on table statistics, which means they can be badly wrong when statistics are stale, and making optimization decisions from estimates alone leads to fixing the wrong problemEXPLAIN ANALYZEactually executes the query and reports actual row counts, actual execution time, and any discrepancy from the estimate — always useANALYZEin development or staging; wrap the query in a transaction and rollback if it modifies data
What to look for in the plan
Seq Scanon a large table means no index was used — the database read every row; look for a missing index on the filter column, or runANALYZE tablenameto refresh stale statistics that caused the planner to choose wrongIndex Only Scanis the ideal — all selected columns are in the index, so the heap is never read; this is the fastest possible plan for a read query- A large discrepancy between
rows=estimatedandactual rows=Xmeans the planner is working with stale statistics — runANALYZEon the table to update them
BUFFERS — cache vs disk
EXPLAIN (ANALYZE, BUFFERS)adds a breakdown of how many blocks were read from PostgreSQL's buffer cache versus from disk — a query reading 50,000 blocks from cache is fast; the same 50,000 blocks hitting disk is roughly 100x slower- This separates "the plan is wrong" from "the data is not cached" — two different problems with different solutions
Use pg_stat_statements to find the actual slowest queries across the database by total execution time — not just the queries you suspect are slow.
SQL EXPLAIN Plan — Reading and Optimizing Query Execution Plans
Direct Answer
EXPLAIN shows the query planner's execution plan — the sequence of operations it will perform. EXPLAIN ANALYZE actually runs the query and reports real execution times. The plan reads bottom-up as a tree of nodes: Sequential Scan, index Scan, Hash JOIN, Nested Loop, Sort. Understanding these plans is essential for diagnosing slow queries and validating that indexes are being used.
Common Mistakes & Pitfalls
Running EXPLAIN without ANALYZE and making optimization decisions based on estimated costs rather than actual execution times — estimates can be wildly inaccurate when table statistics are stale.
Check your understanding
Answer these without looking back at the article.
- How does the planner decide between a sequential scan and an index scan?
- What does Rows Removed by Filter tell you about index effectiveness?
- How do you use pg_stat_statements to find optimization targets?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.