From pg_stat_statements to the Execution Plan: LLM-Assisted PostgreSQL Diagnosis

The query with the largest lifetime execution time is not necessarily the query that caused today’s incident, and the plan you reproduce afterward is not necessarily the plan that ran during it.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

The previous investigation classified the incident boundary: PostgreSQL workload rose while blocking, host pressure, and storage limits were either supported or contradicted by evidence. The next question is attribution. Which query shape changed, and what execution behavior explains the change?

PostgreSQL provides complementary evidence. pg_stat_statements aggregates planning and execution counters by database, user, query identifier, and top-level status. EXPLAIN shows the plan the optimizer would choose in a stated context. EXPLAIN ANALYZE executes the statement and adds actual rows, loops, time, buffers, and optional WAL evidence.

None is a plan-history system. The diagnostic chain must preserve interval, identity, execution context, and plan provenance before an LLM compares anything.

The Problem

Four shortcuts repeatedly produce false diagnoses:

• sorting cumulative total_exec_time and calling the first row the incident cause;
• subtracting two lifetime averages instead of calculating counter deltas;
• treating normalized query text as the exact statement and parameter set that ran;
• running EXPLAIN ANALYZE in production and assuming the reproduced plan proves historical behavior.

pg_stat_statements may combine literal variants, evict low-frequency entries, or reset counters. A query ID has limited stability and can change across major versions or catalog differences. Prepared statements may use custom or generic plans. Statistics, schema, configuration, role, search_path, and parameter values all influence the plan.

The core question is: can we connect an interval-level workload change to the execution plan that produced it, while preserving enough contradictory evidence to reject a convenient but unsupported explanation?

Build a Query Attribution Chain

flowchart TD
    A[classified PostgreSQL workload incident] --> B[baseline and incident counter snapshots]
    B --> C[reset-aware interval deltas]
    C --> D[ranked query identity candidates]
    D --> E[version role schema settings and parameter provenance]
    E --> F[safe plan capture method]
    F --> G[estimated and actual structural evidence]
    G --> H[LLM observations hypotheses gaps and actions]
    H --> I[DBA verification]
    I --> J[bounded remediation and validation]

The collector does the arithmetic. The LLM receives a compact record with source artifact IDs, not an unrestricted statistics dump or raw application parameters.

Symptoms

Interval evidence	Leading question	What it does not prove
Execution-time share rises while calls are stable	Did cost per execution rise?	The plan changed
Calls rise while cost per call is stable	Did workload amplification occur?	The SQL is inefficient
Shared reads per call rise	Did access behavior or working set change?	Physical storage performed each read
Temp blocks per call rise	Did a sort, hash, or materialization spill?	Global work_mem should increase
Rows per call changes	Did parameters or result cardinality change?	Planner estimates were wrong
WAL bytes per call rise	Did write amplification change?	WAL configuration is the cause
Latency rises but statement counters do not	Is time outside executor accounting or collection coverage?	PostgreSQL is innocent

First Five Checks

1. Verify collection coverage before ranking queries

Confirm pg_stat_statements is preloaded, query identifiers are computed, the extension exists in the database being queried, and the monitoring role can see the required fields. Record these settings:

SELECT current_setting('server_version') AS server_version,
       current_setting('compute_query_id') AS compute_query_id,
       current_setting('pg_stat_statements.track') AS statement_track,
       current_setting('pg_stat_statements.track_planning') AS planning_track;

SELECT stats_reset, dealloc
FROM pg_stat_statements_info;

A rising dealloc count means statement churn exceeded the configured entry capacity and low-frequency history was discarded. Planning counters remain zero when planning tracking is disabled; enabling it is a deliberate overhead decision, not an incident-time reflex.

2. Rank interval deltas, not cumulative totals

Snapshot counters at the start and end of baseline, transition, incident, and recovery windows. Retain dbid, userid, toplevel, queryid, stats_since, and the module reset time alongside the measurements.

For each identity, derive:

calls in window          = calls end minus calls start
execution time in window = total exec time end minus total exec time start
milliseconds per call    = execution time in window divided by calls in window
rows per call            = rows delta divided by calls in window
blocks per call          = block delta divided by calls in window
WAL bytes per call       = WAL byte delta divided by calls in window

Do not subtract mean_exec_time values. Recompute the interval mean from additive counters. Reject a delta when its entry appeared after the first snapshot, stats_since changed, the module reset crossed the window, or identity cannot be joined safely. Rank both total contribution and per-call regression: a cheap query called far more often can dominate an incident.

