TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 19

Reading Query Plans: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What does EXPLAIN show vs EXPLAIN ANALYZE?
  2. Q2Does EXPLAIN (without ANALYZE) run the query?
  3. Q3Does EXPLAIN ANALYZE actually execute the query? Caution for writes?
  4. Q4What is a Seq Scan and when is it chosen?
  5. Q5What is an Index Scan and when is it chosen?
  6. Q6What is a Bitmap Index Scan / Bitmap Heap Scan (rough idea)?
  7. Q7In "cost=0.00..431.00", what do the two numbers mean?
  8. Q8What's the difference between estimated rows and actual rows?
  9. Q9Why is a big gap between estimated and actual rows a warning sign?
  10. Q10What does "rows" vs "loops" mean in EXPLAIN ANALYZE?
  11. Q11What is a Nested Loop join (intuition)?
  12. Q12What is a Hash Join (intuition)?
  13. Q13What is a Merge Join (intuition)?
  14. Q14Why is "SELECT * on a wide table" often wasteful?
  15. Q15Why does LIKE '%abc' (leading wildcard) defeat a B-tree index?
  16. Q16Why does WHERE UPPER(email) = '...' prevent index use?
  17. Q17Why add LIMIT to exploratory queries?
  18. Q18What does "actual time=...rows=..." tell you about a node?
  19. Q19What does ANALYZE (the maintenance command) do, vs EXPLAIN ANALYZE?
  20. Q20What does EXPLAIN (BUFFERS) add?
  21. Q21What does EXPLAIN (FORMAT JSON) give you?
  22. Q22Why read a plan bottom-up (leaves first)?
  23. Q23What is the "actual rows" you multiply by loops to get total work?
  24. Q24Why might the planner pick a Seq Scan even when an index exists?
  25. Q25Name two analyst query smells you can spot from a plan alone.

RUN EXPLAIN

  1. Q26EXPLAIN a SELECT of all orders for one cust_id.
  2. Q27EXPLAIN a SELECT of orders in a date range.
  3. Q28EXPLAIN a COUNT(*) of sales.orders.
  4. Q29EXPLAIN a join of orders to customers on cust_id.
  5. Q30EXPLAIN ANALYZE the same join; note actual time.
  6. Q31EXPLAIN a query filtering products by price > 5000.
  7. Q32EXPLAIN a query with ORDER BY net_total DESC LIMIT 10.
  8. Q33EXPLAIN a GROUP BY cust_id SUM(net_total).
  9. Q34EXPLAIN ANALYZE a WHERE email LIKE 'a%' on customers.
  10. Q35EXPLAIN ANALYZE a WHERE email LIKE '%gmail.com' (leading wildcard).
  11. Q36EXPLAIN a WHERE UPPER(email) = '[email protected]'.
  12. Q37EXPLAIN a 3-table join (orders->order_items->products).
  13. Q38EXPLAIN (ANALYZE, BUFFERS) a customer aggregate.
  14. Q39EXPLAIN a SELECT * FROM web_events.page_views LIMIT 100.
  15. Q40EXPLAIN ANALYZE a query with no LIMIT on page_views (then add LIMIT).
  16. Q41EXPLAIN a query with an OR across two columns.
  17. Q42EXPLAIN a query with IN (subquery).
  18. Q43EXPLAIN a query with EXISTS (subquery).
  19. Q44EXPLAIN a DISTINCT on order_status.
  20. Q45EXPLAIN ANALYZE a HAVING COUNT(*) > 5 grouping.
  21. Q46EXPLAIN a self-join-free aggregate vs a correlated subquery version.
  22. Q47EXPLAIN a LEFT JOIN anti-join (IS NULL).
  23. Q48EXPLAIN (FORMAT JSON) a simple filter query.
  24. Q49EXPLAIN a query ordering by a non-indexed expression.
  25. Q50EXPLAIN ANALYZE a query and read off estimated vs actual rows.

