SQL EXPLAIN ANALYZE Deep Dive: Reading Execution Plans in Postgres, Snowflake, BigQuery

sql explain analyze is the primitive every senior data engineer, analytics engineer, and DBA reaches for at 3 a.m. when a dashboard query that used to run in 200 milliseconds suddenly takes 40 seconds — and it is the primitive most engineers can type on day one but only a small minority can actually read on day one thousand. The gap between "I know the keyword" and "I can look at a plan artefact and name the fix out loud in one breath" is the gap between a mid-level candidate who gets nervous when the interviewer opens the plan tab and a senior candidate who takes the mouse, points at the hot node, and says "the estimate is thirty times the actual — run ANALYZE, then rewrite the join." This guide is the honest tour of what actually happens inside the planner when you prefix a query with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — how the four numbers per node compose into a plan tree, why cost= is a made-up unit while actual time= is wall clock, how Snowflake's Query Profile, BigQuery's execution graph, and SQL Server's actual execution plan translate the same ideas into different artefacts, and how the same five-step reading discipline turns any of them into a concrete fix.

The tour walks the four engines you have to keep straight in 2026 — Postgres EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) with its cost / rows / loops / actual-time-per-node grid and the BUFFERS clause that surfaces the shared-hit / shared-read / temp-read triangle where spilling hides, the Snowflake Query Profile with its operator cards, micro-partition pruning percent, and local-vs-remote disk spill panel that decides warehouse sizing, the BigQuery execution graph with its stage-based DAG, slot-milliseconds accounting, --dry_run cost estimator, and INFORMATION_SCHEMA.JOBS audit trail, and finally the SQL Server actual execution plan XML with SET STATISTICS TIME/IO ON and the graphical SSMS view. Each section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works — so you leave with the mental model, the syntax, and the senior read-strategy your next interviewer is listening for.

When you want hands-on reps immediately after reading, drill the SQL optimization practice library → for plan-reading and rewrite drills, warm up the index reflex with the SQL indexing room →, and layer the broader SQL practice surface → covering 450+ DE-focused problems that pair every plan artefact with a concrete rewrite.

---

On this page

• Why execution plans matter in 2026
• Postgres EXPLAIN ANALYZE anatomy
• Snowflake QUERY_PROFILE
• BigQuery execution graph
• Dialect matrix + 5-step reading strategy
• Cheat sheet — plan-reading recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why execution plans matter in 2026

The sql query optimizer mental model — cost-based optimisation, statistics, cardinality estimation, and why every senior read-strategy starts with a plan

The one-sentence invariant: an execution plan is the planner's compiled decision tree for how to physically execute a logical SQL statement — which access method for each table, which join algorithm for each join, which order to evaluate joins in, whether to sort, whether to hash, whether to spill, whether to parallelise — and reading it is the fastest way to translate "this query is slow" into "here is the exact one-line fix". Every engine ships an optimiser; every optimiser makes decisions based on table statistics and a cost model; every cost model is imperfect; and every imperfect cost model produces a bad plan on some query somewhere in your product every week. The senior discipline is not to memorise a hundred rewrites — it is to open the plan, find the one node where estimate diverges from actual, and let that number tell you what to fix.

The planner mental model in five layers.

• Parser. Turns the SQL string into an abstract syntax tree. This step never fails silently — a syntax error surfaces as a parse error. Nothing interesting to read at this layer.
• Rewriter. Applies rule-based transformations — view expansion, subquery flattening, predicate pushdown into the CTE / subquery boundary, constant folding. On Postgres this is the pg_rewrite layer; on Snowflake it is folded into the optimiser; on BigQuery it happens before stage assignment.
• Cost-based optimiser (CBO). Considers many equivalent plan shapes, assigns a cost to each based on table statistics (row counts, distinct values, most common values, histograms, correlation coefficient), and picks the cheapest. This is where "the planner got it wrong" usually happens — the stats are stale, the histogram is coarse, the correlation between two columns is off, or the CBO does not know about a functional dependency your data has.
• Executor. Walks the chosen plan tree and returns tuples up the pipeline. Modern executors are pipelined (results stream up the tree) rather than materialised (each node writes to a temp table). The EXPLAIN ANALYZE output shows the actual row count and wall clock at each node — the executor's ground truth.
• Statistics collector. A background job on every engine that samples tables, updates histograms, and feeds the CBO on the next planning cycle. On Postgres — ANALYZE or autovacuum. On Snowflake — automatic and invisible. On BigQuery — table metadata refreshed continuously.

Cost units vs actual time — the two axes of every plan node.

• Cost is a made-up unit. In Postgres, cost is measured in "arbitrary planner units" — a Seq Scan on a 1-row table costs about 0.01 units; a Hash Join on a million rows might cost 250,000 units. The unit does not correspond to milliseconds, CPU cycles, or bytes. It is only meaningful relative to other plan alternatives during optimisation.
• Actual time is wall clock. actual time=0.017..0.019 means "the first row from this node was ready at 0.017 ms after query start; the last row at 0.019 ms." This is real, measurable, comparable across queries. Every senior read starts here — cost is the optimiser's guess, actual time is what happened.
• The estimate-vs-actual gap is the smoking gun. If a node has rows=100 loops=1 in the plan header but actual rows=100000 loops=1, the optimiser was off by a thousand times — a bad plan is almost guaranteed. Fix — run ANALYZE, check for stale stats, or add extended statistics on correlated columns.
• Cost is only comparable within a single plan. You cannot compare cost 12,500 for query A against cost 8,700 for query B on the same table — the units are internal to the planner, not portable across queries.

What senior interviewers actually probe when they open the plan tab.

• Do you know how to read the plan tree? Bottom-up, inner-most first. The Limit at the top is the last node executed; the Seq Scan / Index Scan at the bottom is the first. Candidates who read top-down miss the hot leaf every time.
• Do you know the node types? Seq Scan = full table scan. Index Scan = seek + heap fetch. Bitmap Heap Scan = index-driven heap fetch with bitmap OR. Hash Join = build hash table on smaller side, probe with larger. Merge Join = zip two pre-sorted streams. Nested Loop = outer × inner. Sort = sort operator (may spill). Hash Aggregate = hash-based GROUP BY. Gather = parallel-worker collector.
• Do you know when the plan is bad? Estimate off by 10× or more. Buffers: temp read non-zero (spilling to disk). Nested Loop on two large tables. Seq Scan where an index would work. Sort → Hash Aggregate where the Sort is doing all the work.
• Do you know the fix? Run ANALYZE. Add a covering index. Rewrite a correlated subquery into a JOIN. Add a LATERAL where a scalar subquery loops. Split a WITH RECURSIVE into a materialised temp table. Increase work_mem for a specific session. Add pg_hint_plan hints in exceptional cases.
• Do you know the trade-offs? Adding an index speeds reads but slows writes. Increasing work_mem speeds sorts but risks OOM under concurrency. Materialising a CTE eats memory but avoids a recompute. Hints lock the plan — great for known cases, terrible when data shifts.

The reading discipline — bottom-up, node-by-node, cost-vs-actual ratio.

• Step 1 — bottom-up. Find the leaf nodes (scans, Values clauses, function-returning-set nodes). Read their actual rows and actual time. This is what the executor read from disk (or memory).
• Step 2 — cost-vs-actual ratio. For each node, compare rows= (estimate) to actual rows=. A ratio within 2× is fine. 10× is worth investigating. 100× is almost always a bad plan.
• Step 3 — hot node. Find the node whose actual time is the largest fraction of total wall clock. This is the node to optimise. Speeding up a node that takes 0.1 ms of a 40 s query is pointless.
• Step 4 — spill / shuffle / parallelism. Check for Buffers: temp read (spill), Sort Method: external merge (bigger spill), Workers Launched: 0 (parallelism disabled), Rows Removed by Filter: 999999 (post-filter waste). Each is a specific fix.
• Step 5 — the one-line rewrite. Based on the hot node's failure mode, name the fix out loud — add index, rewrite join, increase memory, run ANALYZE, add hint. Senior candidates land on the fix within thirty seconds of opening the plan.

Why interviewers open plan tabs in senior loops.

• It's a fluency check on the executor mental model. Anyone can type EXPLAIN; the senior signal is being able to look at "Hash Join (cost=12500..37500 rows=1000 width=64) (actual time=45..190 rows=1000000 loops=1)" and say "estimate was a thousand rows, actual was a million — the join is spilling to disk and the plan is wrong" in one breath.
• It's a fluency check on the storage model. Postgres reads pages; Snowflake reads micro-partitions; BigQuery reads columnar files. The Buffers line, the bytes scanned line, the Partitions scanned line — each surfaces the storage cost that differs per engine.
• It's a fluency check on cost. On BigQuery, wrong plan = wrong bill ($5/TB scanned on-demand). On Snowflake, wrong plan = wrong warehouse size (2× per tier). On Postgres, wrong plan = wrong tail latency (100 ms → 40 s). Senior candidates can quantify the cost of a bad plan.
• It's a fluency check on system design. "Design a Postgres schema to support this dashboard" — great answers include the index plan (which composite indexes, in which order), the analyse cadence, and how the cost model reacts to skew. The plan surface is the system design surface.
• It's a fluency check on debugging discipline. "The query got 100× slower after a data load — walk me through your diagnosis." Every senior answer starts with EXPLAIN ANALYZE on the slow and fast versions and diffs the plan trees.

Worked example — the day-one EXPLAIN on a mystery-slow query

Detailed explanation. The archetype: a dashboard query that used to run in 200 ms now takes 40 seconds. The junior instinct is to add an index; the senior instinct is to open the plan and let the numbers name the fix. The single most useful EXPLAIN variant on Postgres is EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT).

Question. Given the query below on a Postgres 15 database, prefix it with the right EXPLAIN variant to surface plan shape, actual timings, buffer accounting, and node targeting. Name the four things you would read first.

Input.

-- The mystery-slow query
SELECT o.customer_id, SUM(o.total) AS lifetime_value
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Code.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.customer_id, SUM(o.total) AS lifetime_value
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Step-by-step explanation.

1. EXPLAIN alone prints the planned tree with estimates only — no query executes. Use this when the query is prohibitively expensive to actually run.
2. ANALYZE runs the query and returns real actual time and actual rows per node. This is what you want 95% of the time — the plan the executor actually walked.
3. BUFFERS adds the shared hit / shared read / temp read triangle per node — cache hits, cold buffer reads from disk, and temp writes (spill). Non-zero temp read is a bright-red flag.
4. VERBOSE adds the column list and expression detail for each node — expensive to eyeball but crucial when you need to know exactly which columns are projected at each layer.
5. FORMAT TEXT (the default) is the classic indented tree. FORMAT JSON is machine-readable for CI regression tests; FORMAT YAML is a middle ground.

Output (elided).

Limit  (cost=250.31..250.56 rows=100 width=16) (actual time=42.512..42.548 rows=100 loops=1)
  Buffers: shared hit=1234, read=456, temp read=42 written=42
  ->  Sort  (cost=250.31..252.81 rows=1000 width=16) (actual time=42.510..42.520 rows=100 loops=1)
        Sort Key: (sum(o.total)) DESC
        Sort Method: top-N heapsort  Memory: 33kB
        ...

Rule of thumb. For 95% of interview-flow diagnosis and on-call debugging, EXPLAIN (ANALYZE, BUFFERS) is the right variant. Add VERBOSE when you suspect a projection issue; add FORMAT JSON when you want to diff plans across two queries programmatically.

Worked example — cost units are not milliseconds (the day-two lesson)

Detailed explanation. The most common junior misconception: reading cost=12500..37500 and treating those as milliseconds. They are not. They are internal planner units used only to rank plan alternatives. The single number that translates to real time is actual time=.

Question. Given the plan fragment below, which numbers are milliseconds and which are made-up units? What is the total wall-clock time of the query?

Input.

Limit  (cost=12500.31..12500.56 rows=100 width=16) (actual time=42.512..42.548 rows=100 loops=1)
  ->  Sort  (cost=12500.31..12502.81 rows=1000 width=16) (actual time=42.510..42.520 rows=100 loops=1)
        ->  HashAggregate  (cost=12495.00..12497.50 rows=1000 width=16) (actual time=41.900..42.400 rows=850 loops=1)
              ->  Hash Join  (cost=1250.00..12480.00 rows=100000 width=12) (actual time=25.100..40.500 rows=98000 loops=1)
                    ...
Planning Time: 0.150 ms
Execution Time: 42.702 ms

Code. (No code — a plan-reading exercise.)

made-up units: cost=..
milliseconds:  actual time=..
row counts:    rows= (estimate) and actual rows= (real)
loops:         actual loops=N — number of times this node was executed
totals:        Planning Time (ms) + Execution Time (ms)

Step-by-step explanation.

1. cost=12500.31..12500.56 — the two numbers are the estimated startup cost and total cost, in arbitrary planner units. Startup cost is what the node pays before emitting its first row; total cost is what it pays to emit all rows.
2. actual time=42.512..42.548 — the two numbers are the real wall-clock times for the first and last row emitted by this node, in milliseconds. If loops > 1, these are the per-loop averages, not the total.
3. rows=100 — the optimiser's estimate. actual rows=100 — the real count. If these differ by 10×, the plan is likely suboptimal.
4. loops=1 — this node executed once. Nodes inside a nested loop, function scan, or subquery may execute many times; in that case wall-clock time = actual time (per loop) × loops.
5. Execution Time: 42.702 ms — the true wall clock for the query. Planning Time — how long the optimiser took to plan (usually sub-millisecond; large for many-JOIN queries).

Output. Wall-clock total = 42.702 ms. The cost numbers (12500, 12480) are not milliseconds — do not multiply them.

Rule of thumb. Read actual time for wall clock. Read cost only to compare plan alternatives when explaining why the optimiser chose plan A over plan B. Never quote cost numbers to a business stakeholder.

Worked example — bottom-up reading discipline