3. Reconstruct execution context

Treat (dbid, userid, queryid, toplevel) as the local identity, not queryid alone. Store server major version, database, role, search_path, relevant planner settings, schema migration and statistics timestamps, prepared-statement behavior, parameter types, and sanitized parameter classes.

The representative SQL replaces many constants with $1, $2, and similar symbols. It is not a safe source of production literals. Identical text can map to different identities under different object resolution, while unrelated statements have a small hash-collision risk. Never correlate query IDs across a major upgrade without direct validation.

4. Choose the least risky plan evidence

Start with plain EXPLAIN (SETTINGS, FORMAT JSON): it plans but does not execute the statement. Use EXPLAIN (GENERIC_PLAN, FORMAT JSON) when the normalized statement contains placeholders and a generic plan is the question; it cannot be combined with ANALYZE.

Use runtime plans only through an approved route:

• capture the plan that naturally ran through bounded auto_explain sampling;
• reproduce on a current physical replica only when replay state and read-only constraints are understood;
• reproduce in a production-like environment with the same statistics, schema, settings, and parameter class;
• run a bounded read-only EXPLAIN (ANALYZE, TIMING FALSE, FORMAT JSON) only after estimating execution risk, locks, result work, and resource consumption.

ANALYZE executes the statement. Wrapping write-bearing analysis in BEGIN and ROLLBACK prevents committed table changes, but it does not make expensive work, locks, sequence effects, functions, or external side effects harmless. Do not let the LLM select or execute this route.

5. Read structure before recommending a fix

Compare estimates with actual rows at each node, and multiply actual rows and time by loops where repeated execution matters. Then inspect filters, rows removed, join fan-out, sort method, hash batches, shared and temp blocks, WAL, workers planned versus launched, and settings captured with the plan.

Costs are planner units, not milliseconds. A sequential scan is not inherently wrong. A nested loop is not inherently wrong. Shared reads are not proof of physical device I/O because the operating system may satisfy them from its cache. A plan captured after ANALYZE, DDL, configuration changes, or cache transition is a new observation—not historical proof.

Decision Tree

flowchart TD
    A[incident query candidate] --> B{valid interval deltas and stable identity}
    B -->|no| C[repair collection gap]
    B -->|yes| D{calls or cost per call changed}
    D -->|calls| E[investigate application workload amplification]
    D -->|cost| F{historical plan artifact exists}
    F -->|yes| G[compare plan structure and provenance]
    F -->|no| H[capture safe current runtime evidence]
    G --> I{estimates diverge from actuals}
    H --> I
    I -->|yes| J[inspect statistics skew correlation and parameters]
    I -->|no| K{temp blocks or repeated loops dominate}
    K -->|yes| L[investigate spill or join fan-out]
    K -->|no| M[correlate waits I/O locks and application time]

LLM Evidence Contract

Require the model to return four separate objects:

OBSERVATION
Incident execution-time delta increased for identity dbid userid queryid toplevel [pps-delta-04].

HYPOTHESIS
A row-estimation error may have amplified the inner side of a nested loop.

MISSING EVIDENCE
The incident plan and parameter class are unavailable; the current plan is not historical proof.

ACTION
Capture a sampled runtime plan for the same identity and compare estimated rows actual rows and loops.

Confidence must fall when provenance is missing. The output must identify evidence against each leading hypothesis and must not translate “Seq Scan observed” directly into “create an index.”

In Practice

The PostgreSQL 18 pg_stat_statements documentation defines each entry by database, user, query ID, and top-level status. It documents normalization, possible hash collisions, entry deallocation, reset timestamps, and limited query-ID stability. It also states that planning tracking is off by default and can impose noticeable contention overhead. The derived operational pattern is to capture additive counters and collector health before performing any LLM analysis.

PostgreSQL’s EXPLAIN command reference states that ANALYZE executes the statement and that planner cost is expressed in arbitrary units. Its plan-reading guide explains that actual rows and time are averages per execution when a node loops. These documented semantics make provenance and multiplication part of correctness, not presentation detail.

The PREPARE documentation describes how PostgreSQL may choose between parameter-specific custom plans and reusable generic plans. It also documents replanning after relevant DDL, updated planner statistics, or search_path changes. Therefore a plan generated with a convenient literal after the incident can legitimately differ from the application’s prepared execution.