READ THE PLAN

  1. Q51From a plan, state whether a Seq Scan or Index Scan was used and why.
  2. Q52Identify the join algorithm in a 2-table join plan.
  3. Q53Find the most expensive node (highest cost) in a plan.
  4. Q54Find the node with the largest actual-time in EXPLAIN ANALYZE.
  5. Q55Identify a large estimate-vs-actual row mismatch in a plan.
  6. Q56Read off total estimated cost of a query.
  7. Q57Identify whether a Sort spilled to disk (external merge) from the plan.
  8. Q58Find "Rows Removed by Filter" and explain what it means.
  9. Q59Identify a Nested Loop with a high loop count.
  10. Q60Read "Heap Fetches" in an Index Only Scan.
  11. Q61Determine if the query used the LIMIT to stop early (plan shows it).
  12. Q62Identify a Bitmap Heap Scan + Bitmap Index Scan pair.
  13. Q63From BUFFERS, tell cache hits from disk reads.
  14. Q64Identify a Gather/parallel node in a plan.
  15. Q65Explain what "loops=N" multiplies in a Nested Loop inner side.
  16. Q66Identify the driving (outer) vs inner table in a Nested Loop.
  17. Q67Read off the hash table memory usage in a Hash node.
  18. Q68Spot a Materialize node and explain why it appeared.
  19. Q69Tell whether ORDER BY was satisfied by an index or a Sort node.
  20. Q70Identify aggregate node type (HashAggregate vs GroupAggregate).
  21. Q71Determine if a subplan/SubPlan appears and what it represents.
  22. Q72Read planning time vs execution time in EXPLAIN ANALYZE.
  23. Q73Spot a Seq Scan on a big table that should have been filtered earlier.
  24. Q74Compare two plans and say which is cheaper and why.
  25. Q75Summarize a plan in one sentence ("scan -> filter -> sort -> limit").

SPOT THE SMELL (no index changes)

  1. Q76Rewrite WHERE UPPER(email) = '[email protected]' to a sargable form (lower on literal side or LIKE).
  2. Q77Rewrite WHERE email LIKE '%@gmail.com' - why it can't use a plain B-tree; alternative.
  3. Q78Replace SELECT * with only the needed columns in a wide-table query.
  4. Q79Add a missing LIMIT to an exploration query on page_views.
  5. Q80Rewrite WHERE EXTRACT(YEAR FROM order_date)=2025 to a sargable range.
  6. Q81Rewrite OR across two columns as UNION ALL (and EXPLAIN both).
  7. Q82Rewrite WHERE order_date::text LIKE '2025%' to a proper date range.
  8. Q83Replace a correlated subquery with a JOIN and compare plans.
  9. Q84Rewrite NOT IN (subquery with NULLs) as NOT EXISTS and compare.
  10. Q85Rewrite WHERE price + 0 > 5000 to WHERE price > 5000 (drop the no-op math).
  11. Q86Rewrite WHERE COALESCE(phone,'') = '' to an IS NULL/empty form.
  12. Q87Show why SELECT DISTINCT to dedupe a join is a smell; suggest the fix.
  13. Q88Rewrite a function-on-column ORDER BY into a plain-column ORDER BY where possible.
  14. Q89Spot a cartesian product (missing join condition) in a plan.
  15. Q90Rewrite WHERE substring(email,1,1)='a' to LIKE 'a%'.
  16. Q91Identify SELECT * feeding a small LIMIT and trim the columns.
  17. Q92Rewrite a leading-wildcard search to a prefix search where business allows.
  18. Q93Spot an unfiltered join on a huge table and add the missing WHERE.
  19. Q94Rewrite WHERE date_trunc('day',order_date)=DATE '2025-03-01' to a range.
  20. Q95Replace an IN (long literal list) with a VALUES join and compare.
  21. Q96Identify a missing LIMIT causing a full sort; add it.
  22. Q97Rewrite WHERE cast(cust_id AS text)='123' to WHERE cust_id=123.
  23. Q98Spot redundant ORDER BY in a subquery and remove it.
  24. Q99Rewrite an aggregate-then-filter that should be a WHERE-then-aggregate.
  25. Q100Given a slow plan, list 3 analyst-level rewrites (no index changes) to try first.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1How do you decide a plan is "bad" from EXPLAIN ANALYZE?
  2. Q2Why does a wrong row estimate cascade into a bad join choice?
  3. Q3When does the planner prefer Hash Join over Nested Loop?
  4. Q4When is a Merge Join chosen, and what does it need (sorted inputs)?
  5. Q5What makes a Sort spill to disk, and how does the plan show it?
  6. Q6What does "Batches > 1" in a Hash node mean?
  7. Q7Why does a leading-wildcard LIKE force a Seq Scan?
  8. Q8Why is function-on-column non-sargable; give the general fix.
  9. Q9What is "Rows Removed by Filter" telling you about selectivity?
  10. Q10How does LIMIT change the chosen plan (early stop)?
  11. Q11Why can OR across columns be slower than UNION ALL?
  12. Q12Why does SELECT * prevent an Index Only Scan?
  13. Q13What does a high loop count on a Nested Loop inner side imply?
  14. Q14Estimated vs actual: which side (over/under) causes nested-loop blowups?
  15. Q15Why might ANALYZE (stats refresh) fix a bad plan with no query change?
  16. Q16How do BUFFERS hits vs reads indicate caching behavior?
  17. Q17What is a Bitmap scan's sweet spot (selectivity range)?
  18. Q18Why is "SELECT DISTINCT" to fix a fan-out join a smell?
  19. Q19How does a correlated subquery appear in a plan (SubPlan/loops)?
  20. Q20Why prefer EXISTS over IN when the subquery may return NULLs?
  21. Q21What does GroupAggregate vs HashAggregate tell you about sorting?
  22. Q22How does parallelism (Gather) show up and when does it help?
  23. Q23Why is planning time relevant for very simple, frequently-run queries?
  24. Q24How to compare two plans fairly (same warm cache, EXPLAIN ANALYZE)?
  25. Q25What 3 things do you check first on any slow analyst query?