Detailed explanation. The plan tree in FORMAT TEXT is nested — inner-most first. Execution walks bottom-up: leaf nodes (scans) produce rows, they feed intermediate nodes (joins, aggregates), which feed the top node (Limit, Sort). Reading top-down misses which leaf is doing the work.

Question. Given the plan below, which node is the bottleneck? Which node should you optimise? Give the reasoning.

Input.

Limit  (actual time=1200.5..1200.6 rows=100 loops=1)
  ->  Sort  (actual time=1200.4..1200.5 rows=100 loops=1)
        ->  HashAggregate  (actual time=1198.3..1199.1 rows=850 loops=1)
              ->  Hash Join  (actual time=100.5..1195.4 rows=98000 loops=1)
                    ->  Seq Scan on orders o  (actual time=99.0..1150.0 rows=10000000 loops=1)
                          Filter: (created_at >= now() - '30 days'::interval)
                          Rows Removed by Filter: 90000000
                    ->  Hash  (actual time=0.5..0.5 rows=1000 loops=1)
                          ->  Seq Scan on customers c  (actual time=0.1..0.4 rows=1000 loops=1)
                                Filter: (country = 'US'::text)

Code. (No code — a plan-reading exercise.)

Step-by-step explanation.

1. Read bottom-up. The two Seq Scan leaves execute first. Seq Scan on customers c — 0.4 ms — cheap. Seq Scan on orders o — 1150 ms — this is the hot node.
2. Rows Removed by Filter: 90000000 on the orders scan — the engine read 100 M rows and threw away 90 M of them via the created_at >= NOW() - INTERVAL '30 days' filter. That means no index on created_at exists, or the filter is not sargable.
3. The Hash Join at 100.5..1195.4 inherits the cost of the orders scan (~1095 ms of the join time is upstream from the scan). The join itself is cheap.
4. HashAggregate and Sort are microseconds — they operate on 100 K joined rows, not 100 M.
5. The fix is at the leaf — add an index on orders(created_at). The join, aggregate, and sort are already optimal.

Output. The bottleneck is Seq Scan on orders o. Fix: CREATE INDEX ON orders(created_at). Estimated speed-up: 100 M reads → ~1 M reads = ~100× — from ~1200 ms to ~15 ms.

Rule of thumb. Bottom-up. Always. The leaf that dominates wall clock is the leaf to optimise. Top-level nodes rarely need attention unless they explicitly spill.

Common beginner mistakes

• Reading the plan top-down — missing the hot leaf.
• Treating cost= as milliseconds — confusing internal units for wall clock.
• Ignoring Rows Removed by Filter — a filter that discards 90% of rows is telling you which index to add.
• Skipping BUFFERS — the temp-read line is the smoking gun for spilling.
• Confusing loops=1 with loops=N — inside a nested loop, per-loop times must be multiplied by loops.

SQL interview question on when to reach for EXPLAIN ANALYZE

A senior interviewer often opens with: "You're on-call and a dashboard query for the CFO went from 200 ms to 40 seconds after last night's data load. Walk me through your first ten minutes of diagnosis. Which EXPLAIN variant do you run, which numbers do you read first, and what are the top three fixes you would try in order?"

Solution Using EXPLAIN (ANALYZE, BUFFERS) on the slow and fast versions, diffing plan trees

-- Step 1 — capture the slow plan
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 100;

-- Step 2 — capture the fast plan by rolling back one week
-- (via a read replica pinned to yesterday's snapshot, or a staging DB)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= '2026-06-03'
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 100;

-- Step 3 — refresh statistics if the slow plan estimate is off by 10× or more
ANALYZE orders;

-- Step 4 — re-run the slow plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 100;

-- Step 5 — if still slow, check for a missing index by hunting Rows Removed by Filter
-- Fix: CREATE INDEX ON orders(created_at) or a composite (customer_id, created_at DESC)

Step-by-step trace.

Step	Action	Reads
1	Prefix slow query with EXPLAIN (ANALYZE, BUFFERS)	Plan tree with cost/actual/loops/buffers
2	Compare to fast-version plan on replica	Diff shows which node's cost or rows changed
3	Look for rows= vs actual rows= gap ≥ 10×	Indicates stale statistics
4	Run ANALYZE orders to refresh statistics	Optimiser now has fresh histograms
5	Re-EXPLAIN — if plan is now fast, root cause was stale stats	Fixed
6	If still slow, look for Rows Removed by Filter: NNN	Missing index — add one
7	Verify final plan is Index Scan not Seq Scan at the hot leaf	Ship

Output:

Diagnosis outcome	Root cause	One-line fix
Estimate rows 10× < actual	Stale statistics	ANALYZE orders;
Rows Removed by Filter in the millions	Missing index	CREATE INDEX ON orders(created_at);
Buffers: temp read=NNN non-zero	Sort or hash spill	SET work_mem = '128MB'; (session)
Nested Loop on two large tables	Bad join estimate	Rewrite join, add composite index, or hint
Seq Scan on a large table with cheap filter	Selectivity < 1% but no index	Add partial index, or composite index

Why this works — concept by concept:

• Diff two plans — a slow-and-a-fast plan on the same query shape (yesterday vs today, prod vs replica) instantly localises the regression. If Hash Join was cheap yesterday and expensive today, the stats on the join columns are stale or the data distribution shifted.
• ANALYZE fixes stale statistics — after a big data load, the planner's histograms and MCV lists are out of date. Running ANALYZE table re-samples and refreshes them; the next EXPLAIN on the same query often shows a completely different, correctly-costed plan.
• Rows Removed by Filter names the missing index — the executor is telling you "I read N rows, threw away most of them via this filter." That filter should be an index seek instead of a scan. Add the index and the filter becomes an Index Cond — zero rows discarded.
• BUFFERS: temp read is spilling — the planner allocated a sort or hash that outgrew work_mem. Two fixes: rewrite the query to sort or aggregate less (LIMIT earlier, smaller GROUP BY), or bump work_mem for this session. Never bump it globally — under concurrency, every session gets its own copy and you run out of RAM.
• Cost — EXPLAIN alone is free (planning cost only). EXPLAIN ANALYZE runs the query — expensive if the query is slow. On production, prefer EXPLAIN alone for exploratory diagnosis; use EXPLAIN ANALYZE when you can afford the extra wall clock or when the query returns quickly enough. The read discipline itself is O(depth of plan tree) — a five-node plan reads in five seconds.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — indexing SQL indexing problems

Practice →

---

2. Postgres EXPLAIN ANALYZE anatomy

postgres explain analyze — the four numbers per node, the six major node types, and how BUFFERS surfaces the spill triangle

The mental model in one line: every Postgres plan node emits four numbers — cost (planner units), rows (estimate), actual time (wall clock), and loops (executions) — plus a variable-length BUFFERS triangle of shared hit / shared read / temp read that reveals whether the node lived in cache, hit cold buffers, or spilled to disk. Once you memorise the four numbers per node and the six major node types, you can read almost any Postgres plan in one pass.

Slot 1 — the four numbers per node.

• cost=startup..total — planner units. Startup cost is what the node pays before emitting its first row (sort must complete before emit; hash must build before probe). Total cost is the price to emit all rows. Ratio total / startup reveals whether the node is pipelined (near 1:1) or blocking (large gap).
• rows=N — the planner's estimate. Comes from table statistics (pg_stats) and the selectivity of the predicates above. Skewed columns without extended statistics produce bad estimates.
• actual time=first..last — wall clock (ms). First is when the first row was ready; last is when the last row was ready. If loops > 1, both are per-loop averages.
• actual rows=N — the real count. If rows=100 but actual rows=100000, the optimiser was off by 1000× — almost certainly a bad plan.
• loops=N — how many times the executor ran this node. loops=1 for most; loops=N for nodes inside a nested loop or a subquery per outer row. Wall-clock cost of the node = actual time × loops.

Slot 2 — the six major node types you must recognise on sight.

• Seq Scan — full table scan. Reads every row in the table, applies filters row-by-row. Fast on small tables; catastrophic on big tables when a filter has good selectivity (should have been an index).
• Index Scan — btree seek + heap fetch. Uses an index to locate rows, then reads the heap page. Best when selectivity is high (< 1% of table) and the projected columns are mostly in the index or need heap fetch.
• Index Only Scan — btree seek without heap fetch. Available when all projected columns are covered by the index. Fastest read path — often 10× faster than Index Scan because it skips heap I/O.
• Bitmap Heap Scan — index-driven with heap fetch, but the executor first builds a bitmap of matching heap pages, then reads them in sorted order. Fast on medium-selectivity filters (1–10% of table) — better than Seq Scan because it skips non-matching pages, better than plain Index Scan because it batches heap I/O.
• Hash Join — build a hash table on the smaller side, probe with the larger. O(build_side_rows + probe_side_rows). Great for large tables with no useful sort order; may spill if the build side exceeds work_mem.
• Merge Join — zip two pre-sorted streams. O(N + M) after sort, or O(N + M) if both sides come pre-sorted from an index. Great when input is already sorted; expensive if you have to explicitly sort first.
• Nested Loop — outer × inner: for each outer row, execute the inner. O(outer × inner). Fine when outer is tiny; catastrophic when outer is millions.
• Sort — external sort operator. Cheap when input fits work_mem; expensive when it spills to temp read. Sort Method: quicksort = in-memory; Sort Method: top-N heapsort = smart bounded sort for ORDER BY ... LIMIT N; Sort Method: external merge = spilled.
• Hash Aggregate / Group Aggregate — hash-based or sort-based GROUP BY. Hash Aggregate builds a hash on group keys; Group Aggregate requires pre-sorted input. Hash Aggregate may spill; Group Aggregate never does but requires a Sort upstream.
• Gather / Gather Merge — parallel worker collector. Gather collects rows in arbitrary order; Gather Merge preserves sort order from workers. Presence of these means the planner chose parallel execution — parallel_workers and max_parallel_workers_per_gather control this.

Slot 3 — the BUFFERS triangle: shared hit / shared read / temp read.

• shared hit=N — pages served from the buffer cache (shared buffers). Fast — RAM read, no disk I/O.
• shared read=N — pages read from disk into the buffer cache. Slower than hit; each read is ~100 μs on SSD, ~5 ms on spinning disk. High shared read = cold cache; may warm up on subsequent runs.
• shared dirtied=N — pages modified by the query (usually only for DML). Ignore for read-only diagnosis.
• temp read=N / temp written=N — pages the executor spilled to disk because a Sort, Hash Join, or Hash Aggregate overflowed work_mem. This is the spill signal. Any non-zero temp read means the query is thrashing — fix is either query rewrite (limit earlier, smaller GROUP BY) or SET work_mem = '128MB' for the session.
• shared read / (shared hit + shared read) — the cache miss rate. Under 5% is fine; over 50% means the working set doesn't fit in shared_buffers or the buffer cache is being churned.

Slot 4 — reading a plan bottom-up in five moves.

• Move 1 — identify the leaves. Every Seq Scan, Index Scan, Bitmap Heap Scan, Values Scan, Function Scan is a leaf. Read their actual time × loops for total per-leaf cost.
• Move 2 — find the hot leaf. The leaf with the largest actual time × loops is the bottleneck. If Seq Scan on orders is 1200 ms and everything else is 5 ms, the fix is at that scan.
• Move 3 — check Rows Removed by Filter. If a Seq Scan has Filter: created_at > NOW() - INTERVAL '30 days' and Rows Removed by Filter: 99000000, the filter should be an index. Add one.
• Move 4 — check estimate-vs-actual. For each node, compare rows= to actual rows=. A 10× gap is worth investigating; a 100× gap almost always means a bad plan. Run ANALYZE.
• Move 5 — check BUFFERS: temp read. Non-zero temp read = spill. Fix by rewriting the query or bumping work_mem.

Slot 5 — the estimate-vs-actual failure modes.

• Stale statistics. After a data load, the histograms and MCV lists are out of date. Run ANALYZE table to refresh.
• Skewed columns without extended stats. A column with 90% of rows having value 'X' and 10% having other values will fool the optimiser unless you create extended statistics: CREATE STATISTICS s ON col1, col2 FROM t;.
• Correlated columns. If country and city are highly correlated but the planner assumes independence, it can be off by 100× on multi-column predicates. Fix — extended statistics on (country, city).
• Function-wrapped columns. WHERE EXTRACT(YEAR FROM created_at) = 2026 is not sargable — the planner can't use the histogram. Rewrite: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.
• Prepared-statement plan reuse. With plan_cache_mode = 'auto', Postgres may pick a generic plan that is bad for specific parameter values. Force plan_cache_mode = 'force_custom_plan' if the shape hurts you.

Slot 6 — the eight most common Postgres plan pathologies.

• Seq Scan on a large filtered table. Missing index. Fix — CREATE INDEX ON t (filter_col); or a composite index if multiple filters combine.
• Nested Loop with millions on the outer side. Very slow — should be Hash Join. Fix — check estimates; if the planner underestimated the outer, run ANALYZE. If the estimate is right and the plan is still bad, raise enable_nestloop = off (dangerous — use only after diagnosis).
• Sort with external merge method. Spilling. Fix — rewrite to sort less (add LIMIT, smaller GROUP BY, pre-aggregate), or bump work_mem.
• Hash Join with temp read non-zero. Spilling. Fix — swap build side (planner may have picked the wrong side), or bump work_mem.
• Bitmap Heap Scan reading most of the table. The bitmap is close to full — the index isn't selective enough. Fix — either accept Seq Scan (which is what the planner would pick with better stats), or add a partial index.
• Index Scan with high Rows Removed by Index Recheck. The index was used but many rows didn't match the full filter — the index only covered part of the predicate. Fix — extend the index to a covering INCLUDE index.
• Parallel Seq Scan with Workers Launched: 0. Parallelism was disabled — usually because max_parallel_workers_per_gather = 0 or the table is too small (min_parallel_table_scan_size). Fix — configure appropriately.
• CTE Scan on a materialised CTE. Pre-PG12 CTEs were materialised (always). PG12+ inlines by default; if a CTE is materialised on new Postgres, it's an explicit WITH ... AS MATERIALIZED — verify that was intentional.

Slot 7 — EXPLAIN variants you should know.

• EXPLAIN query — plan-only, no execution. Free but based on estimates only. Useful for expensive queries or when you can't afford to run.
• EXPLAIN ANALYZE query — runs the query and returns actual timings. The everyday variant.
• EXPLAIN (ANALYZE, BUFFERS) — adds the BUFFERS triangle. Use always for on-call diagnosis.
• EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — adds column projections. Useful for projection issues.
• EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) — machine-readable. Great for CI regression tests, plan-diffing tooling, and pg_stat_statements correlation.
• EXPLAIN (ANALYZE, BUFFERS, WAL) — adds WAL (write-ahead log) accounting for DML. Useful when diagnosing slow INSERTs / UPDATEs.
• EXPLAIN (ANALYZE, BUFFERS, SETTINGS) — adds non-default GUC settings. Useful when a query works differently on prod vs a replica.

