Prompt Engineering

PostgreSQL Query Analysis — EXPLAIN (ANALYZE, BUFFERS) Reading

A real plan with the story inside: estimate says 42 rows, actual is 51,840. PostgreSQL analysis grounded in the plan — stale statistics, bloat, planner costs, and the index arsenal.

Open in SQL Optimization Prompt

Overview

PostgreSQL analysis starts with the right plan command: EXPLAIN (ANALYZE, BUFFERS) — plain EXPLAIN cannot show actual rows, loops, or buffer traffic. This prompt loads a real plan whose story is in the numbers: a Seq Scan with estimate 42 rows against actual 51,840, and nearly 8M rows removed by the filter — the statistics-staleness signature that invalidates every downstream planner choice. The forensic evidence mode requires every conclusion to cite its operator by name, and the platform guidance brings the PostgreSQL-specific checklist: autovacuum and bloat state, random_page_cost on SSDs, partial and expression indexes where evidence supports them, and whether the query shape blocks parallelism.

How to use this resource

Capture the right plan

EXPLAIN (ANALYZE, BUFFERS) — actuals and buffer traffic, not estimates alone.
Read the gaps

Estimate 42 vs actual 51,840 is not noise — it is the planner working from a false map.
Fix causes, not symptoms

Stale statistics and bloat get fixed before index advice — the plan may fix itself.

Why This Works

Plan-grounded analysis cannot drift into generic advice
The estimate-vs-actual lens finds the root cause behind wrong plans
Platform guidance covers what PostgreSQL specifically rewards and punishes

Best for

PostgreSQL queries that ignore an index you expected
Plans whose estimates and actuals disagree wildly
Teams moving from guessing to plan-reading

Not for

MySQL or SQL Server plans — operator semantics and tooling differ by platform
Explaining what the query does functionally — that's the Code Explanation Prompt

Use cases

Reading an EXPLAIN ANALYZE output operator by operator
Diagnosing estimate-vs-actual gaps as statistics problems
Choosing between partial, expression, and BRIN indexes with evidence

FAQ

Why does this PostgreSQL prompt demand EXPLAIN (ANALYZE, BUFFERS) and not plain EXPLAIN?

Plain EXPLAIN can't show actual rows, loops, or buffer traffic — only estimates. The DATABASE CONTEXT demands EXPLAIN (ANALYZE, BUFFERS) so the analysis can read the estimate-42-vs-actual-51,840 gap that flags stale statistics. The sql-optimization-prompt generates this analysis prompt; you still run the EXPLAIN yourself and paste the verbatim plan for it to read.

Can this prompt recommend an index without knowing which indexes already exist?

It's built to refuse that. The QUERY CONTEXT says existing indexes are "not provided — do not assume any index exists," so under Forensic Optimization mode, where evidence is missing the output emits the exact command to gather it instead of a guess. Every recommendation must cite its operator, row count, or schema fact by name.

Does this work for reading a MySQL or SQL Server execution plan?

Not well — the platform is set to PostgreSQL, and the guidance is Postgres-specific: autovacuum and bloat, random_page_cost on SSDs, and partial, expression, or BRIN indexes. notFor states operator semantics and tooling differ by platform for MySQL or SQL Server. Regenerate in the sql-optimization-prompt with the right platform so the checklist matches your engine.

Customize This Resource

Opens this setup in SQL Optimization Prompt. Generate to get the full optimization contract — then adjust the goal, platform, and evidence mode.

Open in SQL Optimization Prompt

Prompt Template

Copy it as-is, or use Open in SQL Optimization Prompt to load it pre-filled and customize it with your own context.

OPTIMIZATION OBJECTIVE
Analyze this EXPLAIN ANALYZE output: the events query seq-scans despite the tenant filter.
Optimization goal: execution plan analysis — read the plan the engine actually chose, operator by operator.
Establish why this query is expensive before changing anything. Every recommendation must trace to evidence from this query, this plan, or this schema — no generic database advice.

DATABASE CONTEXT
Platform: PostgreSQL.
- Demand EXPLAIN (ANALYZE, BUFFERS) — plain EXPLAIN cannot show actual rows, loops, or buffer traffic.
- Check bloat and statistics: dead tuples inflate scans and stale statistics mislead the planner — verify last autovacuum and analyze times.
- Reason about planner costs: default random_page_cost assumes spinning disks; on SSDs it pushes the planner away from index scans it should take.
- Use PostgreSQL's index arsenal only where evidence supports it: partial indexes for hot subsets, expression indexes for computed predicates, BRIN for huge append-only tables.
- Check parallelism: whether the plan uses workers, and whether the query shape (volatile functions, some CTEs) blocks them.