DIAGNOSE THE BOTTLENECK

  1. Q26EXPLAIN ANALYZE a customer-lifetime-spend aggregate; name the slow node.
  2. Q27EXPLAIN ANALYZE the orders->customers join; Hash or Nested Loop?
  3. Q28EXPLAIN ANALYZE a top-10 orders by net_total; did LIMIT help?
  4. Q29EXPLAIN ANALYZE WHERE email LIKE '%gmail.com'; identify the Seq Scan.
  5. Q30EXPLAIN ANALYZE a 3-table join; find the dominant node.
  6. Q31EXPLAIN ANALYZE a GROUP BY with HAVING; HashAggregate or GroupAggregate?
  7. Q32EXPLAIN ANALYZE an ORDER BY on a non-indexed column; did Sort spill?
  8. Q33EXPLAIN ANALYZE a correlated subquery over orders; note loop count.
  9. Q34EXPLAIN ANALYZE the equivalent JOIN; compare to Q33.
  10. Q35EXPLAIN ANALYZE a query with a big estimate/actual mismatch; quantify it.
  11. Q36EXPLAIN (ANALYZE,BUFFERS) a page_views scan; reads vs hits.
  12. Q37EXPLAIN ANALYZE a DISTINCT over a fan-out join; spot the inflation.
  13. Q38EXPLAIN ANALYZE a date-range filter; Index/Bitmap/Seq?
  14. Q39EXPLAIN ANALYZE a NOT IN with a nullable subquery; behavior.
  15. Q40EXPLAIN ANALYZE a self-join-free vs correlated "above-avg" query.
  16. Q41EXPLAIN ANALYZE a query joining reviews->products->brand; bottleneck.
  17. Q42EXPLAIN ANALYZE a wide SELECT * on orders vs trimmed columns.
  18. Q43EXPLAIN ANALYZE an OR-across-columns filter; note the scan.
  19. Q44EXPLAIN ANALYZE the UNION ALL rewrite of Q43; compare.
  20. Q45EXPLAIN ANALYZE a HAVING-without-WHERE that should pre-filter.
  21. Q46EXPLAIN ANALYZE a GROUP BY high-cardinality column; memory use.
  22. Q47EXPLAIN ANALYZE a join missing a condition (accidental cross join).
  23. Q48EXPLAIN ANALYZE a query with function-on-column in WHERE.
  24. Q49EXPLAIN ANALYZE the sargable rewrite of Q48; compare.
  25. Q50EXPLAIN ANALYZE a paginated OFFSET 100000 LIMIT 50; cost of deep offset.

