Query Execution Plans

1. What Is It?

A query execution plan is the database's step-by-step strategy for executing a SQL query — which tables to access, in what order, which indexes to use, how to join tables, and how to sort or aggregate results. The query planner generates this plan by estimating costs and choosing the strategy it believes will be cheapest.

EXPLAIN shows you this plan. EXPLAIN ANALYZE actually runs the query and shows you both the estimated plan and the measured reality. Reading execution plans is the most important skill for diagnosing slow queries. Without it, index and query tuning is guesswork.

2. How It Works

Basic EXPLAIN output (PostgreSQL):

Copy
EXPLAIN SELECT id, total_cents FROM orders WHERE user_id = 42 AND status = 'paid';

Index Scan using idx_orders_user_status on orders  (cost=0.43..8.45 rows=3 width=16)
  Index Cond: ((user_id = 42) AND (status = 'paid'))

Key fields:

• Node type — what operation is performed (Index Scan, Seq Scan, Hash Join, Sort, Aggregate…)
• cost=start..total — planner's estimate of startup cost and total cost in arbitrary units
• rows=N — estimated number of rows this node produces
• width=N — estimated average row width in bytes

EXPLAIN ANALYZE — actual vs. estimated:

Copy
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total_cents FROM orders WHERE user_id = 42 AND status = 'paid';

Index Scan using idx_orders_user_status on orders
    (cost=0.43..8.45 rows=3 width=16)
    (actual time=0.082..0.095 rows=1 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'paid'))
  Buffers: shared hit=4
Planning Time: 0.3 ms
Execution Time: 0.2 ms

New fields with ANALYZE:

• actual time=start..end — real elapsed time in milliseconds for this node
• rows=N (actual) — real row count produced
• loops=N — how many times this node was executed (relevant in nested loops)
• Buffers: shared hit/read — cache hits vs. disk reads

Common node types and what they mean:

-+ResetFullscreen

Plans are trees — read from the innermost (most indented) nodes outward. Each node's output feeds its parent.

3. What SDEs Actually Need to Know

Seq Scan is not always bad. On a small table (a few hundred rows), Seq Scan is often faster than Index Scan because the overhead of navigating the index outweighs the cost of reading a few pages sequentially. The planner knows this. A Seq Scan on a table with 100,000+ rows that you expected to be filtered heavily is where you should investigate.

Large discrepancies between estimated and actual rows signal stale statistics. If the plan shows rows=3 but actual is rows=50000, the planner made decisions based on wrong assumptions — likely because ANALYZE hasn't run recently after a large data load. This can cause the planner to choose an Index Scan (thinking few rows match) when a Hash Join would be faster.

Copy
-- Refresh statistics after bulk load:
ANALYZE orders;

Nested Loop join is efficient when the inner input is small and well-indexed. It executes the inner scan once per outer row. If the inner table is large and unindexed, this becomes O(n×m) — very slow. Look for Nested Loop with a large loops= value as a warning sign.

Hash Join is typically best for joining large tables without a usable index on the join key. It's a one-pass build + probe. Its downside: the hash table must fit in work_mem; if it spills to disk, performance drops significantly.

Sort with Disk in the output means work_mem is too low for this query. Sorts spill to temporary disk files when the sort exceeds work_mem. Increasing work_mem for specific sessions (not globally) can help:

Copy
SET work_mem = '64MB';
-- Then run your query

The BUFFERS option reveals I/O behavior. shared hit=N means N pages were found in the buffer cache. shared read=N means N pages were read from disk. A query with many read buffers is I/O-bound; consider whether the working set fits in shared_buffers.

Using EXPLAIN without ANALYZE doesn't run the query — useful for destructive statements (DELETE, UPDATE) or expensive queries where you want to preview the plan without cost.

4. Tradeoffs & Decisions

When the planner chooses a bad plan

The most common cause is stale statistics — run ANALYZE. After that:

• Check if the index the planner should use actually exists
• Check if the index is being defeated by a function on the column: WHERE LOWER(col) = ?
• Check if the column has a data type mismatch: WHERE int_col = '42' (string vs. integer) may not use the index in some databases

Forcing an index (use sparingly):

PostgreSQL doesn't have direct index hints, but you can disable competing strategies for debugging:

Copy
SET enable_seqscan = off;  -- force index use for debugging only
EXPLAIN SELECT ...;
SET enable_seqscan = on;   -- always reset

Never leave query hints or disabled scan types in production code — they prevent the planner from adapting to changed data distributions.

plan_cache and prepared statements: In applications using prepared statements, the query plan is cached after the first execution. If data distribution changes (e.g., a column's cardinality shifts dramatically), the cached plan may become suboptimal. PostgreSQL uses generic plans after 5 executions; the custom plan for each execution considers actual parameter values.

CTEs as optimization fences (PostgreSQL < 12): In PostgreSQL before version 12, a CTE (WITH clause) was always materialized — the planner couldn't push predicates into it. This meant filtering after a CTE couldn't use indexes inside the CTE. From PostgreSQL 12 onwards, non-recursive CTEs are inlined by default. If you see a CTE producing unexpectedly many rows in an older system, this is likely why.

5. Interview Cheat Sheet

Key sentences:

• "I use EXPLAIN ANALYZE to see the actual execution time and row counts for each node — the difference between estimated and actual rows reveals stale statistics."
• "Seq Scan on a large table with a heavy filter means the query isn't using an index — I check whether the index exists, and whether a function call or type mismatch is defeating it."
• "A large loops= count on a Nested Loop node with an unindexed inner side is a performance red flag — it's O(n×m)."
• "Sort spilling to disk means the operation exceeded work_mem — I increase it session-scoped, not globally."

Common follow-ups:

Q: What's the difference between Index Scan and Bitmap Heap Scan? A: An Index Scan fetches one heap row at a time by following index pointers directly — good for very selective queries that return few rows. A Bitmap Heap Scan first builds a bitmap of all matching heap page locations from the index, then reads heap pages in physical order — better when many rows match, because it batches I/O rather than doing random single-row fetches.

Q: How do you read a complex execution plan with multiple joins? A: Start from the innermost (most indented) node and work outward. Each node feeds rows to its parent. Look for the nodes with the highest actual time and largest discrepancy between estimated and actual rows — those are the bottlenecks and the places where the planner's assumptions are wrong.

Q: What does "cost" mean in an execution plan? A: Cost is an arbitrary unit the planner uses to compare strategies. It's calculated from estimates of disk I/O, CPU, and row counts using configurable cost factors. It's not wall-clock time. The planner picks the plan with the lowest estimated total cost. When a "cheaper" plan runs slower in reality, the estimates were wrong — usually because of stale statistics or skewed data distributions.