Common beginner mistakes

• Running EXPLAIN without ANALYZE on a slow query and drawing conclusions from estimates only.
• Bumping work_mem globally after seeing a single spill — every backend gets its own copy and OOM under concurrency.
• Adding an index without checking that the plan changes — sometimes the planner still prefers Seq Scan due to bad stats or low selectivity.
• Reading top-down — missing which leaf is the actual bottleneck.
• Ignoring loops — a Nested Loop with actual time=0.1ms loops=1000000 costs 100 seconds, not 0.1 ms.
• Copying EXPLAIN output into Slack without formatting — a plan tree is nearly unreadable when line breaks are lost.

Worked example — the Seq Scan that should have been an Index Scan

Detailed explanation. The most common Postgres plan bug: a Seq Scan on a large table where a filter has good selectivity. The plan shows a Seq Scan with Rows Removed by Filter in the millions; the fix is a single-column index.

Question. Given the plan below on an orders table (100 M rows), identify the fix. Show the DDL that would flip the plan to Index Scan (or Bitmap Heap Scan) and reduce wall clock from ~1200 ms to under 20 ms.

Input.

QUERY PLAN
Limit  (cost=... rows=100 width=16) (actual time=1200.5..1200.6 rows=100 loops=1)
  Buffers: shared hit=1234 read=456
  ->  Sort  (cost=... rows=100 width=16) (actual time=1200.4..1200.5 rows=100 loops=1)
        ->  Seq Scan on orders o  (cost=... rows=1000000 width=12) (actual time=99.0..1150.0 rows=1000000 loops=1)
              Filter: (created_at >= now() - '30 days'::interval)
              Rows Removed by Filter: 99000000
              Buffers: shared hit=1234 read=456

Code.

-- Fix: add index on the filter column
CREATE INDEX CONCURRENTLY idx_orders_created_at
  ON orders (created_at DESC);

-- Verify the plan flip
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY SUM(total) DESC
LIMIT 100;

Step-by-step explanation.

1. The current plan reads 100 M rows (Seq Scan on orders) and discards 99 M via the created_at filter — clear signal of a missing index on created_at.
2. CREATE INDEX CONCURRENTLY — the CONCURRENTLY clause avoids taking an ACCESS EXCLUSIVE lock, so writes continue during index build. Takes longer (double-scan) but keeps prod live.
3. DESC in the index matches the query's ORDER BY SUM(total) DESC — but the sort is on the aggregate, not created_at; so DESC here doesn't help ordering. It's still fine because btree indexes can be scanned in either direction.
4. After the index exists, run ANALYZE orders (or wait for autovacuum). The optimiser now has fresh selectivity estimates for created_at and will pick Index Scan or Bitmap Heap Scan when the filter is selective enough.
5. Re-run EXPLAIN (ANALYZE, BUFFERS) — the new plan should show Bitmap Heap Scan or Index Scan on orders with actual rows=1000000 (the filter-matching rows only) and wall clock under 20 ms.

Output.

Before	After
Seq Scan on orders	Bitmap Heap Scan on orders
actual rows=1000000 loops=1	actual rows=1000000 loops=1
Rows Removed by Filter: 99000000	Rows Removed by Filter: 0
Buffers: shared read=456	Buffers: shared read=12
Wall clock ~1200 ms	Wall clock ~15 ms

Rule of thumb. Any Seq Scan with Rows Removed by Filter in the millions is a missing-index diagnosis. The DDL is one line; the wall-clock win is 80–100× typical.

Worked example — the Sort that spills and how work_mem fixes it

Detailed explanation. A common failure mode: a Sort node with Sort Method: external merge and temp read in the buffers. The engine ran out of work_mem and spilled to a temp file. Two fixes — rewrite to sort less, or increase work_mem.

Question. Given the plan below with a spilled Sort, show two fixes and their trade-offs.

Input.

Sort  (cost=... rows=10000000 width=64) (actual time=8500.0..12000.0 rows=10000000 loops=1)
  Sort Key: total DESC
  Sort Method: external merge  Disk: 640000kB
  Buffers: shared hit=5000, temp read=80000 written=80000
  ->  Seq Scan on orders  (cost=... rows=10000000 width=64) (actual time=0.5..2200.0 rows=10000000 loops=1)

Code.

-- Fix A — bump work_mem for this session only (safe, cheap)
SET work_mem = '256MB';
SELECT * FROM orders ORDER BY total DESC;
RESET work_mem;

-- Fix B — rewrite to sort less (best when you only want top-N)
SELECT * FROM orders
ORDER BY total DESC
LIMIT 100;
-- Postgres picks Sort Method: top-N heapsort — bounded memory, no spill

-- Fix C — pre-aggregate before sorting (best when downstream only needs summaries)
SELECT customer_id, SUM(total) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

Step-by-step explanation.

1. The current plan sorts 10 M rows and spills 640 MB to disk (Sort Method: external merge Disk: 640000kB). Wall clock is 12 s.
2. Fix A — SET work_mem = '256MB' for the session. The Sort fits in memory, method changes to quicksort, wall clock drops to ~3 s. Never ALTER SYSTEM SET work_mem = '256MB' — under concurrency, 100 sessions × 256 MB = 25 GB of hash / sort memory.
3. Fix B — add LIMIT 100. The planner picks Sort Method: top-N heapsort — a bounded heap of size N. Memory usage is N × row_width, not full-table. Wall clock drops to ~2 s.
4. Fix C — pre-aggregate. Instead of sorting 10 M raw rows, sort 100 K aggregated rows. Hash Aggregate → Sort → Limit. Wall clock ~500 ms.
5. Prefer C > B > A. Rewriting the query is usually cheaper than tuning memory; increasing memory is the last resort when the query truly needs a big sort.

Output.

Fix	Sort Method	Disk spill	Wall clock
None	external merge	640 MB	12 s
A: work_mem=256MB	quicksort	0	3 s
B: LIMIT 100	top-N heapsort	0	2 s
C: pre-aggregate + LIMIT	quicksort	0	500 ms

Rule of thumb. Diagnose spill first — the Sort Method: external merge line and non-zero temp read are the smoking gun. Prefer to rewrite the query; bump work_mem only for the session, never globally.

Worked example — EXPLAIN (ANALYZE, BUFFERS) on a Hash Join bottleneck

Detailed explanation. A Hash Join between two large tables can dominate wall clock when the build side is bigger than expected. Reading BUFFERS reveals whether the hash is spilling.

Question. Given the plan below with a slow Hash Join, walk through diagnosis. Which side is the build side? What does temp read on the hash imply? What's the fix?

Input.