REWRITE FOR SARGABILITY

  1. Q51Rewrite WHERE UPPER(email)=... to a sargable predicate.
  2. Q52Rewrite WHERE EXTRACT(YEAR FROM order_date)=2025 to a half-open range.
  3. Q53Rewrite WHERE date_trunc('month',order_date)=DATE '2025-03-01' to a range.
  4. Q54Rewrite WHERE order_date::text LIKE '2025-03%' to a range.
  5. Q55Rewrite WHERE cast(cust_id AS text)='123' to integer equality.
  6. Q56Rewrite WHERE price*1.0 > 5000 to drop the no-op cast.
  7. Q57Rewrite WHERE substring(email FROM 1 FOR 1)='a' to LIKE 'a%'.
  8. Q58Rewrite WHERE COALESCE(net_total,0) > 0 to a sargable form.
  9. Q59Rewrite OR (status='A' OR status='B') to IN and compare plans.
  10. Q60Rewrite OR across two different columns as UNION ALL.
  11. Q61Rewrite NOT IN (nullable subquery) to NOT EXISTS.
  12. Q62Rewrite a correlated "above brand average" subquery as a JOIN to a grouped CTE.
  13. Q63Rewrite SELECT * + LIMIT to a trimmed column list.
  14. Q64Rewrite a leading-wildcard contains-search to a prefix search.
  15. Q65Rewrite WHERE age(now(),registration_date) > INTERVAL '1 year' to a date compare.
  16. Q66Rewrite WHERE lower(city)='mumbai' to compare with the literal lowered.
  17. Q67Rewrite IN (huge literal list) as a JOIN to a VALUES table.
  18. Q68Rewrite a self-join "previous order" into LAG (Topic 18) and compare plans.
  19. Q69Rewrite WHERE net_total/quantity > 100 to avoid per-row division where possible.
  20. Q70Rewrite a HAVING filter that belongs in WHERE (pre-aggregation).
  21. Q71Rewrite DISTINCT-to-dedup-join using EXISTS instead.
  22. Q72Rewrite WHERE order_date BETWEEN x AND y (inclusive) to half-open.
  23. Q73Rewrite an ORDER BY random() sample to TABLESAMPLE.
  24. Q74Rewrite a deep OFFSET pagination to keyset (Topic 5).
  25. Q75Rewrite WHERE concat(first_name,last_name) ILIKE '%x%' to per-column predicates.

JOIN & SORT TUNING (read/rewrite only)

  1. Q76Compare plans of correlated-subquery vs JOIN for "customers above avg spend".
  2. Q77Identify which table drives a Nested Loop and whether that's sensible.
  3. Q78Pre-aggregate one side of a join in a CTE to cut fan-out; compare plans.
  4. Q79Show how filtering before joining changes the plan (push WHERE down).
  5. Q80Compare INNER JOIN vs EXISTS for "orders that have items".
  6. Q81Compare LEFT JOIN ... IS NULL vs NOT EXISTS for anti-join.
  7. Q82Identify a Sort that exists only to satisfy DISTINCT; rethink.
  8. Q83Compare GROUP BY then JOIN vs JOIN then GROUP BY for an aggregate report.
  9. Q84Show a Hash Join spilling (Batches>1) on a big aggregate; note it.
  10. Q85Compare a 3-table join in two join orders (rewrite FROM order).
  11. Q86Identify Materialize on the inner side and explain its role.
  12. Q87Replace a redundant ORDER BY inside a subquery; show plan change.
  13. Q88Compare DISTINCT vs GROUP BY for deduplication plans.
  14. Q89Show how adding LIMIT lets a Nested Loop short-circuit.
  15. Q90Compare UNION vs UNION ALL plans (dedupe cost).
  16. Q91Identify an accidental cross join and add the join key.
  17. Q92Compare IN (subquery) vs JOIN for "orders by top customers".
  18. Q93Show the plan effect of selecting fewer columns (Index Only Scan eligibility).
  19. Q94Compare correlated EXISTS vs semi-join rewrite plans.
  20. Q95Identify a GroupAggregate forced by ORDER BY; could HashAggregate work?
  21. Q96Compare a window-function top-N (Topic 16) vs correlated top-N plans.
  22. Q97Show how a CTE materialization barrier changes a plan (MATERIALIZED hint).
  23. Q98Compare aggregating in SQL vs returning rows for the BI tool (row counts).
  24. Q99Identify the single most expensive node across a 4-table report and propose a rewrite.
  25. Q100Given a slow 4-table analyst report, produce a prioritized rewrite plan (no indexes).

