Execution Plan Analysis

Hook: Interviewers love this question because a slow query is often not a SQL problem at all — it is a bad route chosen by the optimizer.

Question: How do you analyze an execution plan in SQL?

Answer: An execution plan is the database’s step-by-step strategy for running your query. I read it to see whether the engine is scanning whole tables, using indexes, choosing a sensible join order, and whether the estimated row counts are close to the real ones. If the estimates are far off, or I see expensive scans and joins, that usually points to missing indexes, stale statistics, or a query that needs rewriting.

Interview-Ready Answer: I start with EXPLAIN, and for real performance debugging I use EXPLAIN ANALYZE so I can compare estimated rows and cost against actual time and actual rows. Then I look for red flags like sequential scans on large tables, big estimate errors, nested loops over many rows, and filters that happen after reading the data instead of inside an index condition. My goal is to make the predicate sargable — search-argument-able, meaning the database can use an index directly — and to verify that the plan matches the data shape.

What it is

An execution plan is the optimizer’s recipe for answering your query. In most databases, the plan is a tree: leaf nodes read rows from tables or indexes, and parent nodes join, filter, sort, or aggregate those rows.

One important detail: cost is not time. It is an internal score the optimizer uses to compare choices. Two plans with costs 100 and 200 are only meaningful relative to each other on the same system, with the same statistics and settings.

How the planner chooses a plan

1. Parse the SQL. The database turns your text into an internal tree of tables, predicates, joins, and output columns.
2. Rewrite the query. It expands views, simplifies expressions, and pushes filters where it can.
3. Generate access paths. For each table, the optimizer considers options like sequential scan, index scan, bitmap scan, or index-only scan.
4. Estimate row counts. It uses statistics such as table size, null counts, histograms, and most-common values to guess how many rows each step will produce.
5. Pick join order and join type. It tries combinations and compares nested loop, hash join, and merge join options.
6. Assign cost. The planner uses page-read and CPU-style formulas to estimate work. Settings like random_page_cost and work_mem affect the choice.
7. Execute the chosen plan. With EXPLAIN ANALYZE, the engine also measures real time, actual rows, loops, and sometimes buffer reads.

How to read the output

• actual rows vs estimated rows: A big gap usually means the statistics are wrong or the predicate is misleading the optimizer.
• loops: This tells you how many times a node ran. A nested loop with 1,000 outer rows may execute the inner node 1,000 times.
• Filter vs Index Cond: Index Cond means the index helped narrow the search early. Filter means rows were removed after they were already read.
• BUFFERS: This helps separate CPU work from I/O. Many physical reads can make a query slow even when the time on one test looks okay.

Common scan types and when they appear

Node	Meaning	Best for	Watch out
Seq Scan	Reads all rows	Small tables	Expensive on big tables
Index Scan	Uses index then table	Selective filters	Random I/O
Index Only Scan	Reads index only	Covered queries	Needs visibility checks
Bitmap Heap Scan	Batch index hits	Many matching rows	Extra setup cost

For joins, the same idea applies: Nested Loop is good when one side is tiny; Hash Join is usually strong for large equality joins; Merge Join shines when both inputs are already sorted. If a hash table does not fit in work_mem — 4 MB by default in PostgreSQL — it can spill to disk and get slower.

When and why to use it

Use plan analysis when a query slows down, after adding or changing an index, after bulk loads, or when data distribution shifts. It is the fastest way to decide whether the fix is the SQL text, the index design, or stale statistics.

Important edge cases

• Small tables: A sequential scan can be faster than an index because reading the whole table once is cheaper than jumping around storage.
• Functions and casts: Wrapping a column in a function can block normal index use. Example: DATE(created_at) is often worse than a range predicate on created_at.
• Stale statistics: After large inserts, updates, or deletes, the planner can guess badly until ANALYZE or autovacuum refreshes the stats.
• Write queries: EXPLAIN ANALYZE on UPDATE, DELETE, or INSERT actually runs the change. Use a transaction and roll back if you are testing.
• Version and settings: Different database versions, or different values for random_page_cost and work_mem, can produce different plans. Do not compare plan text blindly across systems.

