Explain an Execution Plan Before You Change Anything

Tier: Foundation · Workflow area: SQL Authoring & Query Tuning · ~25 minutes

Execution plans are the most information-dense artifact in database work and the least read. This playbook uses AI as a plan translator: it turns a nested tree of operators into a narrative you can verify node by node. The output is understanding, not action — changes come later (pb-03), and only with before/after proof.

When to use

• Any plan longer than a screen20+ node plans from ORMs and report builders are where human eyes glaze and AI narration pays for itself.
• A query got slow and nobody knows whybefore guessing at indexes, get the narrative: which node consumed the time, and was it doing more work than the optimizer predicted?
• Onboarding onto someone else's queryinherited SQL plus its plan, explained together, is the fastest route to safe ownership.

When NOT to use

• As a substitute for capturing the real planan LLM asked to "predict the plan" for a query invents one. Only feed plans your engine actually produced, and prefer ANALYZE (actual) over estimated plans.
• When the plan is the wrong artifactif the wait profile says the session is blocked on locks or I/O saturation, the plan walkthrough will be a well-explained red herring. Check waits first (pb-07).
• To justify a change you already madenarration after the fact rationalizes. The discipline is plan-read first, hypothesis second, change third.

The recipe

1. 1.

   Capture the actual plan with runtime numbers. Estimates alone hide the most important signal — the estimate-vs-actual gap:

   Copy

   EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
   SELECT c.region, count(*), sum(ol.amount)
   FROM orders o
   JOIN order_lines ol ON ol.order_id = o.id
   JOIN customers c ON c.id = o.customer_id
   WHERE o.ordered_at >= now() - interval '90 days'
   GROUP BY c.region;
   

   If the query is too slow or too dangerous to run with ANALYZE, say so and use plain EXPLAIN — but tell the model it is looking at estimates only.

2. 2.

   Prompt for a structured walkthrough, not a verdict. The failure mode is an LLM that skips to "add an index." Force the reading first:

   Copy

   You are a senior PostgreSQL performance engineer. Below is an actual
   execution plan (EXPLAIN ANALYZE, BUFFERS) from PostgreSQL 16.
   
   Walk it in four parts, in order, before any recommendations:
   1. NARRATIVE — bottom-up: what is read, how, in what join order,
      in plain language a developer can follow.
   2. TIME — which nodes account for the actual time, with the numbers.
   3. ESTIMATE GAPS — every node where estimated rows and actual rows
      differ by more than 10x, and what each gap implies.
   4. BUFFERS — where the reads happen; shared hit vs read ratio.
   Only then: 5. up to three hypotheses worth testing, each phrased as
   "test X, expect Y" — no DDL, no config changes.
   
   Plan:
   [paste plan]
   

3. 3.

   Verify the narrative against the tree. Spot-check three claims minimum: the node the AI says is hottest really carries the largest actual time; the join order it describes matches the tree nesting; the row counts it quotes exist in the plan text. A good verified summary reads like:

   Copy

   Hot node: Parallel Seq Scan on order_lines
     actual time 2841ms of 3105ms total  ✓ (matches plan line 7)
   Estimate gap: index scan on orders — est. 1,240 rows, actual 386,554
     (312x)  ✓ — planner chose nested loop on a bad estimate
   Buffers: shared read 412,009 vs hit 18,441 — cold, I/O-bound  ✓
   

4. 4.

   Interrogate the biggest estimate gap. Estimate gaps are usually stale statistics, correlated columns, or a non-sargable predicate. Ask directly, then confirm on the database:

   Copy

   -- When were stats last refreshed on the misestimated table?
   SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_mod_since_analyze
   FROM pg_stat_user_tables
   WHERE relname IN ('orders', 'order_lines');
   
   -- Does the predicate defeat the index? (functions, casts, mismatched types)
   SELECT attname, atttypid::regtype
   FROM pg_attribute
   WHERE attrelid = 'orders'::regclass AND attname = 'ordered_at';
   

   If n_mod_since_analyze is a large fraction of n_live_tup, run ANALYZE orders; on a copy or replica and re-capture the plan before believing anything else.

5. 5.

   Write the two-line diagnosis. Compress the walkthrough into the ticket: time goes to X because Y; the leading hypothesis is Z, testable by W. Example: "93% of time is a seq scan on order_lines driven by a nested loop chosen on a 312x row misestimate; hypothesis: stale stats on orders — re-ANALYZE and re-plan before considering indexes."

6. 6.

   Park the hypotheses, don't execute them. The output of this playbook is understanding plus a ranked test list. Execution belongs to the tuning loop with before/after verification (pb-03).

Make it yours

• SQL Servercapture the actual execution plan (SSMS: Include Actual Execution Plan, or SET STATISTICS XML ON), paste the XML or the plan summary, and ask the same four-part walkthrough; estimate gaps show as Estimated vs Actual Number of Rows per operator, and SET STATISTICS IO ON output stands in for BUFFERS.
• MySQLuse EXPLAIN ANALYZE (8.0.18+) for the tree with actual timings; there is no buffers section, so pair it with SHOW STATUS LIKE 'Handler%' deltas around the query run.
• Oraclegenerate the cursor's real plan with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')) after running with /*+ GATHER_PLAN_STATISTICS */; E-Rows vs A-Rows is your estimate-gap column.

Quality check

• The plan is actual, recent and from the right environmenta week-old plan from staging explains nothing about today's production incident.
• Every AI claim you repeat was matched to a plan linenarrations are occasionally, confidently wrong about which node dominates. You verified against the text, not the vibe.
• Estimate gaps got a statistics check before an indexing theorystale stats explain more slow plans than missing indexes do, and ANALYZE is cheaper than DDL.
• No change shipped from this sessionif you feel the urge to create an index right now, that is pb-03, with hypopg and before/after plans.