Interview grade, edge cases

CONCEPTUAL

  1. Q1Walk a methodical process for diagnosing a slow query from EXPLAIN ANALYZE.
  2. Q2How do estimate errors propagate and cause Nested Loop blowups?
  3. Q3BUFFERS: shared hit/read/dirtied - what each implies for I/O.
  4. Q4When is a Seq Scan actually optimal (full aggregate, low selectivity)?
  5. Q5How do you tell a Sort or Hash spilled to disk, and the work_mem link?
  6. Q6Why is "actual rows x loops" the true work of an inner node?
  7. Q7How does a missing/stale ANALYZE produce a bad plan?
  8. Q8Identify a query where parallelism helps vs where it doesn't.
  9. Q9Why can adding LIMIT dramatically change the chosen plan?
  10. Q10How to compare two plans fairly (cache warmth, ANALYZE, repetition).
  11. Q11What plan signs suggest "an index would help here"?
  12. Q12What plan signs suggest indexing will NOT help?
  13. Q13How does selectivity decide Index vs Bitmap vs Seq?
  14. Q14Why does SELECT * block Index Only Scans?
  15. Q15How does join order interact with the planner's reordering limit?
  16. Q16What is a "row estimate of 1" trap that triggers Nested Loops?
  17. Q17Why might a CTE act as an optimization fence (and PG12+ inlining)?
  18. Q18How do you read NestedLoop + Materialize together?
  19. Q19Why are leading-wildcard and function-on-column the top analyst smells?
  20. Q20When is HashAggregate forced to spill, and the symptom in the plan?
  21. Q21How to spot an accidental cross join from cost/row explosion.
  22. Q22Why prefer rewriting before indexing for analyst ad-hoc queries?
  23. Q23How does EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) aid tooling?
  24. Q24What is plan regression and how do you catch it?
  25. Q25Build a one-paragraph "tuning checklist" for analysts.

PLAN FORENSICS

  1. Q26SCENARIO: A customer-360 report is slow - EXPLAIN ANALYZE it and name the top-3 costly nodes.
  2. Q27Find the node with the worst estimate/actual ratio in a 4-table report.
  3. Q28From BUFFERS, decide if a query is I/O-bound or CPU-bound.
  4. Q29Detect a Sort spilling to disk on a big ORDER BY; quantify.
  5. Q30Detect a Hash Join with Batches>1; what would reduce it (no index)?
  6. Q31Identify a Nested Loop with loops in the millions and explain the cause.
  7. Q32Quantify "Rows Removed by Filter" and compute filter selectivity.
  8. Q33Identify whether ORDER BY used an index or a Sort node.
  9. Q34Find the planning-time vs execution-time split for a trivial query run often.
  10. Q35Detect a Bitmap Heap Scan with high "exact heap blocks" vs "lossy".
  11. Q36Identify a parallel plan and whether workers were actually launched.
  12. Q37Detect a Materialize node and decide if it helped.
  13. Q38From a JSON plan, extract the most expensive subtree programmatically (describe).
  14. Q39Identify a SubPlan re-executed per row (correlated) and its cost.
  15. Q40Detect a GroupAggregate forced by an upstream Sort.
  16. Q41Identify a deep OFFSET causing a full scan+sort.
  17. Q42Find a wide SELECT * preventing Index Only Scan; show row width impact.
  18. Q43Detect a cartesian product from cost explosion in a multi-join.
  19. Q44Compare warm vs cold cache runs (BUFFERS reads -> hits).
  20. Q45Identify the join whose reorder would most help (largest intermediate).
  21. Q46Detect an aggregate spilling (HashAggregate Disk Usage).
  22. Q47From the plan, estimate the intermediate result size before the final aggregate.
  23. Q48Identify whether a LIMIT short-circuited a Nested Loop early.
  24. Q49Spot a redundant Sort that a prior operator already satisfied.
  25. Q50Produce a ranked list of bottleneck nodes for a slow 5-table query.