PostgreSQL’s auto_explain documentation provides automatic slow-plan logging and sampling, but warns about overhead. With analysis enabled, per-node timing can affect every statement even when the threshold is not met; disabling timing reduces that cost when row counts are sufficient. This supports bounded, measured capture—not permanent maximal logging.

Remediation Options

Proven finding	Bounded remediation	Avoid	Validate with
Stale table statistics	Targeted ANALYZE under an approved window	Assuming every estimate error is stale data	New estimate accuracy, plan, latency, load
Correlated predicates or skew	Evaluate higher statistics targets or extended statistics	Blanket statistics increases	Estimate error on representative parameter classes
Missing access path	Build and test a selective index with deployment controls	Indexing every sequential scan	Plan choice, write cost, index size, latency
Sort or hash spill	Reduce input first; test scoped memory change	Raising global work_mem	Temp blocks, memory concurrency, latency
Nested-loop amplification	Fix estimates, predicates, or access path	Disabling join methods globally	Actual rows times loops, plan stability
Generic-plan sensitivity	Verify custom versus generic behavior and application preparation	Forcing a cluster-wide cache mode	Tail latency across parameter classes, planning cost
Call amplification	Fix retry, fan-out, batching, or request behavior	Tuning the plan for an application storm	Calls per request, throughput, total execution share

Rollback Plan

Collection changes need rollback too. If planning tracking, auto_explain, I/O timing, or a higher sampling rate adds measurable overhead or log volume, return the named setting to its recorded value and confirm that telemetry pressure falls. Preserve the resulting evidence gap.

For SQL remediation, record the old plan artifact, configuration, schema definition, statistics state, application version, and validation window. Index deployment needs a removal decision and dependency check. Scoped settings expire with their session or transaction. Statistics changes cannot always be restored exactly, so compare against a prepared fallback plan and application rollback. Never reset pg_stat_statements to make validation look clean; use fresh snapshots and deltas.

Where It Breaks

Failure mode	False conclusion	Correction
Lifetime ranking	The largest historical query caused the incident	Rank reset-aware interval deltas
Average subtraction	Mean latency changed by the difference of two means	Recompute from total-time and call deltas
Query ID used globally	One ID is stable across upgrades and replicas	Scope identity and validate compatibility
Current plan called incident plan	A regression has been proven	Require a historical artifact or state the gap
Convenient literal is reproduced	Prepared behavior is represented	Capture parameter class and generic or custom provenance
ANALYZE treated as read-only inspection	Plan capture has no operational effect	Use explicit approval and bounded execution
Node type becomes diagnosis	Seq scan or nested loop is automatically wrong	Evaluate cardinality, loops, buffers, and total contribution
LLM receives raw SQL and binds	More context means better diagnosis	Redact, minimize, and retrieve sensitive text only when approved

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

• No Raw Customer Data: The LLM must not receive bind values, EXPLAIN ANALYZE output containing literal filter constants, pg_stat_activity.query text with embedded parameters, or table contents. Send normalized pg_stat_statements queryid and digest text, plan shapes, and timings.
• No Autonomous Production Execution: The LLM cannot run VACUUM FULL, REINDEX, ALTER SYSTEM, pg_terminate_backend, index DDL, or parameter changes. It proposes; a human authorizes, applies, validates, and holds the rollback condition.
• No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
• No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
• No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

• Problem: PostgreSQL statement statistics aggregate history, while execution plans depend on context and runtime capture can change production behavior.
• Solution: Rank reset-aware interval deltas, preserve scoped query identity and execution provenance, then acquire the least risky plan evidence needed to test a specific hypothesis.
• Proof: Replay a known call surge, cardinality change, and spill in a controlled environment. The workflow should distinguish frequency from per-call regression and refuse to claim a historical plan change when the prior plan is absent.
• Action: Add two-snapshot delta calculation, collector-health checks, plan provenance, and an approval-gated plan-capture route before allowing an LLM to recommend SQL remediation.

The next article separates Aurora PostgreSQL from EC2 PostgreSQL: distributed storage, WAL behavior, replica topology, waits, failover, and the managed-service evidence needed before blaming SQL.

Sources

• PostgreSQL 18 — pg_stat_statements
• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Using EXPLAIN
• PostgreSQL 18 — PREPARE
• PostgreSQL 18 — auto_explain