Real-World Story: Imagine a checkout service for an e-commerce site. The team has a page that looks up recent orders by customer and status. After a nightly bulk import, the page suddenly jumps from 40 ms to 6 seconds. The plan shows a sequential scan on a table with millions of rows, and the estimate is wildly wrong: the optimizer thinks only a handful of rows will match, but thousands do.

The symptom is easy to miss at first because the query still works, but users feel spinning loaders, timeout errors, and occasional checkout delays. In the logs you might see repeated slow-query warnings, high CPU, and plan output like actual rows far above the estimate. The fix is usually boring but effective: refresh statistics with ANALYZE, add a composite index that matches the real filter order, and rewrite predicates so the planner can use the index instead of scanning everything.

The big lesson: execution plan analysis turns a vague complaint like ‘the app feels slow’ into a concrete diagnosis like ‘the optimizer chose the wrong access path because the data shape changed.’

Follow-up & Tricky Questions:

• How do estimated rows and actual rows help you? A big mismatch means the planner misunderstood the data distribution, which usually points to stale statistics or a predicate shape that hides selectivity. If the numbers are close but the query is still slow, the problem is more likely I/O, memory, or join order.
• What does EXPLAIN (ANALYZE, BUFFERS) add? ANALYZE runs the query and reports real timing, while BUFFERS shows whether the query used cached pages or had to read from disk. That makes it much easier to tell CPU issues from storage issues.
• Why might an index be ignored? The table may be small, the predicate may match too many rows, the column may be wrapped in a function, or the query may need an implicit cast. In those cases, a sequential scan can genuinely be cheaper.
• How do composite indexes change the plan? The leftmost columns matter most, so an index on (customer_id, created_at) can help a query that filters by customer_id and then sorts or filters by created_at. If you reverse the order, the same query may no longer use the index well.
• How do join types show up in a plan? Look for Nested Loop, Hash Join, and Merge Join. Each one signals a different trade-off between row counts, sort order, and memory use.

Tricky / Gotchas:

• Does a low cost mean fast? No. Cost is an internal estimate, not elapsed time, so it is only useful for comparing plans on the same system.
• Does EXPLAIN ANALYZE on an UPDATE just simulate the write? No. It actually executes the statement, so use a transaction and roll it back if you are experimenting.
• Can Index Only Scan still touch the table? Yes. If the visibility map is not ready, PostgreSQL may still fetch heap pages to verify that the row is visible.

Common Mistakes:

• Reading cost as time. Correction: cost is only a planner score; check actual time for real performance.
• Ignoring estimate errors. Correction: compare estimated rows to actual rows first, because big gaps explain many bad plans.
• Adding indexes before checking the predicate. Correction: make the condition sargable first, then add the right index shape.
• Forcing the plan with settings. Correction: do not use knobs like disabling scans as a permanent fix; solve the data or query issue.

Memory Hook: Think of an execution plan like a GPS route. The database can take the highway, the local road, or a scenic detour; EXPLAIN tells you which route it chose, and EXPLAIN ANALYZE tells you whether that route was actually fast.

Cheat Sheet:

• EXPLAIN shows the chosen plan; EXPLAIN ANALYZE shows the plan plus real execution numbers.
• Look for sequential scans, bad join choices, and big gaps between estimated and actual rows.
• Filter means late filtering; Index Cond means early index use.
• Low selectivity, small tables, or non-sargable predicates can make a sequential scan the right choice.
• Stale stats, memory limits, and version differences can change the plan.

Practice Tasks:

• Run EXPLAIN on a simple query and identify the scan type.
• Add and remove an index, then compare the plan and actual timing.
• Rewrite a non-sargable predicate like DATE(created_at) into a range filter and see how the plan changes.