REWRITES THAT CHANGE THE PLAN

  1. Q51Rewrite a correlated "above-average" query as a grouped JOIN; show plan win.
  2. Q52Rewrite OR-across-columns as UNION ALL; quantify the improvement.
  3. Q53Rewrite EXTRACT(YEAR ...) filter to a range; show Index/Bitmap appears.
  4. Q54Rewrite NOT IN (nullable) to NOT EXISTS; correctness + plan.
  5. Q55Rewrite SELECT * to needed columns to enable an Index Only Scan path.
  6. Q56Rewrite deep OFFSET to keyset; show constant-time plan.
  7. Q57Rewrite a fan-out DISTINCT into EXISTS; show row reduction.
  8. Q58Pre-aggregate one side in a CTE to cut a Nested Loop blowup.
  9. Q59Push a WHERE below a join (rewrite) to shrink intermediates.
  10. Q60Replace ORDER BY random() LIMIT with TABLESAMPLE; plan diff.
  11. Q61Rewrite IN (huge list) as a VALUES join; plan diff.
  12. Q62Rewrite a self-join "previous value" into LAG; plan diff.
  13. Q63Rewrite leading-wildcard search to prefix (where business allows).
  14. Q64Rewrite a HAVING-only filter into WHERE; show pre-aggregation savings.
  15. Q65Split an OR into two indexed-friendly branches via UNION ALL.
  16. Q66Rewrite a date::text LIKE filter to a range; plan diff.
  17. Q67Replace a multi-column concat ILIKE with per-column predicates.
  18. Q68Rewrite correlated EXISTS to a semi-join; compare.
  19. Q69Rewrite an aggregate over a join to aggregate-then-join; compare.
  20. Q70Materialize a reused subquery via a CTE; measure.
  21. Q71Convert a scalar correlated subquery in SELECT to a LEFT JOIN aggregate.
  22. Q72Rewrite COUNT(DISTINCT x) heavy query into a two-step group; compare.
  23. Q73Rewrite a window top-N vs correlated top-N and pick the better plan.
  24. Q74Trim projected columns flowing into a sort to shrink memory.
  25. Q75Produce before/after EXPLAIN ANALYZE for the biggest available win.