QUERY CONTEXT
Query:
```sql
SELECT id, type, payload, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 100;
```
Execution plan (verbatim):
```
Limit  (cost=184320.10..184320.35 rows=100 width=64) (actual time=1853.21..1853.44 rows=100 loops=1)
  ->  Sort  (cost=184320.10..184320.21 rows=42 width=64) (actual time=1853.19..1853.30 rows=100 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 58kB
        ->  Seq Scan on events  (cost=0.00..184319.00 rows=42 width=64) (actual time=0.04..1843.55 rows=51840 loops=1)
              Filter: (tenant_id = 42)
              Rows Removed by Filter: 7948160
Planning Time: 0.21 ms
Execution Time: 1854.13 ms
```
An execution plan is provided — ground the analysis in it. Do not reason from the query text where the plan already answers.
Tables and row counts:
- events: ~8M rows
Existing indexes: not provided — do not assume any index exists. Before recommending new indexes, list what must be checked about the current ones.

PERFORMANCE SYMPTOMS
Query takes ~1.9 seconds; estimate says 42 rows, actual is 51,840.

OPTIMIZATION GOAL
Primary goal: Execution Plan Analysis.
Analysis priorities for this goal:
1. Work from the actual plan, not the query text: optimize what the engine does, not what the SQL looks like.
2. Find the cost concentration: the one or two operators where most of the time or rows go.
3. Compare estimates against actuals: a large estimate-vs-actual gap means stale statistics or unmodelable predicates — and invalidates every downstream choice.
4. Follow the row counts between operators — where rows explode or collapse is where the story is.

EVIDENCE REVIEW
Evidence mode: Forensic Optimization.
- Every conclusion must cite its evidence by name: the plan operator, the row count, the schema fact.
- No recommendation without an evidence trail — where the evidence does not exist yet, output the exact command to gather it instead of the recommendation.
- Label every statement: FACT (from the provided query, plan, or schema), ASSUMPTION (believed without evidence), or HYPOTHESIS (a testable claim with its test).
- Attach a confidence level to every recommendation, justified by the strength of its evidence.
- Generic advice is forbidden: a recommendation that would read the same without ever seeing this query does not belong in the analysis.

BOTTLENECK ANALYSIS
- Identify the bottlenecks and, for each: what it costs, why it matters for this workload, and the estimated share of the total cost.
- Expected analysis areas for this goal: Highest-cost operators; Estimate vs actual gaps; Plan warnings: spills, conversions, missing stats; Choices the optimizer was forced into.
- Distinguish the bottleneck from its symptom — a slow sort caused by a missing filter is a filter problem, not a sort problem.

OPTIMIZATION OPPORTUNITIES
- Order opportunities by expected impact, highest first; state the basis for each estimate.
- For each opportunity: the change, the expected effect, and the evidence that predicts it.
- Any query rewrite must return identical results — call out every difference in NULL handling, duplicates, and ordering, or state explicitly that there is none.
- Do not tune speculatively: a change without an evidenced problem is risk without benefit.

INDEX RECOMMENDATIONS
- Every index recommendation must name the exact predicates, joins, or sorts it serves — no index without a clause.
- Justify column order for composite indexes, and say whether the index should cover (and at what storage and write cost).
- State each index's write tax: which inserts and updates it slows, and whether the workload can afford that.
- Check existing indexes first: a near-miss index that could be extended beats a new overlapping one.

TRADEOFF ANALYSIS
- Every recommendation carries its costs: write amplification, storage, maintenance burden, plan-stability risk, staleness (for pre-aggregation).
- State when NOT to apply each recommendation — the workload shape under which it backfires.

ASSUMPTIONS
- List every assumption made about data volumes, value distributions, index state, or workload patterns.
- Mark each assumption VERIFIED (with its evidence) or UNVERIFIED (with the query or command that would resolve it).
- Any recommendation that depends on an UNVERIFIED assumption must be flagged as conditional on it.

NON-GOALS
- Do not invent execution plans.
- Do not assume indexes exist.
- Do not assume row counts.
- Do not recommend changes without justification.
- Separate facts from assumptions throughout.
- Explain the tradeoffs of every change that has them.
- Schema redesign and application-level changes are out of scope — unless the evidence shows no query-level fix exists, in which case say so and stop.

OUTPUT REQUIREMENTS
- Present recommendations as an ordered list, highest expected impact first, each with its evidence and its tradeoffs.
- For each recommendation, include the verification step: how to measure the improvement — EXPLAIN (ANALYZE, BUFFERS) before and after, on production-shaped data.
- Where essential evidence is missing, the first recommendations are the commands to gather it — not guesses in its place.
- End with the open questions: what could not be determined from the provided context, and what would settle each.

More resources from SQL Optimization Prompt

Resource

Optimize SQL Query — Joins, Cardinality, and Wasted Work

A five-table join that got slow as data grew: establish cardinality first, check each join strategy against the data shape, and hunt the fan-out doing wasted work.

Prompt Engineering

Resource

SQL Optimization Prompt — the Evidence-Based Contract

"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.

Prompt Engineering

Resource

Execution Plan Analysis — Read What the Engine Actually Chose

Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.

Prompt Engineering

Resources that pair well

Resource

Debugging Prompt — the Investigation Contract

"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.

Prompt Engineering

Resource

Code Review Prompt — the Review Contract

"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.

Prompt Engineering

Resource

Refactor Prompt — the Behavior Preservation Contract

"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.

Prompt Engineering

Related tools

Tool

SQL Optimization Prompt

Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.

Coding Workflows

Tip: Save time by exploring related resources and tools that integrate with this resource.