Hash Join  (cost=... rows=1000000 width=32) (actual time=4500.0..8500.0 rows=980000 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=1000 read=4500 temp read=25000 written=25000
  ->  Seq Scan on orders o  (cost=... rows=10000000 width=16) (actual time=0.5..3000.0 rows=10000000 loops=1)
  ->  Hash  (cost=... rows=5000000 width=20) (actual time=4200.0..4200.0 rows=5000000 loops=1)
        Buckets: 65536 Batches: 8 Memory Usage: 4096kB
        ->  Seq Scan on customers c  (cost=... rows=5000000 width=20) (actual time=0.5..2000.0 rows=5000000 loops=1)

Code.

-- Fix A — bump work_mem for the session
SET work_mem = '256MB';

-- Fix B — swap the build side by rewriting (planner picked wrong)
-- The customers table is bigger than orders in this query, but customers should be filtered first
SELECT *
FROM orders o
JOIN (SELECT id FROM customers WHERE country = 'US') c
  ON c.id = o.customer_id;
-- Now the hash is built on filtered customers (much smaller), not raw customers

-- Fix C — force the planner via a subquery / CTE materialisation
WITH us_customers AS MATERIALIZED (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.*
FROM orders o
JOIN us_customers c ON c.id = o.customer_id;

Step-by-step explanation.

1. Hash is the build side — Postgres always builds the hash on the bottom child of Hash Join. Here, that's customers c (5 M rows).
2. Batches: 8 — the hash was partitioned into 8 batches because it didn't fit in work_mem. Batches > 1 means spilling.
3. Buffers: shared hit=1000 read=4500 temp read=25000 — the temp read=25000 is the smoking gun. 25 000 pages × 8 KB = 200 MB spilled.
4. Fix A — bump work_mem. If bumped to 256 MB, Batches drops to 1 and temp read goes to 0; wall clock roughly halves.
5. Fix B — swap sides. If customers is filtered by a selectivity predicate (country = 'US'), do the filter before the join. The filtered customers hash is 500 K rows (10% of 5 M), fits in work_mem, no spill.

Output.

Fix	Batches	temp read	Wall clock
None	8	200 MB	8.5 s
A: work_mem=256MB	1	0	4.5 s
B: pre-filter build side	1	0	1.5 s
C: MATERIALIZED CTE	1	0	1.5 s

Rule of thumb. Batches > 1 on a Hash node = spilling. Prefer to filter the build side down before the join; work_mem bump is the last resort.

postgres explain analyze interview question on a two-plan diff

A senior interviewer often asks: "Here are two EXPLAIN (ANALYZE, BUFFERS) outputs for the same query — one from prod and one from a staging DB with the same data. Prod is 30 seconds; staging is 300 ms. Walk me through the diff and name the root cause."

Solution Using bottom-up plan diffing to isolate the regressing node

-- Prod plan (slow — 30 s)
Limit  (actual time=30000..30000 rows=100 loops=1)
  Buffers: shared hit=5000 read=45000 temp read=8000
  ->  Sort  (actual time=29995..30000 rows=100 loops=1)
        Sort Method: external merge  Disk: 64000kB
        ->  HashAggregate  (actual time=28000..29500 rows=850000 loops=1)
              ->  Hash Join  (actual time=100..27000 rows=1000000 loops=1)
                    Buffers: temp read=8000
                    ->  Seq Scan on orders  (actual time=0.5..12000 rows=100000000 loops=1)
                    ->  Hash  (actual time=500..500 rows=5000000 loops=1)
                          Batches: 8  Memory Usage: 4096kB
                          ->  Seq Scan on customers  (actual time=0.5..300 rows=5000000 loops=1)

-- Staging plan (fast — 300 ms)
Limit  (actual time=290..300 rows=100 loops=1)
  Buffers: shared hit=1500 read=200
  ->  Sort  (actual time=289..300 rows=100 loops=1)
        Sort Method: top-N heapsort  Memory: 33kB
        ->  HashAggregate  (actual time=250..280 rows=850000 loops=1)
              ->  Hash Join  (actual time=50..220 rows=1000000 loops=1)
                    ->  Bitmap Heap Scan on orders  (actual time=1..80 rows=1000000 loops=1)
                          Recheck Cond: (created_at >= '2026-06-10')
                          ->  Bitmap Index Scan on idx_orders_created_at
                    ->  Hash  (actual time=100..100 rows=5000000 loops=1)
                          Batches: 1  Memory Usage: 512000kB
                          ->  Seq Scan on customers  (actual time=0.5..80 rows=5000000 loops=1)

Step-by-step trace.

Node	Prod actual time	Staging actual time	Diff
Seq/Bitmap Scan on orders	12000 ms (Seq, 100M rows)	80 ms (Bitmap, 1M rows)	Missing index on prod
Hash (customers)	500 ms, Batches: 8	100 ms, Batches: 1	work_mem too low on prod
Sort	external merge, 64 MB spill	top-N heapsort, 33 kB	Sort method differs — cascades from filter
Total wall clock	30 s	300 ms	100× gap

Prod is missing an index on orders(created_at) — the Seq Scan reads 100 M rows and the filter discards 99 M. Staging has the index; the plan flips to Bitmap Heap Scan reading only 1 M matched rows. Prod's work_mem is also too low — the customers hash spills to 8 batches; staging has bigger work_mem and fits in 1 batch. Two fixes, one root cause per — but the dominant fix is the index.

Output:

Fix priority	Change	Est. wall clock after
1	CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at); then ANALYZE orders;	~500 ms
2	ALTER SYSTEM SET work_mem = '32MB'; (was 4 MB) then restart / reload	~350 ms
3	Ensure both plans are on Postgres 15+ (top-N heapsort improvements)	~300 ms

Why this works — concept by concept:

• Bottom-up diff — comparing plan trees leaf-first surfaces the storage-access divergence immediately. The prod Seq Scan vs staging Bitmap Heap Scan is a one-line diff that pinpoints the missing index.
• Rows Removed by Filter — even without staging as a reference, prod's plan alone shows 99 M rows discarded by a filter. That is the missing-index tell every time.
• Batches: N > 1 — the Hash node's Batches: 8 line means the hash spilled to 8 partitions on disk. Combined with Memory Usage: 4096kB and Buffers: temp read=8000, it names the spill and its size (~64 MB).
• Sort Method divergence — prod uses external merge (spilled); staging uses top-N heapsort (bounded). The method differs because the row count reaching the sort differs — the filter pathology upstream cascades all the way down.
• Cost — Diagnosis cost = one EXPLAIN (ANALYZE, BUFFERS) × 2 plans = 30 s + 300 ms wall clock. Fix cost = one CREATE INDEX CONCURRENTLY (typically minutes on 100 M rows, non-blocking) + ANALYZE. Downstream cost = zero — the index also benefits every other orders(created_at) filter in the codebase.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — indexing Composite index and covering-index drills

Practice →

---

3. Snowflake QUERY_PROFILE

snowflake query profile — the operator stack, micro-partition pruning percent, and how local vs remote disk spill drives warehouse sizing

The mental model in one line: Snowflake's Query Profile presents the executed query as a stack of operator cards — TableScan, Filter, Aggregate, Join, Sort, WindowFunction — each annotated with bytes scanned, partitions total vs partitions scanned (pruning percent), row counts, and a spillage panel with local disk spill and remote disk spill in bytes; reading it top-down and hunting for low pruning percent plus non-zero remote spill is the entire diagnosis discipline. Once you can navigate the Snowsight Query Profile tab and its underlying QUERY_HISTORY view, you can size warehouses, spot bad table clustering, and rewrite joins in one pass.

Slot 1 — where to find the profile.

• Snowsight → Activity → Query History → click into a query → Profile tab. The graphical, interactive view. Every operator is a clickable card with bytes scanned, rows produced, and % of total execution time.
• SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE query_id = 'abc123'; — the underlying table. Available roughly 45 minutes to 3 hours delayed. Columns include bytes_scanned, partitions_scanned, partitions_total, bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage, warehouse_size.
• SELECT * FROM SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY WHERE query_id = 'abc123'; — near-real-time (7 days retention). Same columns as ACCOUNT_USAGE but faster availability, shorter retention.
• GET_QUERY_OPERATOR_STATS(query_id) — table function returning per-operator statistics. Machine-readable; use for programmatic profile analysis.
• The SYSTEM$EXPLAIN_PLAN_JSON(query_text) function — returns the planned (pre-execution) plan as JSON. Useful when you can't afford to execute.
• WEB_UI shortcut — every query in Snowsight has a "Query Profile" link that opens the interactive view directly.

Slot 2 — the operator card anatomy.

• TableScan — the leaf. Reads from micro-partitions. Card shows Bytes scanned, Partitions total, Partitions scanned, and Pruning %. Pruning percent is the single most important number in every profile.
• Filter — applies a WHERE predicate. Card shows input row count and output row count. The pushdown-into-scan is invisible on Snowflake — good filters are absorbed into the TableScan card.
• JoinFilter — a Bloom filter used to skip micro-partitions on the probe side of a hash join. Presence means Snowflake is doing dynamic partition pruning — a huge win when it applies.
• Join — hash join (default), sort-merge join (rare), broadcast join (for small side). Card shows Build side rows, Probe side rows, and the join algorithm.
• Aggregate — hash-based GROUP BY. Card shows input rows, groups produced, bytes spilled (if any).
• Sort — order-by operator. Card shows input rows, bytes spilled, sort keys.
• WindowFunction — analytic function (ROW_NUMBER, RANK, SUM() OVER (...)). Card shows partitions, bytes spilled.
• Result — the final projection. Card shows total rows returned, total bytes returned to client.
• Broadcast / DistributeByHash — data-distribution nodes across the warehouse's cluster nodes. Presence of Broadcast usually means the planner detected a small side.
• Execution time % — every card has a per-operator percent of total execution time. Sort operators by this — the hot operator is the fix target.

Slot 3 — micro-partition pruning percent (the single most important number).

• Partitions total — how many micro-partitions the table has in total. Micro-partitions are ~50–500 MB chunks Snowflake maintains internally; you don't create or manage them.
• Partitions scanned — how many were actually read. Ideally a tiny fraction of total.
• Pruning % = 1 - partitions_scanned / partitions_total — the % of micro-partitions skipped by min/max pruning on the WHERE clause. 99%+ is great. 50% means half your storage cost. 0% means the filter was not sargable or the table wasn't clustered on the filter column.
• How Snowflake prunes. For each micro-partition, it stores min and max of each column. A WHERE created_at >= '2026-06-10' predicate can eliminate any micro-partition whose max < '2026-06-10'. Pruning happens before scan — no bytes read from pruned partitions.
• When pruning fails. Function-wrapped columns (DATE(created_at)), non-clustered columns, complex predicates the optimiser can't push down. Fix — rewrite the predicate to be sargable, or CLUSTER BY the table on the filter column.

Slot 4 — local vs remote disk spill and warehouse sizing.

• Local disk spill = spill onto the warehouse node's SSD. Slower than RAM but manageable. bytes_spilled_to_local_storage non-zero means the warehouse ran out of memory but is still within its SSD budget.
• Remote disk spill = spill onto Snowflake's remote storage (S3-backed). Slow — 10–100× slower than local spill. bytes_spilled_to_remote_storage non-zero is the strongest signal that the warehouse is too small.
• Warehouse sizing rule of thumb. If a query has non-zero remote spill, escalate one warehouse tier (X-Small → Small → Medium → Large → X-Large → 2XL → 3XL → 4XL). Each tier is roughly 2× the compute and 2× the memory. Remote spill going to zero usually happens within one or two tier bumps.
• Cost trade-off. Each tier bump doubles cost per minute. If a query is remote-spilling and you bump L → XL, you pay 2× per minute but the query may finish in 1/3 the time — net savings.
• Auto-suspend and multi-cluster. Auto-suspend is orthogonal; it kills the warehouse after idle. Multi-cluster is for concurrency (multiple queries at once), not for individual query size.

Slot 5 — bytes scanned vs partitions scanned.

• bytes_scanned — the compressed bytes Snowflake read from storage. Columnar reads mean only projected columns are counted.
• partitions_scanned — a proxy for I/O. Each micro-partition is ~50–500 MB uncompressed.
• bytes_written_to_result / bytes_written — outbound data.
• Cost model on Snowflake — compute is time-billed (per second on the warehouse tier), not bytes-billed. So bytes_scanned is a diagnostic, not a bill line. The bill is warehouse tier × query wall clock.
• Optimising bytes scanned — pick fewer columns (SELECT a, b, c not SELECT *), push predicates to prune, use CLUSTER BY on high-filter columns.

Slot 6 — reading a Snowflake profile top-down.

• Move 1 — sort operators by execution time %. The hot operator is the one with 40%+ of total time.
• Move 2 — check the hot operator's card. If it's a TableScan, check pruning %. If it's a Join, check build vs probe sizes. If it's an Aggregate, check spillage.
• Move 3 — check spillage panel. Non-zero remote spill = warehouse too small. Non-zero local spill = borderline.
• Move 4 — check bytes scanned across TableScans. If one TableScan reads 90% of total bytes, is it necessary? Can columns be projected away or partitions pruned?
• Move 5 — name the fix. Cluster the table. Rewrite the filter. Bump warehouse. Add a materialised view. Rewrite the join.

Slot 7 — the eight most common Snowflake profile pathologies.

• Pruning % = 0. Full-table scan. Either the filter isn't sargable or the table isn't clustered on the filter column. Fix — rewrite the filter (DATE(created_at) = '2026-07-01' → created_at BETWEEN '2026-07-01' AND '2026-07-02') or add CLUSTER BY (created_at).
• Non-zero remote disk spill. Warehouse too small. Bump one tier.
• JoinFilter not present. Snowflake didn't detect an opportunity for Bloom-filter dynamic pruning. Usually because one side is huge and the other is not filtered. Rewrite the query to filter the build side first.
• Broadcast join with a large "small" side. The planner broadcast a table that isn't actually small. Verify with SELECT COUNT(*) FROM small_side — if it's over ~1M rows, force a shuffle join by hinting.
• Cartesian join (accidental cross join). Card shows 10× the expected row count. Root cause is a missing join key. Search the query text for a missing ON clause.
• Sort with high spill. Same as Postgres — either rewrite to sort less (add LIMIT, pre-aggregate), or bump warehouse.
• Window function on a huge partition. ROW_NUMBER() OVER (PARTITION BY user_id) on a table where one user has 100 M rows spills catastrophically. Fix — pre-filter to relevant users, or handle skew explicitly.
• QUERY_HISTORY delay hurts on-call. ACCOUNT_USAGE.QUERY_HISTORY can be up to 3 hours delayed. For real-time on-call, use INFORMATION_SCHEMA.QUERY_HISTORY (near-real-time, 7 days).

Slot 8 — interview probes on Snowflake plan reading.

• "How do you read a Snowflake query profile?" — Open Snowsight Query History, click into the query, open the Profile tab. Sort operators by execution time %. Check hot operator's card (pruning %, spillage, join sides). Name fix.
• "What is micro-partition pruning?" — Snowflake maintains min/max per column per micro-partition. WHERE col < X eliminates partitions with min > X. Pruning % tells you the effectiveness of the filter against the physical layout.
• "Why is my query slow at page 1M on OFFSET pagination in Snowflake?" — Micro-partition pruning helps for the initial WHERE, but OFFSET's scan-and-discard is amplified by columnar reads. Switch to keyset (previous blog).
• "When would you use CLUSTER BY?" — When a table is very large (multiple TB), has a well-defined filter column (usually date), and pruning % is low. Cost — Snowflake maintains clustering in the background; cost per TB per month is documented.
• "How do you size a warehouse?" — Start Medium. Watch for remote spill. If zero, drop to Small; if non-zero, bump to Large. Iterate. Multi-cluster is orthogonal (for concurrency).

Common beginner mistakes

• Reading the profile bottom-up (Postgres reflex) — Snowflake profiles read top-down.
• Ignoring pruning % — a full-table scan at 0% pruning is silent unless you look at that number.
• Confusing local and remote spill — remote is much more expensive.
• Only bumping warehouse without checking pruning — often the fix is to rewrite the filter, not scale up.
• Missing that SELECT * costs more than SELECT a, b, c — Snowflake is columnar; fewer projected columns = fewer bytes scanned.

Worked example — the TableScan with 0% pruning that a CLUSTER BY cures

Detailed explanation. The archetype: a fact table sliced by date_col with a WHERE date_col = X filter shows pruning % of 0. The table isn't clustered on date_col; the min/max of each micro-partition covers the whole date range, so no partition can be pruned. The fix is CLUSTER BY (date_col).

Question. Given the profile card below on a events table (200 TB, 800 M micro-partitions) with WHERE event_date = '2026-07-10', name the fix. Show the DDL and the expected pruning % after.

Input.

TableScan (events)
  Partitions total:     800,000
  Partitions scanned:   800,000
  Pruning %:            0.0%
  Bytes scanned:        180 GB
  Rows produced:        4,200 M
  Execution time %:     78%

Code.

-- Fix: cluster the table by event_date
ALTER TABLE events CLUSTER BY (event_date);

-- Snowflake reclusters in the background; monitor with:
SELECT SYSTEM$CLUSTERING_INFORMATION('events', '(event_date)');

-- After clustering completes (typically hours to days on 200 TB):
-- The same WHERE event_date = '2026-07-10' should show:
-- Pruning % ≈ 99.5%
-- Bytes scanned ≈ 900 MB
-- Wall clock ≈ 10× faster

Step-by-step explanation.

1. Current state — 800 K partitions, 800 K scanned = 0% pruning. Each micro-partition's event_date min/max range covers so many days that no partition can be eliminated by the filter.
2. ALTER TABLE ... CLUSTER BY starts a background reclustering job. Snowflake rewrites micro-partitions so event_date values are co-located. Cost — measured in credits per TB; documented in Snowflake pricing.
3. SYSTEM$CLUSTERING_INFORMATION returns a JSON describing the current clustering depth. average_depth close to 1 means fully clustered.
4. After clustering, WHERE event_date = '2026-07-10' prunes down to the ~4 K micro-partitions covering that day — pruning % ≈ 99.5%.
5. Rule of thumb — cluster on the column your dashboard filters on. Almost always a date or timestamp. Don't cluster on high-cardinality unrelated columns.

Output.

Before	After
Pruning % 0.0%	Pruning % 99.5%
Bytes scanned 180 GB	Bytes scanned 900 MB
Partitions scanned 800,000	Partitions scanned 4,000
Execution time 78% of total	Execution time 8% of total
Wall clock ~4 min	Wall clock ~25 s

Rule of thumb. Any TableScan with pruning % under 50% on a filter column is a clustering diagnosis. Cluster once, benefit every dashboard that filters on that column.

Worked example — the remote spill that a warehouse bump cures

Detailed explanation. A Sort or Aggregate operator with non-zero bytes_spilled_to_remote_storage is the strongest single signal that the warehouse is undersized. One tier bump usually zeroes it out.

Question. Given the profile card below on a Sort operator with 220 GB remote spill on a Medium warehouse, name the fix. Show the ALTER WAREHOUSE that would cure it.

Input.

Sort
  Sort keys:                        event_date, user_id
  Input rows:                       4,200,000,000
  Output rows:                      4,200,000,000
  Bytes spilled to local storage:   80 GB
  Bytes spilled to remote storage:  220 GB
  Execution time %:                 61%
Warehouse:  MEDIUM

Code.

-- Fix A — bump the warehouse for this run
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'LARGE';
-- Re-run query
-- Expected: local spill ~40 GB, remote spill 0

-- Fix B — rewrite the query to sort less (LIMIT, pre-aggregate)
SELECT user_id, event_date, MAX(event_ts) AS last_event
FROM events
GROUP BY user_id, event_date
ORDER BY event_date DESC, user_id
LIMIT 1000;
-- Sort now operates on ~2M aggregated rows, not 4.2B raw rows

-- Fix C — if this is a one-off, use a larger warehouse only for this session
USE WAREHOUSE big_wh;  -- pre-created 2XL
-- run query
USE WAREHOUSE analytics_wh;

Step-by-step explanation.

1. Current state — Medium warehouse, 220 GB remote spill on the Sort. Remote spill dominates wall clock; each byte spilled to remote storage is 10–100× slower than in-memory.
2. Fix A — bump to Large. Large has 2× memory of Medium (~256 GB vs 128 GB for Sort spill headroom). Remote spill should drop to zero; local spill may remain but is manageable.
3. Fix B — rewrite. If the query only needs top-N or pre-aggregated data, do that first. Sorting 4.2 B rows to keep 1000 is wasteful; pre-aggregate to 2 M rows, then sort.
4. Fix C — separate warehouse. If this is a nightly job that hits Large only once, keep the daytime warehouse Medium and switch to a bigger warehouse only for the nightly step.
5. Cost trade-off — Large is 2× Medium's per-second cost, but if wall clock drops from 30 min to 5 min, net cost drops.

Output.

Fix	Remote spill	Local spill	Wall clock	Cost
None	220 GB	80 GB	30 min	30 × 1 = 30 units
A: Large warehouse	0	40 GB	8 min	8 × 2 = 16 units
B: pre-aggregate	0	0	3 min	3 × 1 = 3 units
C: dedicated 2XL for the job	0	0	4 min	4 × 4 = 16 units

Rule of thumb. Prefer B (rewrite) > A (bump warehouse) > C (dedicated warehouse). Rewriting is usually the cheapest and most durable fix.

Worked example — the Broadcast join that broke on data growth

Detailed explanation. Snowflake's planner picks broadcast join when one side is estimated small enough to distribute to all warehouse nodes. If the small side grows (say from 100 K rows to 20 M rows) but stats haven't refreshed, the broadcast may fail or become catastrophic.

Question. Given the profile card below with a Broadcast join that's dominating wall clock, name the diagnosis and the fix.

Input.

Join (INNER)
  Type:           BROADCAST
  Build side:     dim_customer (20 M rows, 1.2 GB in memory)
  Probe side:     fact_orders (4.2 B rows)
  Execution time %: 55%
  Bytes spilled to remote: 8 GB

Code.

-- Diagnosis: dim_customer isn't small anymore; broadcasting 20M rows to every node is expensive.

-- Fix A — force a hash join instead of broadcast (use hint)
SELECT /*+ NO_BROADCAST(dim_customer) */
  o.id, o.total, c.name
FROM fact_orders o
JOIN dim_customer c ON c.id = o.customer_id
WHERE o.event_date = '2026-07-10';

-- Fix B — filter dim_customer down before the join
SELECT o.id, o.total, c.name
FROM fact_orders o
JOIN (
  SELECT id, name FROM dim_customer WHERE active_flag = TRUE
) c ON c.id = o.customer_id
WHERE o.event_date = '2026-07-10';

-- Fix C — cluster fact_orders by event_date so the probe side prunes first
ALTER TABLE fact_orders CLUSTER BY (event_date);

Step-by-step explanation.

1. Broadcast join built a hash on dim_customer (20 M rows, 1.2 GB) and shipped it to every warehouse node. Memory pressure caused spill.
2. Fix A — hint NO_BROADCAST on dim_customer forces a shuffle join; both sides are hash-partitioned by the join key. No broadcast means no per-node memory footprint.
3. Fix B — filter the dim table to only the active customers (say 4 M rows). The broadcast becomes cheap again.
4. Fix C — cluster the fact table so the WHERE event_date prunes probe-side partitions first. Combined with (A) or (B), the query is near-optimal.
5. Interview signal — knowing when Snowflake picks broadcast vs shuffle join and how to force each is a senior-level probe.

Output.

Fix	Join type	Remote spill	Wall clock
None	Broadcast	8 GB	4 min
A: NO_BROADCAST	Hash / Shuffle	0	2 min
B: filter dim	Broadcast (smaller)	0	90 s
B + C	Broadcast + probe pruning	0	45 s

Rule of thumb. Broadcast join is optimal for genuinely small dim tables (< 10 K rows). When the "small" side grows past ~1 M rows, force shuffle join or filter the dim first.

snowflake query profile interview question on plan reading and warehouse sizing

A senior interviewer often asks: "A finance dashboard on Snowflake takes 8 minutes to load. You're the on-call DE. Walk me through the Query Profile — what do you look at first, what's the ordered checklist of fixes, and how do you decide when to bump the warehouse vs when to rewrite the query?"

Solution Using the top-down profile checklist with pruning, spill, and operator time %

-- Step 1 — find the query and its query_id
SELECT query_id, query_text, execution_time / 1000 AS seconds
FROM SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY
WHERE start_time > DATEADD(hour, -1, CURRENT_TIMESTAMP)
ORDER BY execution_time DESC
LIMIT 20;

-- Step 2 — pull the per-operator stats
SELECT *
FROM TABLE(GET_QUERY_OPERATOR_STATS('abc123-def456'))
ORDER BY execution_time_breakdown_overall_percentage DESC;

-- Step 3 — check pruning on TableScans
SELECT
  operator_type,
  operator_attributes:table_name::string AS table_name,
  operator_statistics:pruning:partitions_scanned::int AS scanned,
  operator_statistics:pruning:partitions_total::int AS total_partitions,
  1 - (operator_statistics:pruning:partitions_scanned::float
       / operator_statistics:pruning:partitions_total::float) AS pruning_pct
FROM TABLE(GET_QUERY_OPERATOR_STATS('abc123-def456'))
WHERE operator_type = 'TableScan';

-- Step 4 — check spillage
SELECT
  bytes_spilled_to_local_storage,
  bytes_spilled_to_remote_storage,
  warehouse_size
FROM SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY
WHERE query_id = 'abc123-def456';

Step-by-step trace.

Step	Signal	Action
1	Find query_id from QUERY_HISTORY	Isolate the slow query
2	List operators by execution time %	Identify the hot operator (usually TableScan or Sort/Join)
3	Check pruning % on all TableScans	If any < 50%, cluster or rewrite filter
4	Check remote spill in QUERY_HISTORY	If non-zero, bump warehouse OR rewrite
5	Check Broadcast joins on non-small tables	Force NO_BROADCAST hint or filter first
6	Verify SELECT * isn't the culprit	Project only needed columns
7	Re-run and diff	Confirm expected drop in wall clock

Output:

Signal	Root cause	One-line fix
Pruning % < 50% on filter column	Table not clustered	ALTER TABLE t CLUSTER BY (col)
Remote spill > 0	Warehouse too small OR bad plan	Bump 1 tier OR rewrite
Broadcast join with build > 1M rows	Stale stats or growing dim	/*+ NO_BROADCAST(t) */ hint
SELECT * on 100-column table	Excess columnar reads	Project only needed columns
Sort spilling on top-N	No LIMIT push-down	Add explicit LIMIT + pre-aggregate

Why this works — concept by concept:

• Top-down operator time % — Snowflake profiles are naturally ordered by execution time %. The hot operator jumps to the top; you don't have to hunt for it. Read from there.
• Pruning % is the pre-scan lever — every byte you don't scan is free. Clustering shifts the pruning % from 0 to 99% for the filter column. That single move often 10×'s dashboard queries.
• Remote spill = memory pressure — remote is 10–100× slower than local spill. Zero remote spill is the sizing target. When bumping a warehouse takes remote spill to zero, you've found the right tier.
• Rewrite > scale, when possible — rewriting (adding LIMIT, pre-aggregating, filtering dims first) is durable and doesn't inflate cost per query. Bumping warehouse is fast but pays 2× per tier.
• Cost — profile diagnosis is $0 (a few metadata queries). Fix costs vary — clustering a 200 TB table takes credits to reclusters; bumping a warehouse for one query is fractional cost; rewriting the SQL is free but takes engineer-time. Net-cost usually favours rewrite > cluster > scale.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — aggregation Pre-aggregation and GROUP BY drills

Practice →

---

4. BigQuery execution graph

bigquery execution plan — the stage-based DAG, slot-milliseconds, shuffle bytes, --dry_run cost estimation, and INFORMATION_SCHEMA.JOBS

The mental model in one line: BigQuery's execution model is a directed acyclic graph of stages, where each stage is a set of parallel workers reading input, applying a piece of the query, and writing output that gets shuffled into the next stage; reading the graph is a matter of finding the hot stage (largest slot_ms), checking shuffle bytes (network cost), and reading the INFORMATION_SCHEMA.JOBS row for the query to correlate slot time against wait time. Once you can read the stage graph and the JOBS row, you can size reservations, spot skew, and translate cost per query into cost per week.

Slot 1 — the stage-based model.

• Stages are the unit of parallel work. Each stage has an input (from disk or a previous stage's shuffle output), a compute step (filter, aggregate, join), and a shuffle output. Stages run in parallel wherever their inputs are ready.
• Slots are the compute unit. Each stage runs on some number of slots (BigQuery's word for "worker threads"). On-demand pricing gives 2000 slots per project by default; flat-rate reservations pin exact slot counts.
• Slot-milliseconds (slot_ms) — the fundamental unit of BigQuery compute cost. If a stage uses 100 slots for 4.2 seconds, that's 100 × 4200 = 420,000 slot_ms. The whole-query total_slot_ms is the sum across all stages.
• Wall clock ≠ slot time. Wall clock is what the user waits. Slot time is total compute. A stage using 100 slots for 4.2 s has 420,000 slot_ms but only 4.2 s of wall clock. High parallelism means wall clock much less than slot time.

Slot 2 — reading the stage graph.

• Console view — BigQuery UI → Query History → click query → Execution details tab. Shows the DAG with each stage's slot time, wait time, and shuffle bytes.
• Stage columns. slot_ms, wait_ms, read_ms, compute_ms, write_ms, shuffle_output_bytes, records_read, records_written.
• Skew signal. If a stage has slot_ms significantly larger than expected given records_read, the workers are unbalanced — some workers read 100× more than others. Skew is expensive.
• Wait vs compute. wait_ms > compute_ms means the stage was queued waiting for slots (concurrency pressure). compute_ms > wait_ms means the query was compute-bound (worked flat out).
• Hot stage. The stage with the largest slot_ms is the bottleneck. Optimising a stage with 200,000 slot_ms of a 220,000-total is worthwhile; optimising a 200-slot_ms stage isn't.

Slot 3 — --dry_run for cost estimation without execution.

• bq query --dry_run --use_legacy_sql=false 'SELECT ...' — returns the estimated bytes processed without running the query.
• --dry_run is free. No slots used, no bytes billed.
• Estimate accuracy. Usually within 5% of actual on partition-pruned queries. Larger error on complex joins where the planner can't estimate output.
• Use case — cost gates in CI. Wrap queries in a dry-run + assertion: fail if estimated bytes exceed a threshold. Catches accidental SELECT * on a 100 TB table before deployment.
• Web console equivalent. The query editor shows "This query will process X TB when run" — same estimator.

Slot 4 — INFORMATION_SCHEMA.JOBS audit trail.

• SELECT * FROM \region-us.INFORMATION_SCHEMA.JOBS WHERE job_id = 'abc'; — full metadata row per query.
• Key columns. total_bytes_processed, total_slot_ms, total_bytes_billed, start_time, end_time, cache_hit, statement_type, query.
• job_stages — nested array with per-stage details.
• referenced_tables — array of tables the query touched. Useful for lineage.
• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION — organisation-wide view (requires admin role).
• Retention. ~180 days. Sufficient for weekly cost reviews.

Slot 5 — slot utilisation vs wait time (the CPU vs queue distinction).

• Compute time = time slots actually worked. Green area in the console utilisation chart.
• Wait time = time slots were assigned but blocked (usually on I/O or shuffle). Amber area.
• Read time = time reading from disk / column storage. Grey area.
• Write time = time writing shuffle output. Blue area.
• Diagnosis reading. If wait time > compute time, the query is I/O-bound or shuffle-bound; a bigger reservation won't help. If compute time > wait time, more slots would speed it up (assuming you have them).

Slot 6 — on-demand vs flat-rate slot billing.

• On-demand. $5 per TB scanned (US region). Bills by total_bytes_billed. Slots are elastic — up to 2000 per project. Best for spiky workloads.
• Flat-rate reservations. Commit to X slots per month. Predictable bill. Queries wait in queue when they exceed the reservation. Best for consistent workloads.
• BI Engine. In-memory cache for dashboarding queries. Reduces slot cost when the same query runs repeatedly.
• Storage cost is separate. ~$20/TB/month for active storage; ~$10/TB/month for long-term (unchanged for 90 days).
• Egress cost is separate. Charged per byte leaving GCP.

Slot 7 — reading the graph in five moves.

• Move 1 — find the hot stage. Sort by slot_ms desc. The top stage is the bottleneck.
• Move 2 — check hot stage's compute vs wait. Compute > wait = more slots would help. Wait > compute = I/O bound.
• Move 3 — check shuffle bytes. Large shuffle between two stages means the intermediate result is big. Aggregate earlier if possible.
• Move 4 — check records_read vs partition prune. If a stage read 1 T records but the table is 100 T records, pruning worked. If it read 100 T, pruning failed — check partition and cluster keys.
• Move 5 — name the fix. Partition the table on the filter column, cluster on high-cardinality keys, rewrite SELECT *, use approximate aggregations for cardinality estimates.

Slot 8 — the eight most common BigQuery execution-graph pathologies.

• Full-table scan on a partitioned table. Filter isn't partition-pruning-friendly. WHERE DATE(created_at) = '2026-07-10' on a table partitioned by DATE(created_at) prunes. WHERE EXTRACT(YEAR FROM created_at) = 2026 doesn't.
• SELECT * on a wide table. Every column is read even if only three are used. Fix — project only what you need.
• Skewed join. One key value has 100× more rows than others; one worker gets stuck. Fix — pre-aggregate, sample, or handle skew explicitly.
• Cross join without predicate. The classic FROM a, b WHERE a.x = b.x typo. BigQuery aborts on huge cross joins.
• Large shuffle between stages. The intermediate is bigger than either input. Sometimes fine (aggregation reduces later); usually a bad plan.
• Cache miss on repeated dashboarding queries. BI Engine not configured. Fix — add a reservation with BI Engine.
• On-demand querying a 100 TB table daily. $500/query. Fix — partition, cluster, materialised view, or move to flat-rate.
• Slot wait time > compute time. Queue pressure. Fix — larger reservation, or run job in a less contended time window.

Slot 9 — interview probes on BigQuery plan reading.

• "How do you read a BigQuery execution plan?" — Open Query History, click into query, open Execution details. Find hot stage by slot_ms. Check compute vs wait. Diagnose shuffle. Name fix.
• "What's slot_ms?" — Slot × milliseconds. The fundamental compute unit. Total slot_ms = sum across all stages.
• "When would you use --dry_run?" — For cost estimation before running expensive queries. Also in CI to gate PRs that would scan too much.
• "How do you use INFORMATION_SCHEMA.JOBS?" — Query it for slow-query audits, cost attribution, or plan history. Correlates well with team dashboards.
• "On-demand or flat-rate?" — On-demand for spiky ad-hoc analytics ($5/TB is fine when volume is modest). Flat-rate for consistent workloads where you can commit to steady slot usage.

Common beginner mistakes

• Confusing slot_ms with wall clock — 100 slots × 4 s = 400 slot_s, but wall clock was 4 s.
• Forgetting to filter on the partition column — full-table scan on a partitioned table costs the same as an unpartitioned table.
• Using SELECT * in dashboards — every extra column is a byte scanned = a cent billed.
• Ignoring shuffle bytes — the intermediate stages may be bigger than the input if the plan is bad.
• Running expensive queries without --dry_run first.

Worked example — the full-table scan on a partitioned table

Detailed explanation. The most common BigQuery bill surprise: a table partitioned by DATE(created_at) but the query filter is WHERE EXTRACT(YEAR FROM created_at) = 2026 — the filter can't prune partitions because the planner can't inverse-map the function.

Question. Given the query below on a 100 TB events table partitioned by DATE(created_at), why does --dry_run estimate 100 TB? What's the fix?

Input.

SELECT user_id, COUNT(*) AS n
FROM `project.dataset.events`
WHERE EXTRACT(YEAR FROM created_at) = 2026
  AND EXTRACT(MONTH FROM created_at) = 7
GROUP BY user_id
LIMIT 100;

Code.

-- Diagnose
bq query --dry_run --use_legacy_sql=false '
SELECT user_id, COUNT(*) AS n
FROM `project.dataset.events`
WHERE EXTRACT(YEAR FROM created_at) = 2026
  AND EXTRACT(MONTH FROM created_at) = 7
GROUP BY user_id
LIMIT 100'
-- Output: This query will process 100 TB when run.

-- Fix: rewrite to a sargable partition filter
SELECT user_id, COUNT(*) AS n
FROM `project.dataset.events`
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-08-01'
GROUP BY user_id
LIMIT 100;
-- Dry run: This query will process 3 TB when run. (One month of 100 TB × 12.)

Step-by-step explanation.

1. Original query wraps created_at in EXTRACT(...). BigQuery's partition pruner can't invert the function to identify which partitions match — it falls back to full-table scan.
2. --dry_run reveals the damage: 100 TB estimated. At $5/TB on-demand, that's $500 per run.
3. Fix — rewrite the filter as a range on the raw column: WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'. The partition pruner can now identify exactly the July 2026 partitions.
4. Re-run --dry_run: 3 TB estimated. At $5/TB, that's $15 per run — 30× cheaper.
5. Rule of thumb — always filter partitioned tables on the raw partition column with >= / < / BETWEEN. Never wrap in a function.

Output.

Query	Dry-run bytes	On-demand cost
WHERE EXTRACT(YEAR FROM ...)	100 TB	$500
WHERE created_at BETWEEN ...	3 TB	$15
WHERE created_at BETWEEN ... AND cluster_col = X	300 GB	$1.50

Rule of thumb. Always --dry_run before running big-table queries. Always filter on the partition column with sargable predicates. Cluster the table on the next-most-common filter column for even more pruning.

Worked example — the skewed join that dominates one stage

Detailed explanation. A join where one key value has 100× more rows than others creates a skewed stage — one worker gets stuck processing the hot key while others finish quickly. Symptom: one stage's slot_ms is much larger than expected.

Question. Given the execution graph fragment below showing a skewed join stage, name the diagnosis and the fix.

Input.

Stage 3 — HashJoin (fact_events × dim_user)
  Records read:     4,200,000,000
  Records written:  4,200,000,000
  slot_ms:          82,000,000
  wait_ms:          400
  compute_ms:       410,000
  Shuffle output:   660 GB
  Skew warning:     True — user_id=SYSTEM has 800M rows

Code.

-- Fix A — filter the hot key out (if valid)
WITH filtered_events AS (
  SELECT *
  FROM fact_events
  WHERE user_id != 'SYSTEM'    -- exclude the system pseudo-user
    AND created_at BETWEEN '2026-07-01' AND '2026-07-31'
),
system_events AS (
  SELECT *
  FROM fact_events
  WHERE user_id = 'SYSTEM'
    AND created_at BETWEEN '2026-07-01' AND '2026-07-31'
)
SELECT ... FROM filtered_events JOIN dim_user USING (user_id)
UNION ALL
SELECT ... FROM system_events   -- handle SYSTEM separately

-- Fix B — hash-partition the join key with a salt
SELECT ...
FROM fact_events e
JOIN dim_user u ON u.user_id = e.user_id
  AND MOD(e.hash_salt, 10) = MOD(u.hash_salt, 10)

Step-by-step explanation.

1. Stage 3 shows 82 M slot_ms — the dominant stage. Records read is 4.2 B, but the skew warning notes one user_id (SYSTEM) accounts for 800 M rows.
2. In a hash join, all rows with the same key hash to the same worker. SYSTEM sends 800 M rows to one worker while 100+ others finish quickly.
3. Fix A — split the hot key. Query the SYSTEM subset separately, then UNION ALL with the non-SYSTEM subset. Each subset's join is balanced.
4. Fix B — salt the join. Add a hash-salt column that varies within the hot key, so the 800 M rows spread across 10 workers instead of 1. Requires schema change to the dim table.
5. Interview signal — recognising skew from the execution graph and naming a mitigation is a senior-level probe.

Output.

Fix	slot_ms	Wall clock
None	82,000,000	8 min
A: filter hot key	42,000,000	4 min
B: salted key	44,000,000	3 min
A + partition prune	8,000,000	45 s

Rule of thumb. Any stage with slot_ms >> average is a skew candidate. Fix by isolating the hot key or salting it.

Worked example — moving from on-demand to a reservation for cost savings

Detailed explanation. A team running 50 similar dashboards on-demand at $5/TB every hour discovers they're paying $30,000/month in BigQuery. A flat-rate reservation can cap the bill while providing the same throughput — often 40% cheaper.

Question. Given the monthly usage below, decide whether to stay on-demand or switch to flat-rate. What size reservation would break even?

Input.

Monthly total bytes billed: 6 PB
Monthly on-demand cost:      $30,000  ($5/TB * 6,000 TB)
Average slot_ms/day:         2.1 * 10^10  (typical 400 slots utilized on avg)
Peak slots concurrent:       ~1200 (short bursts)
Off-peak:                    ~200
Query cadence:               15,000 queries/day

Code.

-- Estimate flat-rate cost at various commitments
-- Assumes $2000/mo per 100 slots (approximate US pricing; verify current)
-- 500 slots  = $10,000/mo — throttled at peaks
-- 700 slots  = $14,000/mo — few queue stalls
-- 1000 slots = $20,000/mo — covers 95% peaks
-- 1200 slots = $24,000/mo — covers 99% peaks
-- Autoscale up to 500 flex slots = ~$1000/mo extra

Step-by-step explanation.

1. On-demand at $30 K/month; queries run at up to 1200 slots peak but average 400 slots.
2. A 700-slot flat-rate reservation ($14 K/month) covers the average with headroom. Queries can queue during peaks — usually OK for dashboards.
3. Add 500 flex-slot autoscaling ($1 K/month typical) to cover the top 5% of peak-slot-demand queries without queueing.
4. Total ≈ $15 K/month vs $30 K on-demand — 50% cheaper for the same throughput.
5. Trade-off — reservations require a monthly commitment; on-demand is elastic. If usage drops 50% next quarter, on-demand shrinks with it; flat-rate doesn't.

Output.

Model	Slots	Cost / month	Notes
On-demand	2000 (elastic)	$30,000	Elastic; pays per TB scanned
Flat 500 slots	500	$10,000	Queues at peaks
Flat 700 + 500 flex	700+500	$15,000	Sweet spot
Flat 1200 slots	1200	$24,000	Almost no queueing

Rule of thumb. Flat-rate wins when average slot utilisation is above ~30% of peak. On-demand wins for spiky ad-hoc analytics. INFORMATION_SCHEMA.JOBS gives you the raw material to model this.

bigquery execution plan interview question on cost + performance tuning

A senior interviewer often asks: "A dashboard on a 200 TB partitioned + clustered events table is slow and expensive — 4 minutes per load, $8 per query, running every hour. Walk me through your diagnosis using the execution graph and INFORMATION_SCHEMA.JOBS, and name the top three fixes ranked by ROI."

Solution Using stage-graph diagnosis + partition pruning + materialised view

-- Step 1 — pull job metadata
SELECT
  job_id,
  total_bytes_processed / POW(1024, 4) AS tb_scanned,
  total_slot_ms,
  end_time - start_time AS wall_clock
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'abc123';

-- Step 2 — read the stage graph via query_plan (JSON)
SELECT
  step.name        AS stage_name,
  step.slot_ms     AS stage_slot_ms,
  step.records_read AS records_read,
  step.shuffle_output_bytes AS shuffle_bytes
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(job_stages) AS step
WHERE job_id = 'abc123'
ORDER BY step.slot_ms DESC;

-- Fix A — narrow the partition filter
-- Was: WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
-- Now: WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
-- Effect: 200TB → 15TB scanned per query

-- Fix B — pre-aggregate into a materialised view
CREATE MATERIALIZED VIEW `project.dataset.mv_daily_events` AS
SELECT
  DATE(created_at) AS event_date,
  user_id,
  COUNT(*) AS event_count,
  SUM(revenue) AS revenue
FROM `project.dataset.events`
GROUP BY event_date, user_id;

-- Dashboard now queries the MV, scanning ~10 MB per day per user instead of raw events

-- Fix C — add BI Engine to the reservation
-- (Console → BigQuery → Reservations → Add BI Engine capacity — e.g. 20 GB)
-- Cached repeat queries served in milliseconds

Step-by-step trace.

Fix	Bytes scanned	Slot_ms	Wall clock	Cost / query
Baseline (90-day filter, raw events)	200 TB	15 * 10^10	4 min	$1,000
A — narrow to 7 days	15 TB	1.3 * 10^10	45 s	$75
A + B — MV pre-aggregation	300 MB	1 * 10^8	3 s	$0.02
A + B + C — BI Engine cache	300 MB (first) / 0 (cache)	~0	200 ms	$0 (cache hit)

Output:

Metric	Baseline	Optimised
Wall clock	4 min	200 ms (cache) / 3 s (miss)
Bytes scanned	200 TB	300 MB
Cost / query	$1,000	$0.02
Hourly cost	$1,000/hr	$0.02/hr
Weekly cost	$168,000/wk	$3.36/wk

Why this works — concept by concept:

• Partition pruning by narrowing the filter window — most dashboards actually only need the last 7 or 30 days. Narrowing from 90 days to 7 days is a mechanical 13× reduction in bytes scanned. Verify with --dry_run.
• Materialised view for pre-aggregation — dashboards summarise the raw data. Pre-summarise once, query many times. Automatic incremental refresh keeps the MV current. Bytes scanned per dashboard load drops from TB to MB.
• BI Engine cache — an in-memory cache for repeat queries. Sub-second latency; zero bytes scanned on cache hit. Perfect for dashboards where the same query runs every hour.
• INFORMATION_SCHEMA.JOBS for cost attribution — the metadata table lets you attribute per-team, per-project, and per-user costs. Weekly review of the top 20 queries by total_bytes_processed finds the biggest optimisation targets.
• Cost — Diagnosis: $0 (metadata queries). Fixes: MV creation is a one-off compute cost (~$100 for 200 TB); BI Engine adds a fixed monthly cost (~$100 for 20 GB). Ongoing: 50,000× cheaper. ROI in hours.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — window functions Window function and aggregation drills

Practice →

---

5. Dialect matrix + 5-step reading strategy

read execution plan across four engines — Postgres, Snowflake, BigQuery, SQL Server — one query, four artefacts, one senior reading strategy

The mental model in one line: the same conceptual query (join, filter, aggregate, sort, limit) compiles to a nested plan tree on Postgres, an operator stack on Snowflake, a stage DAG on BigQuery, and an XML plan graph on SQL Server; the artefacts look different but the reading discipline is the same — find the hot node, check the estimate-vs-actual, check for spill or shuffle, check parallelism, name the fix. Learn the five-step discipline once; apply it everywhere.

Slot 1 — the dialect matrix.

Engine	EXPLAIN keyword	Profile artefact	Cost surface
Postgres	EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT/JSON)	Nested plan tree with per-node cost / rows / actual / loops / buffers	pg_stat_statements, log_min_duration_statement
Snowflake	EXPLAIN (plan only) + Query Profile UI (actual)	Operator stack with pruning % + spill panel	QUERY_HISTORY, ACCOUNT_USAGE, GET_QUERY_OPERATOR_STATS
BigQuery	bq query --dry_run + Execution details tab	Stage DAG with slot_ms + shuffle_bytes	INFORMATION_SCHEMA.JOBS, dry_run cost estimator
SQL Server	SET STATISTICS TIME/IO ON + Actual Execution Plan (XML)	Graphical plan in SSMS + XML	Query Store, DMVs (sys.dm_exec_query_stats)

Slot 2 — SQL Server-specific plan reading.

• SET STATISTICS TIME ON — reports CPU and elapsed time per statement.
• SET STATISTICS IO ON — reports logical reads (from cache) and physical reads (from disk) per table.
• Actual Execution Plan (Ctrl+M in SSMS) — the graphical plan tree. Nodes are labelled with cost %, actual row count, and estimated row count.
• Estimated vs Actual Plan — Estimated is planning-only (no execution); Actual is post-execution. Prefer Actual for diagnosis.
• Plan XML — the underlying representation. Machine-readable; can be diffed programmatically.
• Query Store — SQL Server's built-in query performance history. Similar to pg_stat_statements but with plan diffs and forced-plan capability.
• DMVs — sys.dm_exec_query_stats, sys.dm_exec_query_plan, sys.dm_os_wait_stats. Real-time perf data.
• Missing Index DMVs — sys.dm_db_missing_index_details — SQL Server's suggestion engine. Use as a starting point, not a truth.
• Common operators. Index Seek, Clustered Index Scan, Hash Match, Nested Loops, Sort, Stream Aggregate, Table Spool.
• Cost estimate. Each operator has a "cost of subtree" %. The percentages sum to 100%; the operator with the highest % is the bottleneck.

Slot 3 — the same query across four engines.

-- Same conceptual query on all four engines
SELECT
  customer_id,
  SUM(total) AS lifetime_value
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Each engine shows this differently:

• Postgres — nested tree: Limit → Sort → HashAggregate → Bitmap Heap Scan on orders.
• Snowflake — operator stack: Result → Sort → Aggregate → Filter → TableScan(orders).
• BigQuery — 3-stage DAG: Stage 1 (read + filter) → Shuffle → Stage 2 (aggregate + sort) → Stage 3 (limit + output).
• SQL Server — XML plan tree with Limit → Sort → Hash Match (Aggregate) → Index Seek on orders(created_at).

Slot 4 — the 5-step senior read strategy (applies to all four engines).

• Step 1 — find the hot node. The one with the largest fraction of total wall clock (or slot_ms on BigQuery). Every profile UI sorts by this.
• Step 2 — check estimate vs actual. Every plan has an estimate and an actual row count per node. If they diverge by 10× or more, the optimiser was wrong — refresh stats or add extended stats.
• Step 3 — check spill or shuffle. Postgres Buffers: temp read. Snowflake bytes_spilled_to_remote_storage. BigQuery shuffle_bytes (large intermediate). SQL Server Sort Warning in the XML plan. Any of these is a specific fix.
• Step 4 — check parallelism. Postgres Workers Launched. Snowflake warehouse size. BigQuery slot utilisation vs wait time. SQL Server MAXDOP setting. If parallelism is disabled or unused, decide whether it should be.
• Step 5 — fix the plan. Add index, run ANALYZE, rewrite join, add hint, bump memory, cluster the table, pre-aggregate. The hot-node diagnosis names the one-line fix.

Slot 5 — hints and last-resort tools.

• Postgres — pg_hint_plan. An extension for query hints. /*+ SeqScan(t) */ forces seq scan. Use rarely — hints lock the plan against future data shifts.
• Snowflake — NO_BROADCAST, BROADCAST, MATERIALIZED_VIEW_HINT. Fewer hints than other engines; the optimiser is intentionally opaque.
• BigQuery — no hints per se. Rewrite the SQL to force a plan shape. Use materialised views or authorised views for repeated shapes.
• SQL Server — many hints. WITH (NOLOCK), OPTION (HASH JOIN), OPTION (MAXDOP 4). Powerful but easily abused.
• Rule. Hints are a last resort. First — refresh stats. Second — rewrite. Third — index. Fourth — hint (and add a comment explaining the hint's justification for the next maintainer).

Slot 6 — cost per query (bill-line thinking).

• Postgres self-hosted. Cost = servers × hours. Query cost = fraction of server-time. Diagnose to keep utilisation reasonable; scale up when needed.
• Postgres cloud (RDS, Aurora). Cost = instance-hours + IO. Bad queries can inflate IO — check pg_stat_statements and diff top-N by total time.
• Snowflake. Cost = warehouse-tier × query-wall-clock. Bad plans = long wall clock = big bill. Optimise for shorter wall clock at a given tier.
• BigQuery on-demand. Cost = TB scanned × $5. Bad plans = full-table scans = big bill. Optimise for pruned partitions and smart clustering.
• BigQuery flat-rate. Cost = slot commitment × month. Bad plans = queue waits or overrun. Optimise for slot efficiency (compute > wait).
• SQL Server. Cost = server + license. Query cost = share of server; long queries block others via lock contention. Diagnose lock waits with DMVs.

Slot 7 — reading discipline vs tuning discipline.

• Reading. Every plan artefact you can read is a chance to catch a regression before it ships. Add plan diffs to code review. Compare prod vs staging plans for the same query. Set alerts on pg_stat_statements for queries whose mean-time doubles.
• Tuning. Once the read names the fix, apply the fix. Prefer stats refresh (cheap) → query rewrite (durable) → index (durable, mild write cost) → materialised view (durable, storage cost) → warehouse bump (fast, expensive). Hints are last.

Slot 8 — the SQL Server actual execution plan XML example.

-- SQL Server: turn on both flags
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Include Actual Execution Plan (Ctrl+M) then run:
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY lifetime_value DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

-- Right-click the plan → Show Execution Plan XML → paste into a diff tool

-- Turn off after diagnosis
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Common beginner mistakes

• Reading only one engine's plan without recognising the other three shapes.
• Confusing Snowflake's operator stack (top-down) with Postgres's plan tree (bottom-up).
• Forgetting that BigQuery slot_ms ≠ wall clock — a 100-slot query is fast relative to slot time.
• Skipping the estimate-vs-actual check — the single most reliable signal of a bad plan.
• Bumping resources first — always diagnose the plan first; scale is the last resort.

Worked example — reading the same query on all four engines

Detailed explanation. The clearest way to lock in the mental model: read the same query's plan on Postgres, Snowflake, BigQuery, and SQL Server, and identify the equivalent nodes.

Question. Given the query and the plan artefacts from each engine, map each engine's nodes to the conceptual operators (Filter, GroupBy, Sort, Limit).

Input.

-- The query
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Code. (No code — a mapping exercise.)

Conceptual operator | Postgres node        | Snowflake operator | BigQuery stage | SQL Server node
Read                | Bitmap/Index Scan    | TableScan          | Stage 1 input  | Index Seek
Filter              | Recheck Cond / Filter | Filter / Scan     | Stage 1 filter | (subsumed)
GroupBy             | HashAggregate        | Aggregate          | Stage 2 agg    | Hash Match (Aggregate)
Sort                | Sort                 | Sort               | Stage 2 sort   | Sort
Limit               | Limit                | Result             | Stage 3 output | Top

Step-by-step explanation.

1. Read — every engine has a scan. Postgres shows the physical access method (Bitmap / Index / Seq); Snowflake shows partitions scanned; BigQuery shows records_read per stage; SQL Server shows the seek predicate.
2. Filter — subsumed into scan on most engines when the predicate is sargable (index-lookup key on Postgres / SQL Server; partition prune on BigQuery; pruning % on Snowflake).
3. GroupBy — always a hash aggregate for high-cardinality group keys. Postgres and SQL Server can also pick sort-based aggregation when input is already sorted.
4. Sort — the ORDER BY SUM(total) DESC requires sorting after aggregation. Postgres and SQL Server may pick top-N heapsort when combined with LIMIT.
5. Limit — the top node. Postgres Limit, Snowflake Result (subsumes limit), BigQuery output stage, SQL Server Top.

Output. The mapping in the table above. Learn one column deeply; the others come naturally once you can read one.

Rule of thumb. Read one engine deeply first (usually Postgres because the syntax is most explicit). Then translate to Snowflake, BigQuery, SQL Server as you encounter them. The five-step reading discipline is invariant across all four.

Worked example — the same query optimised differently on each engine

Detailed explanation. Optimising the same conceptual query on each engine involves engine-specific levers — Postgres wants indexes, Snowflake wants clustering, BigQuery wants partition + cluster keys, SQL Server wants an index seek plus a covering include column.

Question. For the query above, give the one-line optimisation for each engine.

Input.

-- The query
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE created_at >= '2026-06-10'
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 100;

Code.

-- Postgres
CREATE INDEX CONCURRENTLY idx_orders_created_customer_total
  ON orders (created_at, customer_id) INCLUDE (total);

-- Snowflake
ALTER TABLE orders CLUSTER BY (created_at);

-- BigQuery
CREATE OR REPLACE TABLE `project.dataset.orders`
PARTITION BY DATE(created_at)
CLUSTER BY customer_id
AS SELECT * FROM `project.dataset.orders`;

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_created_at
  ON orders (created_at)
  INCLUDE (customer_id, total);

Step-by-step explanation.

1. Postgres — composite index on (created_at, customer_id) with INCLUDE (total) covers the filter, the group key, and the aggregate — index-only scan possible.
2. Snowflake — cluster by created_at so the WHERE filter prunes micro-partitions. SELECTing only three columns already benefits from columnar storage.
3. BigQuery — partition by DATE(created_at) so the filter prunes date partitions. Cluster by customer_id so the group-by benefits from block-level locality.
4. SQL Server — nonclustered index on created_at with INCLUDE (customer_id, total) — covers the filter and projects the two needed columns.
5. All four fixes achieve the same shape: a small scan + hash aggregate + sort + limit. Wall clock drops from tens of seconds to sub-second.

Output.

Engine	Before	After	Speed-up
Postgres	Seq Scan / 1.2 s	Index Only Scan / 15 ms	80×
Snowflake	0% prune / 45 s	99% prune / 4 s	11×
BigQuery	Full-scan / 200 TB	Partition + cluster / 2 GB	100,000× cost
SQL Server	Clustered Index Scan / 2.5 s	Index Seek + Include / 30 ms	83×

Rule of thumb. Every engine has an equivalent "cover the query with the index" pattern. Learn the syntax for each; the concept is the same.

execution plan analysis interview question on cross-engine plan reading

A senior interviewer often asks: "Design a runbook a mid-level engineer can follow to read execution plans across Postgres, Snowflake, BigQuery, and SQL Server. What are the five steps, and what's the one-line signal on each engine that tells you 'this plan is broken'?"

Solution Using the invariant 5-step read strategy plus a per-engine cheat card

STEP 1 — find the hot node/stage.
  Postgres:  sort plan by actual_time; deepest actual_time × loops = hot node.
  Snowflake: sort by execution_time_breakdown_overall_percentage; top card = hot.
  BigQuery:  sort by slot_ms; largest stage = hot.
  SQL Server: sort by cost of subtree %; highest = hot.

STEP 2 — check estimate vs actual.
  Postgres:  rows= vs actual rows= per node. Gap ≥ 10× is bad.
  Snowflake: input rows vs output rows on operators. Compare to statistics.
  BigQuery:  records_read vs estimated records. INFO_SCHEMA.JOBS has both.
  SQL Server: Estimated Number of Rows vs Actual Number of Rows on each node.

STEP 3 — check spill or shuffle.
  Postgres:  BUFFERS: temp read non-zero; Sort Method: external merge.
  Snowflake: bytes_spilled_to_remote_storage non-zero.
  BigQuery:  large shuffle_output_bytes between stages.
  SQL Server: Sort Warning; Hash Warning; TempDB usage.

STEP 4 — check parallelism.
  Postgres:  Workers Launched. Should be > 0 on big scans.
  Snowflake: warehouse size and MULTI_STATEMENT_COUNT.
  BigQuery:  slot count and wait_ms.
  SQL Server: MAXDOP and Parallelism operator in the plan.

STEP 5 — fix the plan.
  Postgres:  ANALYZE; add index; rewrite join; SET work_mem session.
  Snowflake: CLUSTER BY; rewrite filter; bump warehouse tier.
  BigQuery:  partition + cluster; materialised view; rewrite SELECT.
  SQL Server: rebuild statistics; CREATE INDEX; Query Store forced plan.

BROKEN-PLAN one-line signals (per engine):
  Postgres:  "Rows Removed by Filter: 99000000" — missing index.
  Snowflake: "Partitions scanned = Partitions total" — no clustering / bad filter.
  BigQuery:  "Full scan on partitioned table" — filter not sargable.
  SQL Server: "Clustered Index Scan on 100M-row table with cost 98%" — missing index.

Step-by-step trace.

Step	Postgres	Snowflake	BigQuery	SQL Server
1 hot	actual_time × loops	exec time %	slot_ms per stage	cost of subtree %
2 est vs act	rows vs actual rows	input vs output rows	records_read	Est vs Actual rows
3 spill	temp read	remote spill bytes	large shuffle	Sort/Hash warning
4 parallel	Workers Launched	WH size	slot count vs wait	MAXDOP + parallel op
5 fix	ANALYZE + INDEX	CLUSTER + rewrite	PARTITION + MV	REBUILD + INDEX

The runbook is invariant across all four engines. The signals differ; the discipline is the same.

Output:

Engine	Fastest one-signal diagnosis
Postgres	Any Seq Scan with Rows Removed by Filter > 1M
Snowflake	Any TableScan with pruning < 50% on a filter column
BigQuery	Any query where dry_run bytes > 10 TB on a partitioned table
SQL Server	Any plan node with cost of subtree > 80% and no index seek

Why this works — concept by concept:

• Invariant discipline — every plan diagnosis reduces to five steps: hot node, estimate vs actual, spill/shuffle, parallelism, fix. The signals differ per engine but the mental model is one.
• One-signal diagnosis — for every engine, there's a single reading that catches the most common plan pathology. Teaching a mid-level engineer that signal gives them 80% of senior plan-reading skill for that engine.
• Runbook portability — an engineer who learns the runbook on Postgres can port it to Snowflake or BigQuery in a day. The mental model is the durable asset; syntax is trivia.
• Escalation path — cheap fixes first (refresh stats), durable fixes second (index, cluster, partition), expensive fixes last (scale up, hint, materialise). Applies universally.
• Cost — the runbook itself is a one-page cheat sheet; teaching it takes an hour. Ongoing cost is engineer-time saved on diagnosis (typically 30–60 min per incident reduced to 5).

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL
Topic — joins
SQL join optimisation drills

Practice →

---

Cheat sheet — plan-reading recipe list

• Bottom-up reading rule. On Postgres and SQL Server, always read the plan tree bottom-up — leaf scans first, Limit last. On Snowflake and BigQuery, sort operators or stages by execution time % / slot_ms and read the top.
• Cost vs actual time. cost= is planner units (Postgres, SQL Server), not milliseconds. actual time= is wall clock (ms). Never quote cost to non-DBA humans.
• The four numbers per Postgres node. cost (startup..total), rows (estimate), actual time (first..last), loops. Wall-clock cost = actual time × loops.
• Estimate vs actual gap. Any node with rows off by 10×+ from actual rows is a bad-stats or bad-plan signal. Fix — ANALYZE, add extended statistics, or force the plan.
• Rows Removed by Filter. Any Postgres Seq Scan with Rows Removed by Filter in the millions is a missing-index diagnosis. Add the index.
• BUFFERS: temp read. Non-zero on Postgres = spilling. Fix — rewrite (LIMIT, pre-aggregate) or SET work_mem per session.
• Sort Method. Postgres — quicksort (in-mem), top-N heapsort (bounded), external merge (spilled). external merge = spill = fix.
• Batches: N > 1. Postgres Hash node with N > 1 = spilling. Fix — bump work_mem or filter build side down.
• Workers Launched: 0. Postgres parallelism disabled. Check max_parallel_workers_per_gather and min_parallel_table_scan_size.
• Nested Loop on two large sides. Bad plan almost always. Fix — refresh stats or force Hash Join (last resort).
• Snowflake pruning %. The single most important number. 99% = great. Under 50% = fix the filter or CLUSTER BY.
• Snowflake remote spill. Non-zero bytes_spilled_to_remote_storage = warehouse too small OR bad plan. Bump one tier or rewrite.
• Snowflake local spill. Non-zero bytes_spilled_to_local_storage but zero remote = borderline. Not urgent.
• Snowflake SELECT *. Costs more than SELECT a, b, c — columnar storage. Project only needed columns.
• Snowflake broadcast join. Fine for < 10 K rows on the build side. Over ~1 M, force NO_BROADCAST or filter first.
• BigQuery slot_ms. Fundamental compute cost unit. Sort stages by slot_ms desc to find the hot stage.
• BigQuery --dry_run. Free bytes-processed estimate. Always run before expensive queries. Gate PRs in CI on estimated bytes.
• BigQuery partition pruning. Filter on the raw partition column with >= / BETWEEN. Never wrap in a function (EXTRACT, DATE).
• BigQuery clustering. Cluster on high-cardinality filter columns after partition. Block-level pruning inside each partition.
• BigQuery materialised view. Pre-aggregate for dashboards. Automatic incremental refresh. Bytes scanned drops 1000×+.
• BigQuery on-demand vs flat-rate. On-demand ($5/TB) for spiky ad-hoc. Flat-rate (per-slot commitment) for consistent workloads.
• SQL Server SET STATISTICS TIME/IO ON. Reports CPU time, elapsed time, logical reads, physical reads. Turn on for diagnosis, off after.
• SQL Server actual execution plan. Ctrl+M in SSMS. Cost of subtree % on every node sums to 100%; hottest node is the fix target.
• SQL Server Query Store. Historical query perf + plan diffs. Forced plans available.
• The 5-step senior read strategy. Hot node → estimate vs actual → spill/shuffle → parallelism → fix. Invariant across all four engines.
• Refresh stats first, always. ANALYZE on Postgres. Snowflake refreshes automatically. BigQuery via INFORMATION_SCHEMA. SQL Server UPDATE STATISTICS. Fresh stats fix ~30% of bad plans immediately.
• Rewrite before scaling. Query rewrite is free forever. Scaling is a monthly cost. Prefer rewrite when feasible.
• Hints are last resort. Every hint locks a plan against future data shifts. Prefer index / cluster / partition changes to hints when possible.
• Cost per query. Postgres self-hosted = server share. Snowflake = warehouse × wall clock. BigQuery on-demand = TB × $5. Flat-rate = monthly commit. Know your cost model when reading plans.
• Plan diffs in code review. Add EXPLAIN (ANALYZE, BUFFERS) outputs to PR descriptions for schema or query changes. Catches regressions before ship.
• CI cost gates. Wrap expensive queries with --dry_run in CI. Fail PR if estimated bytes exceed a threshold.
• Monitoring stack. Postgres pg_stat_statements. Snowflake QUERY_HISTORY. BigQuery INFORMATION_SCHEMA.JOBS. SQL Server Query Store. Weekly review of top-N by mean time or total bytes.
• Rule of thumb. 80% of bad plans come from 20% of queries. Diagnose the top 20 by cost weekly; that's usually where the leverage is.

Frequently asked questions

What is SQL EXPLAIN ANALYZE and how do I read the output?

sql explain analyze is the primitive every engine ships for surfacing the physical plan the executor actually walked — on Postgres it's EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) and it returns a nested plan tree annotated with cost=startup..total (planner units), rows=N (estimate), actual time=first..last (wall clock ms), loops=N (executions), and optional BUFFERS: shared hit / shared read / temp read (memory vs disk vs spill). Read it bottom-up — the leaf scans execute first and feed intermediate nodes (joins, aggregates), which feed the top node (Limit, Sort); the leaf whose actual time × loops dominates total wall clock is the bottleneck. The reading discipline is five steps — find the hot node, compare estimate to actual (10×+ gap = stale stats or bad plan), check for spill (Buffers: temp read non-zero, Sort Method: external merge), check parallelism (Workers Launched > 0), and name the fix (add index, run ANALYZE, rewrite join, bump work_mem). On Snowflake, the equivalent is the Snowsight Query Profile with operator cards and pruning %; on BigQuery, it's the execution graph with per-stage slot_ms; on SQL Server, it's the actual execution plan XML with cost-of-subtree percentages. The read is invariant across engines; only the artefact differs.

What's the difference between Postgres cost and actual time?

Cost is a made-up planner unit used only to rank plan alternatives during optimisation — a Seq Scan might cost 0.00..25000.00, but that does not mean 25 seconds; it means "the CBO's internal price for this scan compared to alternatives." Actual time is wall clock in milliseconds — actual time=42.512..42.548 means the first row from this node was ready 42.512 ms after query start; the last was ready at 42.548 ms. The two are not comparable — cost is arbitrary and only meaningful within a single plan; actual time is real and directly interpretable. On top of that, if a node has loops=1000 it executed a thousand times, so the wall-clock cost of the node is actual time × loops, not just actual time. When you're diagnosing a slow query, ignore cost entirely and read actual time × loops per node; when you're explaining why the optimiser chose plan A over plan B, cost is the axis that matters. Never quote cost to a business stakeholder — "this query cost 250,000" means nothing to them; "this query took 42 seconds" means everything.

How do I read a Snowflake query profile?

Open Snowsight → Activity → Query History → click into the query → Profile tab — the graphical view shows a stack of operator cards (TableScan, Filter, Aggregate, Join, Sort, WindowFunction), each with Execution time %, Bytes scanned, Rows produced, and per-operator statistics. Read top-down, sorted by execution time %, and the hot operator surfaces first. On the hot card — check pruning % for TableScans (99% is great, under 50% is a clustering or filter problem), check local vs remote disk spill for aggregates and sorts (bytes_spilled_to_remote_storage non-zero is the strongest single signal that the warehouse is too small), and check join algorithm for joins (Broadcast for genuinely small dim tables, hash / shuffle for large tables). The underlying data lives in SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY (near-real-time, 7-day retention) and SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (delayed but 1-year retention), with per-operator stats via GET_QUERY_OPERATOR_STATS(query_id). The five-step reading discipline is identical to other engines — hot operator, estimate vs actual (input vs output rows), spill (remote > local > none), parallelism (warehouse size), name the fix (CLUSTER BY, rewrite filter, bump warehouse, force NO_BROADCAST hint).

How do I read a BigQuery execution plan?

Open BigQuery Console → Query History → click the query → Execution details tab — the graphical view shows a stage-based DAG where each stage is a set of parallel workers reading input, applying part of the query, and shuffling output to the next stage. The fundamental compute unit is slot_ms (slot-milliseconds); a stage using 100 slots for 4.2 s costs 420 000 slot_ms with only 4.2 s of wall clock. Read stages sorted by slot_ms desc — the top stage is the bottleneck — and inspect records_read (partition pruning worked if this is a small fraction of the table), shuffle_output_bytes (large shuffle = big intermediate = optimise upstream), wait_ms vs compute_ms (wait > compute = slots queued; compute > wait = flat out), and skew warnings (one hot key monopolising a worker). The underlying data is INFORMATION_SCHEMA.JOBS with total_slot_ms, total_bytes_processed, and job_stages (nested per-stage details). Use bq query --dry_run to estimate bytes_processed before running — free, matches actual within ~5%, and pairs with CI gates to reject expensive PRs. The senior fix set is partition on the filter column (PARTITION BY DATE(created_at)), cluster on the next-most-common filter (CLUSTER BY user_id), create materialised views for dashboards (1000× cost drop typical), and choose on-demand vs flat-rate based on utilisation patterns.

What does "rows removed by filter" mean in Postgres?

Rows Removed by Filter: N on a Postgres plan node means the executor read N rows and threw them away — the filter predicate matched some fraction of the input, and the discarded rows are the loss. On a Seq Scan with Rows Removed by Filter: 99000000, the executor read 100 M rows and kept only 1 M — a scan that should have been an index seek. On an Index Scan with non-zero Rows Removed by Index Recheck, the index covered part of the predicate (say, the leading column) but a remaining condition (say, a trailing column not in the index) had to be re-checked at the heap; extending the index to a covering INCLUDE index eliminates the recheck. On a Bitmap Heap Scan with Rows Removed by Filter: N, the index bitmap was over-inclusive — the index isn't selective enough on its own; a partial index or a composite index that covers more of the predicate reduces the recheck. The general fix pattern is: any node with Rows Removed by Filter in the millions is a missing index or weak index diagnosis — add or extend an index so the filter becomes an Index Cond (part of the seek) instead of a Filter (applied after the read).

How do I fix a plan where the estimate is 10× off the actual?

An estimate that's 10×+ off the actual usually means one of five things — stale statistics (last ANALYZE was days ago and data has shifted; run ANALYZE table to refresh), skewed columns without extended stats (a column with a highly-skewed distribution fools the histogram; create CREATE STATISTICS s ON col FROM t; and run ANALYZE), correlated columns treated as independent (say, city and country are correlated but the planner multiplies their selectivities as if independent; CREATE STATISTICS s (dependencies, ndistinct) ON city, country FROM t; fixes it), function-wrapped predicates (WHERE EXTRACT(YEAR FROM created_at) = 2026 is not sargable; rewrite as a range WHERE created_at BETWEEN '2026-01-01' AND '2027-01-01'), or plan cache mode mismatch (a prepared statement was planned with generic parameters that don't match this call's actual values; force plan_cache_mode = 'force_custom_plan' for this session). The fastest triage — always try ANALYZE first — it's free, fast, and fixes ~30% of estimate divergences on its own. If ANALYZE doesn't help, look at the specific columns and predicates; extended statistics and predicate rewrites cover the remaining ~50%. As a last resort, force the plan via pg_hint_plan or its equivalents on other engines — but hints lock the plan against future data shifts, so always add a comment explaining why and revisit periodically.

Practice on PipeCode

• Drill the SQL optimization practice library → for EXPLAIN ANALYZE reading, plan-tree diffs, Rows Removed by Filter diagnostics, and one-line index or rewrite fixes.
• Sharpen the composite-index reflex with SQL indexing drills → — covering indexes, INCLUDE columns, partial indexes, index-only scans, and cluster keys.
• Layer the join-optimisation surface with SQL join drills → — hash vs merge vs nested loop, broadcast vs shuffle joins on warehouses, and skew mitigation.
• Push the SQL join-language depth with join drills on SQL → — semi-joins, anti-joins, lateral joins, and self-joins that show up in plan-reading interview questions.
• Warm up with aggregation practice → — hash aggregate vs group aggregate, pre-aggregation patterns, and materialised-view design.
• Layer window function drills → — ROW_NUMBER() / RANK() / LAG() plans, partition-key alignment with clustering, and window-frame reading.
• Sharpen the general SQL surface with the SQL practice library → — 450+ DE-focused questions covering plan reading, index design, EXPLAIN ANALYZE, and every adjacent pattern.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql explain analyze` recipe above ships with hands-on practice rooms where you type `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` on a live Postgres, read the plan tree bottom-up, spot the missing index from `Rows Removed by Filter`, migrate the same query to a Snowflake profile with pruning % and remote spill diagnosis, translate it into a BigQuery stage graph with `slot_ms` and `--dry_run` cost analysis, and finally read the SQL Server actual execution plan with `SET STATISTICS TIME/IO ON` — the exact four-engine `read execution plan` fluency that senior DE interviews probe. PipeCode pairs every plan-reading concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your plan-diagnosis answer holds up under a senior interviewer's depth probes.

Practice plan reading now →
Indexing drills →