"WOULD AN INDEX HELP?" (diagnosis only - fix is Topic 20)

  1. Q76SCENARIO: A point-lookup by email is slow - diagnose whether an index would help (don't create it).
  2. Q77Decide if a date-range orders query would benefit from an index.
  3. Q78Decide if a full-table SUM(net_total) would benefit from an index (it won't - why).
  4. Q79Identify the best candidate column(s) for a composite index from a 2-predicate query.
  5. Q80Decide if a partial index (WHERE status='Cancelled') is justified by selectivity.
  6. Q81Decide whether ORDER BY net_total DESC LIMIT 10 would benefit from an index.
  7. Q82Decide if a leading-wildcard search needs a trigram index (Topic 20/extension).
  8. Q83Decide if an expression index on lower(email) is warranted.
  9. Q84Identify a redundant-index situation (describe; don't change).
  10. Q85Decide if a join key lacks an index causing a Hash/Seq pattern.
  11. Q86Decide if a low-selectivity predicate makes indexing pointless.
  12. Q87Estimate the rows an index would have to return to be worthwhile.
  13. Q88Decide if covering columns (INCLUDE) would enable Index Only Scan.
  14. Q89Decide between single-column vs composite for a (cust_id, order_date) filter.
  15. Q90Decide if a BRIN index suits the append-only page_views timestamp (concept).
  16. Q91Decide if indexing helps a GROUP BY high-cardinality key (usually not).
  17. Q92Decide if a foreign-key column needs an index for join performance.
  18. Q93Identify which of 3 slow queries is the best indexing candidate.
  19. Q94Decide if statistics (ANALYZE / extended stats) fix the plan instead of an index.
  20. Q95Decide if the query should be rewritten before considering any index.
  21. Q96Estimate index size/write-cost tradeoff for a hot write table (concept).
  22. Q97Decide if a partial+covering index is the right tool for an "active rows" query.
  23. Q98Rank 5 queries by expected index ROI (diagnosis).
  24. Q99Write the Day-20 "to-do" index recommendation for a slow report (don't run it).
  25. Q100Produce a full tuning report: bottleneck, rewrites tried, and index recommendations for Topic 20.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Explain the full lifecycle: parse -> plan (cost model) -> execute, and where EXPLAIN sits.
  2. Q2How do the cost constants (seq_page_cost, random_page_cost, cpu_*) shape plan choice?
  3. Q3effective_cache_size and its effect on index-vs-seq decisions.
  4. Q4How estimate errors compound across a 5-table join.
  5. Q5join_collapse_limit / from_collapse_limit and large-join planning.
  6. Q6When does the planner stop reordering joins, and how to influence it.
  7. Q7work_mem per-node semantics; total memory = nodes x work_mem x parallelism.
  8. Q8Hash spill (batches) vs Sort spill (external merge) - diagnosis and levers.
  9. Q9Parallel query: when Gather/Gather Merge helps; parallel-unsafe blockers.
  10. Q10Genetic Query Optimizer (GEQO) for very large joins - implications.
  11. Q11Extended statistics (CREATE STATISTICS) for correlated columns - when needed.
  12. Q12Why row-estimate skew on a FK join causes nested-loop catastrophes.
  13. Q13Plan stability: why the "same" query can flip plans over time.
  14. Q14CTE materialization fence vs inlining (PG12+) and forcing each.
  15. Q15Index Only Scan requirements (covering + visibility map).
  16. Q16Bitmap AND/OR combining multiple indexes - when the planner does it.
  17. Q17LATERAL for top-N-per-group vs window - plan tradeoffs.
  18. Q18Partition pruning in plans (if partitioned) - what to look for.
  19. Q19Why a tiny LIMIT can pick a wildly different (sometimes worse) plan.
  20. Q20Auto-explain / pg_stat_statements for catching slow queries (concept; not installed here).
  21. Q21Reading parallel-aware vs parallel-restricted nodes.
  22. Q22Cost vs actual divergence: planner model limits to be aware of.
  23. Q23When to push work to a materialized view (Topic 25) vs tune the query.
  24. Q24Designing a repeatable benchmark for plan comparison.
  25. Q25A staff-level mental model: read a plan in 60 seconds - the algorithm.

MULTI-JOIN FORENSICS

  1. Q26SCENARIO: A 6-table BI dashboard query times out - isolate the single worst node.
  2. Q27Find the join whose intermediate result explodes and why (estimate error).
  3. Q28Diagnose a Nested Loop chosen on a bad rows=1 estimate.
  4. Q29Diagnose a Hash Join spilling across many batches on a big aggregate.
  5. Q30Find where a Sort dominates and whether an upstream order could remove it.
  6. Q31Identify a correlated SubPlan executed millions of times.
  7. Q32Detect a cartesian product hidden in a 5-table FROM list.
  8. Q33Quantify estimate/actual skew at each join level.
  9. Q34Find the projection (SELECT *) inflating row width through the whole plan.
  10. Q35Identify the parallelism gain (or lack) on a large scan.
  11. Q36Detect a Materialize that should be a hash, or vice versa.
  12. Q37Trace BUFFERS through the plan to find the I/O hotspot.
  13. Q38Identify which FK join lacks support causing repeated scans.
  14. Q39Find a GroupAggregate forced by a Sort that an index could remove (diagnose).
  15. Q40Detect a deep OFFSET in a paginated report killing performance.
  16. Q41Identify a DISTINCT masking a join fan-out and the true cause.
  17. Q42Find the most expensive subtree in a JSON plan and summarize it.
  18. Q43Diagnose a window-function sort dominating a report.
  19. Q44Identify a HAVING that should be a WHERE pre-aggregation.
  20. Q45Detect repeated computation that a CTE would materialize once.
  21. Q46Find the join order the planner chose and propose a better one (rewrite FROM).
  22. Q47Detect an aggregate spilling and estimate the memory needed.
  23. Q48Identify a parallel-restricted function blocking parallelism.
  24. Q49Compare the plan with/without a LIMIT to expose plan flips.
  25. Q50Produce a ranked bottleneck report for the 6-table dashboard.

PLAN STABILITY & REGRESSION

  1. Q51SCENARIO: A nightly report got 10x slower with no code change - hypothesize causes from the plan.
  2. Q52Show how stale statistics flip an Index Scan to a Seq Scan.
  3. Q53Demonstrate ANALYZE restoring a good plan (run ANALYZE, re-EXPLAIN).
  4. Q54Show how data growth changes selectivity and the chosen join.
  5. Q55Show how a parameter value (generic vs custom plan) changes the plan.
  6. Q56Demonstrate LIMIT-induced plan instability.
  7. Q57Show extended statistics fixing a correlated-column estimate (concept + diagnose).
  8. Q58Compare warm vs cold cache to avoid false regressions (BUFFERS).
  9. Q59Identify a plan that's fast on small partitions but bad overall.
  10. Q60Show CTE inlining (PG12+) changing a plan vs MATERIALIZED.
  11. Q61Demonstrate how ORDER BY + LIMIT picks a different index path.
  12. Q62Show how join_collapse_limit affects a many-table plan.
  13. Q63Identify a regression from a row-estimate cliff at a boundary value.
  14. Q64Show how work_mem changes a Sort from in-memory to disk.
  15. Q65Build a minimal repro to compare two plans deterministically.
  16. Q66Detect parameter-sniffing-style instability (custom vs generic plan).
  17. Q67Show a plan that regresses only at month-end data volumes.
  18. Q68Identify a function-volatility change forcing re-evaluation.
  19. Q69Demonstrate that adding columns to SELECT removed an Index Only Scan.
  20. Q70Show how a new low-cardinality value skews MCV-based estimates.
  21. Q71Compare plans across two date ranges with very different selectivity.
  22. Q72Detect a plan relying on an assumption that breaks as data evolves.
  23. Q73Establish a "good plan" baseline to detect future regressions.
  24. Q74Show how disabling a plan node (enable_seqscan=off, session) reveals alternatives.
  25. Q75Write a regression-watch note: query, baseline plan, triggers to watch.

END-TO-END TUNING METHODOLOGY

  1. Q76SCENARIO: Own the company revenue dashboard - produce a full tuning report (diagnose->rewrite->index rec for Topic 20).
  2. Q77Step 1: capture EXPLAIN (ANALYZE,BUFFERS) and identify the top bottleneck.
  3. Q78Step 2: apply the highest-ROI rewrite and re-measure.
  4. Q79Step 3: refresh statistics and re-check the plan.
  5. Q80Step 4: list index recommendations (for Topic 20) with justification.
  6. Q81Step 5: decide what belongs in a materialized view (Topic 25).
  7. Q82Tune the customer-360 query end-to-end (rewrites only).
  8. Q83Tune a cohort-retention query's plan (rewrites only).
  9. Q84Tune a "top products per category" report (window vs LATERAL choice).
  10. Q85Tune a monthly P&L report (pre-aggregation + push-down).
  11. Q86Tune a funnel query's plan (rewrites only).
  12. Q87Tune an RFM scoring query's plan.
  13. Q88Tune a slow search query (leading wildcard) - diagnose trigram need (Topic 20).
  14. Q89Tune a deep-pagination listing to keyset.
  15. Q90Tune a COUNT(DISTINCT) heavy KPI query.
  16. Q91Tune a self-join-based "previous order" to LAG.
  17. Q92Tune an OR-heavy filter via UNION ALL.
  18. Q93Tune an aggregate-over-join to aggregate-then-join.
  19. Q94Decide MV vs query for an expensive nightly metric.
  20. Q95Produce before/after metrics (time, BUFFERS) for one big win.
  21. Q96Document the tuning so another analyst can reproduce it.
  22. Q97Prioritize a backlog of 10 slow queries by expected ROI.
  23. Q98Establish SLOs for dashboard query latency and how to monitor.
  24. Q99Decide when to stop tuning (good enough) for an analyst workload.
  25. Q100Deliver a one-page tuning playbook for the analytics team.