Databases & Data / Observability & Monitoring

Find and fix slow PostgreSQL queries with pg_stat_statements and EXPLAIN

Deploy privacy-aware PostgreSQL 18.4 query statistics, identify normalized high-impact queries, capture safe estimated and actual plans, and validate one reversible fix.

300 min14 stepsHigh-impact changeRevision 2

Save or explore

#postgresql #performance #pg-stat-statements #explain #query-planning #indexes #observability

0 of 14 steps completed

Goal

Move from a user-visible latency or database resource symptom to an evidence-backed, correctness-preserving query improvement without unsafe production execution.

Supported environments

PostgreSQL 18.4
Ubuntu Server 24.04 LTS

Prerequisites

Controlled PostgreSQL restart Owner-approved window with existing preload module inventory, health checks, failover/rollback and matching PostgreSQL 18.4 contrib package.
Representative workload evidence Application percentile/throughput metrics, release markers, parameter cohorts and a named database observation window.
Safe plan environment Scrubbed production-like restore or approved standby for SELECT; isolated restored clone for DML and external-effect risk.
Privacy and access controls Restricted observability role, protected/redacted plan storage, no secret literals/comments and audited reset/text access.
Change and rollback path Correctness suite, schema/query deployment controls, lock/storage/WAL/write budget, canary and tested rollback.

Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System

A privacy-aware PostgreSQL 18.4 `pg_stat_statements` deployment loaded through `shared_preload_libraries`, enabled only in approved databases, sized and configured for the workload, monitored for deallocations and overhead, and protected so representative SQL text is visible only to authorized roles.
A repeatable workflow that ranks normalized query families by total time, mean time, calls, rows, temporary I/O, WAL and block-read time; correlates them with waits, locks, table/index statistics and application releases; then captures a safe plan without immediately executing an unknown statement.
A controlled `EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)` process that acknowledges execution and measurement overhead, uses representative parameters, bounded timeouts and an isolated/replay-safe environment, and wraps eligible DML in `BEGIN ... ROLLBACK` only when all effects are transactional.
A one-change-at-a-time remediation and verification loop covering statistics, query shape, indexes, schema and configuration, with before/after workload evidence, regression checks, deployment rollback and continuous observation.

Observable outcome

`pg_stat_statements` is preloaded after a controlled restart, query IDs are enabled, the extension exists where needed, settings are explicit, and non-privileged users cannot read other users' query text.
Operators select a query because it violates a stated latency/resource objective over a representative window—not because one sample looked slow—and retain its `queryid`, statistics reset time, normalized text, calls and workload context.
Estimated and actual plans are captured safely. Rows-versus-estimates, loops, buffer hits/reads/dirties/writes, temporary blocks, I/O timing, WAL, sorts, joins, parallelism, settings and execution overhead are interpreted together.
The chosen change improves the target percentile or resource objective under representative load without unacceptable write amplification, lock time, storage, cache churn, plan regression or loss of correctness; rollback is proven before rollout.

Architecture

How the parts fit together

The PostgreSQL 18.4 server calculates query identifiers and the preloaded `pg_stat_statements` module keeps shared cumulative counters for normalized statement families across databases. A per-database extension exposes views and functions. Only privileged observability roles can see complete query text and reset statistics. Application latency metrics and PostgreSQL views identify a candidate query ID over a named observation window. The operator first uses ordinary `EXPLAIN` or `GENERIC_PLAN` for a non-executing estimate, then reproduces representative parameters in an isolated environment or carefully controlled production session. `EXPLAIN ANALYZE` actually executes the statement and adds instrumentation; DML is tested inside a transaction ending in `ROLLBACK` only when triggers/functions cannot cause external or nontransactional effects. Plan evidence is compared with schema/statistics/I/O/locks. One reviewed change is deployed through a canary and measured against the same query ID/workload objective.

pg_stat_statements shared moduleTracks normalized planning/execution counters keyed by database, user, query ID and top-level status in shared memory.

Per-database extensionExposes the views and reset/info functions in each approved database; loading the library alone does not create them.

Restricted observability roleReads performance data and protected query text according to data classification without obtaining write or superuser privileges.

Application and host telemetrySupplies percentile latency, request volume, deployment markers, CPU, memory, I/O, cache and lock context missing from cumulative SQL counters.

Safe plan workspaceUses a scrubbed production-like clone, read-only replica for SELECT analysis, or bounded session to inspect representative plans without uncontrolled effects.

Change and rollback pipelineApplies one query/index/statistics/configuration change, validates correctness and performance, canaries it, and restores the prior state if gates fail.

Define the user-visible latency/resource objective, privacy boundary, observation window and safe analysis environment before enabling new instrumentation.
Preload `pg_stat_statements`, restart deliberately, create the extension in approved databases, configure query-ID and tracking scope, and measure overhead/deallocations.
Capture the statistics reset timestamp and rank normalized query IDs across a representative window using total and per-call cost plus I/O/WAL/temp evidence.
Correlate the candidate with active waits, locks, table/index statistics, application route/release, parameters and data distribution.
Run non-executing EXPLAIN first; then run EXPLAIN ANALYZE only with explicit execution approval, timeouts, representative values and a safe transactional/environment boundary.
Interpret estimate errors, loops, buffers, I/O, spills, joins, scans, sorts, WAL and settings; form one falsifiable hypothesis.
Apply one reversible change, validate correctness, compare the same workload and query family, canary rollout, and keep monitoring for delayed regressions.

Assumptions

The server runs PostgreSQL 18.4, the current supported PostgreSQL 18 minor release verified on 2026-07-28; PostgreSQL 19 is beta and is not used by these examples.
The operator has a controlled restart window and can inspect `shared_preload_libraries` without removing existing modules. Restart and extension creation are owned changes.
Application metrics identify routes/jobs and latency percentiles, while database statistics retain a representative workload window. Cumulative averages alone are not treated as percentiles.
A scrubbed production-like database, isolated restore, or suitable standby exists for expensive SELECT analysis. Production `EXPLAIN ANALYZE` requires an explicit risk review and bounded session.
Representative bind values and data distribution can be obtained without copying secrets or personal data into tickets, chat, logs or public plan-sharing services.
Schema and query changes follow normal migration, locking, backup, correctness and rollback controls. This guide does not authorize arbitrary indexes or configuration changes.

Key concepts

Normalized statement: `pg_stat_statements` replaces constants with parameter symbols and aggregates statements with the same query structure, but planning/rewrite differences and query-ID stability still need care.
queryid: A hash identifying a normalized parse tree within a PostgreSQL major version and environment. It is useful for correlation, not a permanent cross-version business identifier.
Shared preload: The module requires shared memory initialized at server start, so adding/removing it from `shared_preload_libraries` requires restart rather than reload.
Representative query text: The view stores one representative normalized text for a query family. Text can still reveal table/column names, comments, literals in some utility statements and business structure.
Estimated plan: Ordinary EXPLAIN asks the planner for a plan without running the statement. Costs are planner units, not elapsed milliseconds.
Actual plan: EXPLAIN ANALYZE executes the statement and measures rows, loops and time. It can block, consume resources, write data and trigger effects.
Buffers: Shared/local/temp block hits, reads, dirties and writes show cache and I/O behavior. A hit avoids a storage read but still consumes CPU and memory bandwidth.
Estimate error: A mismatch between estimated and actual rows after accounting for loops. It often points to stale/inadequate statistics, correlation, skew or parameter sensitivity.

Before you copy

Values used in this guide

{{databaseName}}

Approved database in which the extension and analysis are used.

Example: orders

{{queryId}}

Selected normalized query ID from the named statistics window.

Example: -6131348924739121057

{{observationMinutes}}

Representative period used for before/after comparison.

Example: 60

{{statementTimeout}}

Session-local ceiling for controlled plan execution.

Example: 15s

{{lockTimeout}}

Session-local maximum wait for a database lock.

Example: 1s

{{targetQueryFile}}secret

Mode-0600 file containing reviewed, redacted SQL for controlled analysis.

Example: /secure-staging/query-6131348924739121057.sql

Security and production boundaries

`pg_stat_statements` query text and plans expose schema names, predicates, comments and business behavior. Restrict `pg_read_all_stats`/view access, redact exports, and never paste production plans into a public service.
Do not include secrets in SQL comments or literals. Normalization reduces many constants but is not a data-loss-prevention boundary, especially for utility statements and surrounding logs.
Only superusers and roles with `pg_read_all_stats` can see other users' SQL text; verify effective grants and avoid giving the application reset or broad statistics privileges.
`EXPLAIN ANALYZE` executes the statement. `SELECT` can still lock, run volatile functions, call foreign servers or consume extreme resources. DML can fire triggers and functions; `ROLLBACK` cannot undo external side effects.
Use `SET LOCAL statement_timeout`, `lock_timeout`, safe `search_path`, application name and transaction settings in a dedicated session. Never disable safety timeouts globally.
Plans and query files are sensitive artifacts. Store them with restricted permissions and retention, redacting literal values, hostnames, tenant IDs and object names where required.

Stop before continuing if

Stop if restart would remove another preload library, no rollback window exists, or module overhead/shared-memory sizing cannot be measured.
Stop if query-text access violates privacy, secrets appear in SQL/plans/logs, or exported evidence lacks redaction and access controls.
Do not run EXPLAIN ANALYZE for an unknown or unbounded statement, on production DML without transactional proof, or on functions/triggers with external/nontransactional effects.
Stop if representative parameters/data are unavailable, statistics window includes an unmarked reset/deployment, or query ID mapping is uncertain.
Stop any plan execution at timeout, blocking, replication lag, CPU/I/O, WAL, temp or latency threshold; preserve evidence and cancel through the owned path.
Do not deploy a change that lacks correctness tests, lock/storage/write-amplification analysis, canary metrics and a proven rollback.

decision

Define the performance and safety question

read-only

Name the affected application route or job, latency percentile/resource symptom, correctness expectation, observation window, representative load and parameters, privacy owner, maximum analysis overhead, safe environment, and rollback gate before collecting query text or executing plans.

Why this step matters

Optimization without a question tends to move cost rather than improve service. `pg_stat_statements` aggregates database time, while users experience route percentiles that include queues, network and application work. Define both and identify the workload window, because cumulative data across releases or resets can mislead. Parameters and data skew can choose different plans for the same normalized query. Safety boundaries must be written before an operator sees tempting SQL: where ANALYZE may execute, what time/lock/resource ceilings apply, and which effects cannot be rolled back. Privacy ownership determines whether raw text can leave the database.

What to understand

State p50/p95/p99, throughput, error and database CPU/I/O/WAL/temp objectives plus current baseline.

Name the application route, release, tenant/data-distribution classes and bind-value families without recording secret values.

Choose an isolated restored clone or standby for SELECT; production plan execution is an explicit exception.

List volatile functions, triggers, foreign servers, queues, emails and other effects that make transaction rollback insufficient.

Define correctness tests, one-change policy, canary threshold, observation duration and exact rollback.

System changes

None; creates a restricted investigation record.

Syntax explained

p95: 95th percentile user latency from a histogram, not derivable from mean execution time.
analysis budget: Maximum statement/lock time and host/database overhead allowed during evidence collection.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Query Id{{queryId}}Selected normalized query ID from the named statistics window.

sed -n '1,220p' performance/investigation-{{queryId}}.md

Example output / evidence

Objective: checkout p95 <= 250 ms at 120 requests/s
Window: 2026-07-28T09:00:00Z..10:00:00Z
Database: orders / PostgreSQL 18.4
Safety: isolated restored clone; production collection only
Statement timeout: 15s; lock timeout: 1s
Privacy: redacted plans, restricted evidence
Rollback: revert query release; drop candidate index concurrently after validation

Checkpoint: The investigation has measurable success and containment

grep -E 'Objective|Window|Safety|Statement timeout|Privacy|Rollback' performance/investigation-{{queryId}}.md

Continue whenObjective, representative window/load/values, correctness, privacy, execution environment, timeouts, overhead, canary and rollback are approved.

Stop whenThe request is only 'make it faster', representative parameters are unavailable, raw SQL handling is unapproved, or no safe execution/rollback boundary exists.

If this step fails

Database mean is low while users report high p95.

Likely causeTail latency comes from queues, locks, network, application work or a small parameter class hidden by averages.

Safe checks

Correlate request traces, wait events and parameter classes with query ID/time window.

ResolutionRefine the question to the tail cohort rather than optimizing the overall mean.

No production-like analysis environment exists.

Likely causeRecovery/testing discipline has not produced representative data and configuration.

Safe checks

Inventory available standby/restores and differences from production.

ResolutionLimit production work to collection/nonexecuting EXPLAIN and build a scrubbed physical restore before ANALYZE.

Security notes

Do not put literal SQL, binds or customer identifiers in broadly visible investigation titles.

Alternatives

Start with application traces and built-in activity views when restart for pg_stat_statements is not yet approved.

Stop conditions

The request is only 'make it faster', representative parameters are unavailable, raw SQL handling is unapproved, or no safe execution/rollback boundary exists.

verification

Capture version, settings, workload, I/O, waits, and statistics baseline

read-only

Record PostgreSQL 18.4, restart time, active preload modules, query-ID and statistics settings, cache/I/O timing options, database counters, table/index health, active waits, locks, CPU, memory, storage latency, WAL and application deployment markers before changing instrumentation.

Why this step matters

A query plan is influenced by version, configuration, statistics, cache and concurrency. Adding instrumentation before capturing baseline makes overhead impossible to separate. PostgreSQL 18 includes new EXPLAIN details, so plan interpretation must use the actual major/minor documentation. `track_io_timing` improves I/O attribution but calls the system clock and can add overhead on some platforms; it is a measured operational choice. Host saturation or lock contention can make an efficient plan look slow. Record reset/restart/deployment markers so before/after samples represent comparable states.

What to understand

Record exact server/package version, postmaster start, config sources and pending restart.

Capture database transactions, blocks, temp, deadlocks/checksum failures and reset timestamps.

Capture table analyze/vacuum times, live/dead tuples and index scan/fetch statistics.

Sample `pg_stat_activity` waits and `pg_locks`, plus host CPU run queue, memory pressure and device latency.

Record current application release, traffic, jobs, maintenance and known incidents for the same UTC window.

System changes

None; stores a protected baseline snapshot.

Syntax explained

track_io_timing: Adds block read/write timing to statistics and plans with platform-dependent overhead.
pending_restart: Shows settings whose requested value is not active until restart.

Command

sudo -u postgres psql -X -c "SELECT version(),pg_postmaster_start_time(); SELECT name,setting,unit,pending_restart FROM pg_settings WHERE name IN ('shared_preload_libraries','compute_query_id','track_io_timing','track_wal_io_timing','default_statistics_target','shared_buffers','work_mem','effective_cache_size');"

Example output / evidence

PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1)
pg_postmaster_start_time = 2026-07-21 06:00:11+00
shared_preload_libraries | pgaudit
compute_query_id         | auto
track_io_timing          | on
track_wal_io_timing      | on
default_statistics_target| 100

Checkpoint: The pre-instrumentation state is reproducible

sudo -u postgres psql -XAtqc "SELECT current_setting('server_version'),pg_postmaster_start_time(),stats_reset FROM pg_stat_database WHERE datname='{{databaseName}}';"

Continue whenVersion, restart/reset, configuration, app release, traffic, waits, locks, table/index, I/O/WAL and host baseline share one UTC window.

Stop whenA restart/reset/deployment/maintenance event splits the sample, server is already saturated, or production and test configuration cannot be compared.

If this step fails

Statistics reset time is newer than the intended window.

Likely causeSomeone reset cumulative counters or the server restarted.

Safe checks

Inspect reset functions audit, postmaster time, deployment and monitoring gaps.

ResolutionStart a new named representative collection window; do not splice incomparable counters.

Storage latency is high for all queries.

Likely causeHost/storage pressure rather than one query plan may be dominant.

Safe checks

Compare pg_stat_io, device latency, queue depth, checkpoint and backup activity.

ResolutionResolve systemic I/O contention or include it in controlled reproduction before query changes.

Security notes

Baseline process/query views may expose SQL and client addresses; restrict and redact artifacts.

Alternatives

Use existing observability snapshots if they preserve exact settings/reset/deployment context.

Stop conditions

A restart/reset/deployment/maintenance event splits the sample, server is already saturated, or production and test configuration cannot be compared.

config

Preload pg_stat_statements and restart deliberately

caution

Add `pg_stat_statements` to the existing comma-separated `shared_preload_libraries` list without removing modules, set `compute_query_id=auto`, validate effective configuration, schedule a controlled restart, and verify postmaster start time and loaded setting after health checks.

Why this step matters

`pg_stat_statements` allocates shared memory and hooks planning/execution at server start, so it cannot be activated by creating the extension or reloading configuration. The setting is a list; overwriting it can silently remove auditing or another critical module. A restart interrupts connections and resets some operational context, so baseline, maintenance window, failover and rollback are required. `compute_query_id=auto` lets core generate identifiers when a module needs them. Verify the live setting and library behavior after restart; a valid text file is not proof the new postmaster loaded it.

What to understand

Inventory every existing preload module, its order/dependencies, owner and rollback before editing.

Write a dedicated include, inspect `pg_file_settings.error/applied` and `pg_settings.pending_restart`.

Measure shared memory/headroom and module overhead in staging; preserve the previous include.

Drain or fail over according to service policy, restart once, and run connection/replication/archive/application health checks.

Verify live `SHOW shared_preload_libraries`, query-ID setting, postmaster start and logs for module errors.

System changes

Restarts PostgreSQL and allocates shared state for pg_stat_statements.

Syntax explained

shared_preload_libraries: Server-start list of modules; preserve every approved existing entry.
compute_query_id=auto: Enables core query IDs when required by pg_stat_statements.

File /etc/postgresql/18/main/conf.d/60-observe.conf

Configuration

shared_preload_libraries = 'pgaudit,pg_stat_statements'
compute_query_id = auto

Command

sudo -u postgres psql -X -c "SELECT sourcefile,sourceline,name,setting,applied,error FROM pg_file_settings WHERE name IN ('shared_preload_libraries','compute_query_id');" && sudo systemctl restart postgresql@18-main

Example output / evidence

sourcefile                                      | line | name                     | setting                 | applied | error
/etc/postgresql/18/main/conf.d/60-observe.conf | 1    | shared_preload_libraries | 'pgaudit,pg_stat_statements' | t   |
/etc/postgresql/18/main/conf.d/60-observe.conf | 2    | compute_query_id           | auto                    | t       |
PostgreSQL 18.4 health checks PASS
Postmaster restarted 2026-07-28 16:04:22+00

Checkpoint: The new postmaster loaded all approved modules

sudo -u postgres psql -XAtqc "SHOW shared_preload_libraries; SHOW compute_query_id; SELECT pg_postmaster_start_time();"

Continue whenExisting modules plus pg_stat_statements are live, query IDs auto/on, restart health passes, and rollback configuration remains available.

Stop whenAny preload module disappears, config errors exist, restart/failover is unapproved, shared memory is unsafe, or post-restart health/replication/archive fails.

If this step fails

PostgreSQL fails to start with library not found.

Likely causeThe contrib package/version/path is absent or incompatible.

Safe checks

Inspect package files, exact server version and journal without initializing data.

ResolutionRestore previous preload list, restart service, install the matching official package, and retry in a new window.

Audit behavior disappears after restart.

Likely causeThe existing audit module was overwritten or failed ordering/configuration.

Safe checks

Compare live preload list, logs and audit canary with baseline.

ResolutionRollback immediately and reconstruct the full reviewed list before another attempt.

Security notes

Preload configuration controls in-process native code; only install official, version-matched packages.

Alternatives

Collect activity/application metrics until a restart window exists; extension creation alone is not a workaround.

Stop conditions

Any preload module disappears, config errors exist, restart/failover is unapproved, shared memory is unsafe, or post-restart health/replication/archive fails.

config

Create the extension per database and restrict observability access

caution

Create `pg_stat_statements` in each approved database with a controlled owner/schema, test the view, create a dedicated login/group for observability, and grant only the statistics visibility required by privacy policy. Keep reset functions and superuser authority out of routine access.

Why this step matters

The shared module tracks across the cluster, but SQL objects are installed per database. A query against the wrong database can make a healthy deployment look empty. The view exposes performance counters broadly according to grants, but text for statements executed by other users is protected unless the viewer is superuser or has `pg_read_all_stats`. That built-in role also exposes other statistics and should be granted only to a monitored operations identity after privacy review. Resetting statistics destroys the observation window, so routine dashboards should not own reset execution.

What to understand

Inventory all databases and enable only those in the approved observability scope.

Record extension version and ownership after every PostgreSQL/contrib update.

Use a group role with no login and a separate short-lived operator/service login.

Test same-user, other-user, superuser and observer text visibility with synthetic queries.

Audit extension DDL, grants and calls to reset functions; retain reset timestamps in dashboards.

System changes

Creates extension objects in the selected database and optionally grants a restricted statistics role.

Syntax explained

CREATE EXTENSION: Installs view/functions in the current database; does not perform shared preload.
pg_read_all_stats: Built-in role that can see broader statistics and other users' query text; grant sparingly.

Configuration

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.

sudo -u postgres psql -X -v ON_ERROR_STOP=1 -d {{databaseName}} -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" -c "SELECT extname,extversion FROM pg_extension WHERE extname='pg_stat_statements';"

Example output / evidence

CREATE EXTENSION
       extname       | extversion
---------------------+------------
 pg_stat_statements  | 1.11
(1 row)
PASS observer sees aggregate counters
PASS observer cannot reset statistics
PASS unprivileged application cannot read other users' query text

Checkpoint: Metrics are useful without exposing SQL broadly

sudo -u postgres psql -X -d {{databaseName}} -c "SELECT extversion FROM pg_extension WHERE extname='pg_stat_statements'; SELECT dealloc,stats_reset FROM pg_stat_statements_info;"

Continue whenCorrect extension version in approved databases, observer access matches policy, application lacks cross-user text and reset capability, grants are audited.

Stop whenExtension is created in wrong database/schema, unprivileged users see protected text, observer can reset/write, or grant scope lacks privacy approval.

If this step fails

Extension exists in postgres database but application view is missing.

Likely causeExtensions are per database and were created only in the maintenance database.

Safe checks

Connect explicitly to each scoped database and inspect pg_extension.

ResolutionCreate it through the controlled migration in each approved database.

Observer receives more privileges than expected.

Likely causeBuilt-in/group role inheritance includes unrelated monitoring or object rights.

Safe checks

Inspect recursive role membership and test negative SQL/object operations.

ResolutionCreate a narrower role/view or remove memberships; do not compensate with application-level hiding.

Security notes

Query-text visibility is privileged data access and should be logged, reviewed and time-limited.

Alternatives

Expose a security-definer-free redacted aggregate view that omits query text and sensitive dimensions.

Stop conditions

Extension is created in wrong database/schema, unprivileged users see protected text, observer can reset/write, or grant scope lacks privacy approval.

config

Choose tracking scope, capacity, persistence, planning, and utility behavior

caution

Set `pg_stat_statements.max`, `track`, `track_utility`, `save`, and `track_planning` from measured workload and overhead. Start with top-level execution tracking and planning disabled unless planning cost is the question; monitor deallocations before increasing shared memory.

Why this step matters

Tracking every nested statement can multiply entry churn, especially with procedural code. Planning counters are valuable when planning latency matters, but concurrent updates to planning statistics can add contention under many identical queries, so upstream documentation advises considering cost. `max` controls distinct entries in shared memory and requires restart; too low causes deallocation and loss of low-frequency families, while too high consumes memory. `save=on` persists statistics across clean shutdown, which helps continuity but can mix observation windows unless reset/restart markers remain visible. Utility text may contain sensitive DDL or identifiers.

What to understand

Measure distinct entry count and `dealloc` under peak representative query diversity.

Use `track=top` initially; enable `all` only for a defined nested-function question and limited window.

Leave planning tracking off unless planning time is material; measure overhead before/after under concurrency.

Decide whether utility commands belong in collection given privacy and operational value.

Document which settings need restart, reload or session change and test persistence/reset semantics.

System changes

Configures collection breadth, memory capacity and persistence; max changes require restart.

Syntax explained

track=top: Tracks statements issued directly by clients, not nested calls inside functions.
track_planning=off: Avoids planning-stat update overhead until planning is an explicit investigation.
max: Maximum normalized statement entries retained in shared memory.

File /etc/postgresql/18/main/conf.d/61-pg-stat-statements.conf

Configuration

pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = on
pg_stat_statements.save = on
pg_stat_statements.track_planning = off

Command

sudo -u postgres psql -X -c "SELECT name,setting,unit,context,pending_restart FROM pg_settings WHERE name LIKE 'pg_stat_statements.%' ORDER BY name; SELECT * FROM pg_stat_statements_info;"

Example output / evidence

pg_stat_statements.max            | 10000 | | postmaster | f
pg_stat_statements.save           | on    | | sighup     | f
pg_stat_statements.track          | top   | | superuser  | f
pg_stat_statements.track_planning | off   | | superuser  | f
pg_stat_statements.track_utility  | on    | | superuser  | f
dealloc |          stats_reset
--------+-------------------------------
      0 | 2026-07-28 16:04:23+00

Checkpoint: Tracking captures the question within an overhead budget

sudo -u postgres psql -X -d {{databaseName}} -c "SELECT count(*) entries FROM pg_stat_statements; SELECT dealloc,stats_reset FROM pg_stat_statements_info;"

Continue whenScope/privacy approved, entries fit with low deallocation, overhead benchmark passes, and planning/utility choices match the question.

Stop whenDeallocations erase important workload, memory/restart is unsafe, planning/nested tracking overhead breaches budget, or utility text violates policy.

If this step fails

Dealloc increases continuously despite modest query volume.

Likely causeGenerated SQL changes identifiers/structure, search_path or database/user dimensions create many entries, or max is too low.

Safe checks

Rank entry churn, inspect normalized shapes and application query generation without exporting raw text.

ResolutionParameterize/fix generated SQL first or size max from measured diversity and memory.

CPU rises after enabling track_planning.

Likely causeHigh-frequency planning-stat synchronization adds contention.

Safe checks

Disable in a controlled comparison and inspect total_plan_time need.

ResolutionKeep planning tracking off except for bounded diagnostic windows if benefit does not justify overhead.

Security notes

Utility and nested statement collection expands sensitive text surface; use the minimum scope.

Alternatives

Use a short-lived session/application trace for planning questions instead of global planning tracking.

Stop conditions

Deallocations erase important workload, memory/restart is unsafe, planning/nested tracking overhead breaches budget, or utility text violates policy.

instruction

Collect a named representative window without casual resets

read-only

Record `stats_reset`, application release and workload counters, then observe a complete peak or batch interval. Avoid resetting shared statistics unless all stakeholders approve; if reset is required for a controlled benchmark, capture before state, scope reset to the chosen query/user/database where possible, and mark dashboards.

Why this step matters

Cumulative statistics gain meaning from a known interval and workload. A reset improves arithmetic convenience but destroys history for every team sharing the instance unless narrowly scoped. PostgreSQL 18 reset functions can target identifiers, yet even targeted resets change observability and must be audited. A window should include normal cache state, representative bind distribution, concurrency, background jobs and maintenance. Capture numerator and denominator: total time without calls, or calls without traffic, cannot prioritize user impact. Application histogram percentiles and release markers remain separate but correlated evidence.

What to understand

Record pg_stat_statements_info reset/dealloc and database/table statistics reset before and after.

Choose a complete peak hour, daily job or repeatable load test and record request/transaction throughput.

Mark deployments, ANALYZE/VACUUM, backups, checkpoints, incidents and host changes during the interval.

Capture application percentiles/errors and query-ID mapping without logging raw binds.

If resetting, obtain approval, export baseline securely and use the narrowest supported reset identifiers.

System changes

Normally none; an approved reset would destructively clear selected cumulative performance history.

Syntax explained

stats_reset: Timestamp anchoring aggregate counters; every comparison must respect it.
pg_stat_statements_reset: Clears selected/all statement counters and is not a harmless read operation.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.

sudo -u postgres psql -X -d {{databaseName}} -c "SELECT stats_reset,dealloc FROM pg_stat_statements_info; SELECT now(),xact_commit,blks_read,blks_hit,temp_bytes,deadlocks,stats_reset FROM pg_stat_database WHERE datname=current_database();"

Example output / evidence

stats_reset = 2026-07-28 16:04:23+00
dealloc = 0
window_start = 2026-07-28 17:00:00+00
application_release = orders-api-2026.07.28-2
requests = 431827
checkout_p95_ms = 418
database CPU = 71%; temp_bytes delta = 38 GB

Checkpoint: The sample represents one understood workload

test -s performance/window-{{queryId}}.json && jq -e '.requests>0 and .stats_reset < .window_start and .deployments_marked==true' performance/window-{{queryId}}.json

Continue whenWindow boundaries, reset/dealloc, traffic, releases, jobs, database/host load and application percentiles are recorded and comparable.

Stop whenReset/deployment/incident splits the window, workload is not representative, cache warmness differs, or raw bind values would need unsafe capture.

If this step fails

A colleague reset all statement statistics mid-window.

Likely causeReset privilege/process is too broad or ownership is unclear.

Safe checks

Record exact reset time/audit and preserve unaffected application metrics.

ResolutionDiscard the database interval, tighten reset authority, and start a new named window.

Load-test query mix differs from production.

Likely causeFixture, parameter distribution, concurrency or background activity is unrealistic.

Safe checks

Compare route/query-ID call shares and data histograms between environments.

ResolutionCorrect the workload/data model before using its plan or timing to choose a change.

Security notes

Store bind distributions as classified buckets/hashed cohorts, not raw customer values.

Alternatives

Use counter deltas without reset by snapshotting at exact start/end.

Stop conditions

Reset/deployment/incident splits the window, workload is not representative, cache warmness differs, or raw bind values would need unsafe capture.

verification

Rank normalized query families by impact and resource signature

read-only

Query `pg_stat_statements` for the named database and observation window, calculating total and mean execution/planning time, calls, rows, block hits/reads, block read/write time, temp blocks, WAL bytes, and variability. Rank separately by total load, per-call latency, I/O, spill and WAL rather than one universal score.

Why this step matters

The most expensive query depends on the objective. A fast query called millions of times may dominate CPU; a rare 1.5-second report may dominate user tail; a spill-heavy statement consumes temporary storage; a write may generate costly WAL. `total_exec_time` is cumulative database execution, not end-user latency. `mean` and standard deviation describe distribution coarsely but are not p95. Rows and calls provide scale. Block hits are not free, and reads need I/O timing/context. Query text is representative and protected; use query ID in broad tickets and keep redacted text in restricted evidence.

What to understand

Create separate top lists for total_exec_time, mean/max, calls, shared reads/time, temp bytes/blocks, WAL and planning.

Normalize values per call and per returned/affected row where meaningful; watch loops at plan stage.

Join database/user names through OIDs only for authorized observers and distinguish top-level statements.

Correlate query-ID call share with application route/release and request histogram cohort.

Inspect `pg_stat_statements_info.dealloc` so missing low-frequency entries are not mistaken for absence.

System changes

None; reads cumulative statistics and stores a redacted ranked snapshot.

Syntax explained

total_exec_time: Aggregate executor time across calls; useful for total database load.
mean/stddev_exec_time: Average and variability, not request percentiles.
temp_blks_written/wal_bytes: Signals spills and write amplification that elapsed time alone hides.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.

sudo -u postgres psql -X -d {{databaseName}} -P pager=off -c "SELECT queryid,calls,round(total_exec_time::numeric,1) total_ms,round(mean_exec_time::numeric,2) mean_ms,round(stddev_exec_time::numeric,2) stddev_ms,rows,shared_blks_hit,shared_blks_read,temp_blks_written,wal_bytes,left(query,120) query FROM pg_stat_statements WHERE dbid=(SELECT oid FROM pg_database WHERE datname=current_database()) ORDER BY total_exec_time DESC LIMIT 15;"

Example output / evidence

       queryid        | calls | total_ms | mean_ms | stddev_ms | rows  | shared_blks_hit | shared_blks_read | temp_blks_written | wal_bytes | query
----------------------+-------+----------+---------+-----------+-------+-----------------+------------------+-------------------+-----------+----------------------------
-6131348924739121057  | 48210 | 621488.7 |   12.89 |     48.21 | 48210 |        84201912 |          1382190 |            920441 |         0 | SELECT o.id, ... WHERE ...
 2841029917712285540  |   118 | 183900.2 | 1558.48 |    112.30 |   118 |         2201180 |           941202 |                 0 |         0 | SELECT report_month...

Checkpoint: The selected query matches the stated objective

jq -e '.queryid=="{{queryId}}" and .window_minutes=={{observationMinutes}} and .selection_reason!="" and .raw_query_public==false' performance/candidate-{{queryId}}.json

Continue whenCandidate has query ID, restricted redacted text, calls/rows/time/I/O/temp/WAL context, application mapping and objective-specific selection reason.

Stop whenSelection relies on one cumulative average, reset/deallocation invalidates ranking, query mapping is uncertain, or text/export violates privacy.

If this step fails

The slow query is absent from top total time.

Likely causeIt is rare, recently introduced, evicted, another database/user/query ID, or visible only in tail traces.

Safe checks

Rank by max/mean, inspect dealloc/reset and correlate trace query ID/database/user.

ResolutionUse the objective-specific list and a fresh representative window, not only total-time ranking.

One normalized row combines unexpectedly different latency.

Likely causeParameter skew or generic/custom plan behavior creates multiple performance cohorts under one query ID.

Safe checks

Compare parameter buckets in traces and capture safe plans for representative high/low selectivity.

ResolutionAnalyze each parameter class without logging raw sensitive values and consider plan-cache/query design.

Security notes

Export query ID and redacted structure by default; raw representative text stays in restricted storage.

Alternatives

Use a secured monitoring view that exposes metrics and queryid without query text.

Stop conditions

Selection relies on one cumulative average, reset/deallocation invalidates ranking, query mapping is uncertain, or text/export violates privacy.

verification

Correlate the query with waits, blockers, tables, indexes, and releases

read-only

During a representative occurrence, inspect `pg_stat_activity`, `pg_locks`, blocking PIDs, wait events and transaction age; map the redacted query to tables and indexes; capture table/index statistics, bloat indicators and last ANALYZE/VACUUM; and correlate the application route, release and parameter cohort.

Why this step matters

A high duration can be execution work, lock wait, client backpressure, I/O, transaction context or concurrency. A plan captured when unblocked does not explain a lock incident. `pg_stat_activity` is current and transient, so collect bounded samples with UTC context rather than continuous raw query logging. `pg_blocking_pids` identifies blockers, but terminating them without understanding business work can cause rollback and retry storms. Object statistics reveal stale ANALYZE, dead tuples and unused indexes but are cumulative and resettable. Application release and parameter cohort connect the database family to a user-visible cause.

What to understand

Sample activity/waits several times during the event; distinguish active CPU from wait_event and client states.

Build a blocker tree with transaction age, user/application/client and lock mode/object, redacting SQL.

Map query relations to `pg_stat_user_tables`, `pg_stat_user_indexes`, `pg_statio_*`, constraints and statistics.

Check last analyze/autovacuum, estimated versus actual row counts later, and schema migration timestamps.

Correlate with application trace, release, route, pool saturation, parameter class and host I/O.

System changes

None; reads transient/cumulative views and application metadata.

Syntax explained

wait_event_type/wait_event: Current reason a backend is waiting; null does not prove idle or healthy.
pg_blocking_pids: Returns process IDs blocking a backend according to PostgreSQL lock manager.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.Query Id{{queryId}}Selected normalized query ID from the named statistics window.

sudo -u postgres psql -X -d {{databaseName}} -P pager=off -c "SELECT pid,application_name,state,wait_event_type,wait_event,xact_start,query_start,pg_blocking_pids(pid),query_id FROM pg_stat_activity WHERE query_id={{queryId}};"

Example output / evidence

 pid  | application_name | state  | wait_event_type | wait_event | xact_start | query_start | pg_blocking_pids |       query_id
------+------------------+--------+-----------------+------------+------------+-------------+------------------+----------------------
18422 | orders-api       | active | IO              | DataFileRead|            | 17:42:11.2  | {}               | -6131348924739121057
Object stats: orders last_analyze=2026-07-20, n_live_tup=84211902, n_dead_tup=5118201
Index stats: orders_customer_created_idx idx_scan=0

Checkpoint: The latency mechanism is classified before plan changes

jq -e '.mechanism|IN("cpu","io","lock","temp","planning","client","mixed")' performance/candidate-{{queryId}}.json

Continue whenCandidate has wait/blocker evidence, object/statistics health, application/release/parameter mapping and a classified mechanism or explicit uncertainty.

Stop whenAn unresolved blocker/system saturation dominates, query/object mapping is wrong, or collected current SQL/client data violates privacy.

If this step fails

The query is slow only when another transaction is open.

Likely causeLock contention, not its standalone scan/join plan, dominates.

Safe checks

Build blocker tree and inspect owner/transaction purpose and retry behavior.

ResolutionFix transaction scope/order or conflicting workflow; do not add an index solely from an unblocked plan.

No active sample captures the short slow query.

Likely causePolling interval is longer than execution and tail events are sparse.

Safe checks

Correlate tracing, log_min_duration_statement/auto_explain options under privacy review, or a controlled reproduction.

ResolutionUse sampled tracing or bounded diagnostic logging rather than globally logging all raw SQL.

Security notes

Activity and lock views can expose query text, users and client addresses; export only redacted evidence.

Alternatives

Use application trace spans carrying queryid and wait-safe metadata instead of frequent raw activity snapshots.

Stop conditions

An unresolved blocker/system saturation dominates, query/object mapping is wrong, or collected current SQL/client data violates privacy.

verification

Capture a non-executing estimated plan first

read-only

Retrieve the reviewed SQL into a mode-0600 file, substitute only synthetic or approved representative values in an isolated session, set a safe search path and role, and run ordinary `EXPLAIN (VERBOSE, COSTS, SETTINGS, FORMAT JSON)` without ANALYZE. For prepared-style placeholders, use `GENERIC_PLAN` when supported to inspect a generic plan without execution.

Why this step matters

Ordinary EXPLAIN asks the planner for a plan but does not execute the target statement, making it the first safe structural inspection. Planning still parses, rewrites, acquires metadata locks and may run planner support functions, so it is not zero-impact, but it avoids executor data access and DML effects. Representative values matter because selectivity can choose different custom plans. `GENERIC_PLAN` permits placeholders and intentionally ignores values; it cannot be combined with ANALYZE. Capture JSON for machine comparison and text for human review, along with effective settings, schema definition, statistics timestamps and parameter cohort.

What to understand

Obtain SQL from the application/repository or restricted view and redact before evidence export; do not reconstruct from screenshots.

Use exact role and controlled search_path because privileges, RLS and name resolution can change behavior.

Capture schema/index definitions and statistics metadata in the same evidence revision.

Run plans for representative high/low selectivity classes and label custom versus generic context.

Keep ANALYZE absent and confirm no volatile/external behavior was invoked before moving to actual execution.

System changes

No target statement execution; writes a restricted plan artifact outside the database.

Syntax explained

FORMAT JSON: Produces structured plan data suitable for redacted diff and assertions.
SETTINGS: Shows nondefault planner-relevant settings that affected plan selection.
GENERIC_PLAN: Plans with parameter placeholders without values and cannot be combined with ANALYZE.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.Query Id{{queryId}}Selected normalized query ID from the named statistics window.

sudo -u postgres psql -X -d {{databaseName}} -v ON_ERROR_STOP=1 -c "SET ROLE performance_observer; SET search_path=app,pg_catalog; EXPLAIN (VERBOSE, COSTS, SETTINGS, FORMAT JSON) SELECT o.id FROM app.orders o WHERE o.customer_id = 424242 AND o.created_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00' ORDER BY o.created_at DESC LIMIT 50;" > /secure-staging/estimated-{{queryId}}.json

Example output / evidence

[
  {
    "Plan": {
      "Node Type": "Limit",
      "Plan Rows": 50,
      "Plans": [{
        "Node Type": "Index Scan",
        "Index Name": "orders_customer_created_idx",
        "Plan Rows": 47,
        "Index Cond": "(customer_id = 424242)"
      }]
    },
    "Settings": {"search_path": "app, pg_catalog"}
  }
]

Checkpoint: A reproducible plan exists without target execution

jq -e '.[0].Plan and .[0].Settings' /secure-staging/estimated-{{queryId}}.json && stat -c '%a %U' /secure-staging/estimated-{{queryId}}.json

Continue whenPlan matches exact SQL/role/search_path/schema/statistics/parameter class, ANALYZE absent, artifact protected/redacted, and no database/external effect occurs.

Stop whenSQL/role/parameters are uncertain, file contains sensitive values with broad access, planner calls unsafe functions, or an execution keyword was added.

If this step fails

Estimated plan differs between psql and application.

Likely causeRole, search_path, prepared generic/custom plan, parameters, transaction, settings or schema differs.

Safe checks

Capture application session settings and plan-cache behavior with the same release.

ResolutionReproduce exact execution context before interpreting the plan.

EXPLAIN blocks waiting for a lock.

Likely causePlanning needs relation metadata locks conflicting with DDL.

Safe checks

Inspect blockers and lock_timeout without terminating unknown work.

ResolutionCancel at the bounded lock timeout and retry in the safe environment after DDL completes.

Security notes

Plans expose schema, predicates and object names; use mode 0600, redaction and short retention.

Alternatives

Use EXPLAIN GENERIC_PLAN for placeholders when value-specific execution is not yet approved.

Stop conditions

SQL/role/parameters are uncertain, file contains sensitive values with broad access, planner calls unsafe functions, or an execution keyword was added.

command

Execute SELECT with ANALYZE, BUFFERS, WAL, SETTINGS, and bounded overhead

caution

On a scrubbed production-like clone or approved read-only standby, start a read-only transaction, set local statement and lock timeouts plus application name, then run `EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY, TIMING OFF, FORMAT JSON)` with a representative parameter. Monitor host and cancel if limits breach.

Why this step matters

`ANALYZE` executes the SELECT. A SELECT can read millions of blocks, sort to temp, wait on locks, call volatile functions or foreign servers and affect caches/replay conflicts. Use the safest representative environment and explicit authorization. A read-only transaction blocks ordinary writes but not every external effect from functions; review SQL first. `TIMING OFF` reduces per-node clock overhead while retaining actual rows and total execution; use timing on only when node timing is necessary and overhead measured. PostgreSQL 18 includes buffer details with ANALYZE, but explicit BUFFERS keeps intent clear and portable within the guide.

What to understand

Prefer a scrubbed restored clone. A standby is read-only but analysis can cause recovery conflicts and lag; monitor both.

Set LOCAL timeouts/application name within a transaction so settings cannot leak into pooled sessions.

Use representative bind cohort, exact role/search_path and warm/cold-cache conditions labelled separately.

Collect JSON, host CPU/I/O, temp usage, standby replay lag and cancellation evidence; never rely on one timing run.

Repeat a small controlled number and compare rows/buffers rather than treating instrumentation latency as application p95.

System changes

Executes a potentially expensive SELECT and changes caches/temporary I/O; transaction itself rolls back.

Syntax explained

ANALYZE: Actually executes the statement and reports actual rows/loops and execution duration.
BUFFERS/WAL: Reports block and WAL activity; SELECT usually has no target WAL but functions may generate it.
TIMING OFF: Avoids per-node clock calls while preserving actual row counts and total execution time.

Command

Fill variables0/4 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.Statement Timeout{{statementTimeout}}Session-local ceiling for controlled plan execution.Lock Timeout{{lockTimeout}}Session-local maximum wait for a database lock.Query Id{{queryId}}Selected normalized query ID from the named statistics window.

sudo -u postgres psql -X -d {{databaseName}} -v ON_ERROR_STOP=1 <<'SQL'
BEGIN READ ONLY;
SET LOCAL statement_timeout='{{statementTimeout}}';
SET LOCAL lock_timeout='{{lockTimeout}}';
SET LOCAL application_name='explain-review-{{queryId}}';
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY, TIMING OFF, FORMAT JSON)
SELECT o.id FROM app.orders o WHERE o.customer_id=424242 AND o.created_at>=TIMESTAMPTZ '2026-07-01 00:00:00+00' ORDER BY o.created_at DESC LIMIT 50;
ROLLBACK;
SQL

Example output / evidence

Index Scan using orders_customer_created_idx on app.orders o
  (cost=0.57..186.40 rows=47 width=16)
  (actual rows=50 loops=1)
  Index Cond: (customer_id = 424242)
  Buffers: shared hit=38 read=14
Planning Time: 0.812 ms
Execution Time: 6.944 ms
ROLLBACK

Checkpoint: Actual SELECT evidence stayed within its safety budget

jq -e '.[0].Plan["Actual Loops"]>=1 and .[0]["Execution Time"]' /secure-staging/analyze-{{queryId}}.json

Continue whenStatement completed/canceled within limits, no external effects, resource/replay thresholds safe, actual rows/loops/buffers/settings captured for representative cohort.

Stop whenSQL calls volatile/external effects, timeout/lock/resource/temp/WAL/replay threshold breaches, environment/parameters differ, or artifact leaks sensitive data.

If this step fails

SELECT is canceled by recovery conflict on standby.

Likely causeLong analysis conflicts with WAL replay cleanup/locks.

Safe checks

Inspect pg_stat_database_conflicts, replay lag and query duration.

ResolutionMove analysis to an isolated restore; do not extend standby delay and compromise HA without review.

First run is much slower than later runs.

Likely causeCold cache and storage reads differ from warmed execution.

Safe checks

Compare buffer read versus hit and device I/O for labelled runs.

ResolutionReport both relevant cache states and reproduce the one matching production objective.

Security notes

Do not send JSON plans to third-party visualizers; redact and inspect locally.

Alternatives

Use ordinary EXPLAIN only when execution risk cannot be contained.

Stop conditions

SQL calls volatile/external effects, timeout/lock/resource/temp/WAL/replay threshold breaches, environment/parameters differ, or artifact leaks sensitive data.

warning

Analyze DML only inside a proven rollback boundary

danger

For INSERT, UPDATE, DELETE or MERGE, prefer an isolated restored clone. If a production-like transactional test is explicitly approved, inspect every trigger/function/foreign effect, use synthetic target rows, start `BEGIN`, set local timeouts, run EXPLAIN ANALYZE, verify the transaction remains open and affected rows are only the fixture, then issue and verify `ROLLBACK`.

Why this step matters

Upstream PostgreSQL documentation explicitly warns that EXPLAIN ANALYZE executes DML and recommends `BEGIN ... ROLLBACK` when data effects must be discarded. That protects transactional database changes, locks at rollback, and ordinary triggers whose work remains in the same transaction. It cannot undo an extension or function that sends email, performs HTTP, writes an external system/file, commits through another connection, or invokes a nontransactional side effect. It also generates load, WAL and locks before rollback. Therefore a restored isolated clone is the default and a transaction is only one proven boundary, not a sandbox.

What to understand

Inspect `pg_trigger`, function volatility/language/source ownership, foreign tables, rules and extensions in the statement path.

Use a dedicated synthetic fixture whose identifiers cannot match business rows and assert target cardinality before execution.

Set local statement/lock/idle-in-transaction timeouts and application name; keep an independent cancel observer.

Keep psql `ON_ERROR_STOP`, verify transaction status and issue ROLLBACK even after plan capture; close session afterward.

Prove fixture unchanged and inspect audit/external systems; record generated WAL/locks/replication impact.

System changes

Actually executes DML, triggers, locks and WAL; intended database changes are rolled back only if all execution remains transactional.

Syntax explained

BEGIN ... ROLLBACK: Discards transactional database effects; does not reverse external or nontransactional effects.
WAL: Shows WAL generated by executed DML even though the transaction later aborts.

Command

Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sudo -u postgres psql -X -d {{databaseName}} -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
SET LOCAL statement_timeout='{{statementTimeout}}';
SET LOCAL lock_timeout='{{lockTimeout}}';
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY, TIMING OFF)
UPDATE performance_fixture.orders SET reviewed_at=clock_timestamp() WHERE id=900000001;
SELECT txid_current_if_assigned() IS NOT NULL AS transaction_open;
ROLLBACK;
SELECT reviewed_at IS NULL AS rollback_verified FROM performance_fixture.orders WHERE id=900000001;
SQL

Example output / evidence

Update on orders  (actual rows=0 loops=1)
  WAL: records=3 bytes=214
  -> Index Scan using orders_pkey (actual rows=1 loops=1)
     Index Cond: (id = 900000001)
Planning Time: 0.244 ms
Execution Time: 0.481 ms
transaction_open
 t
ROLLBACK
rollback_verified
 t

Checkpoint: DML analysis changed no business or external state

sudo -u postgres psql -XAtqc "SELECT reviewed_at IS NULL FROM performance_fixture.orders WHERE id=900000001;"

Continue whenOnly synthetic row targeted, transaction rollback confirmed, fixture unchanged, no external effects, time/lock/resource limits respected and plan protected.

Stop whenAny trigger/function/FDW/extension can cause external effects, target cardinality is uncertain, production business rows are involved, or rollback verification is absent.

If this step fails

Statement affects more rows than estimated/expected.

Likely causePredicate/fixture mismatch or stale statistics caused broader execution.

Safe checks

Cancel if possible, ensure transaction stays open and ROLLBACK; inspect fixture/predicate on isolated clone.

ResolutionDo not repeat until target row assertion and representative statistics are corrected.

An external webhook fires despite rollback.

Likely causeA trigger/function performed a nontransactional external action.

Safe checks

Stop analysis, preserve audit and reconcile the external system.

ResolutionTreat as incident and move all future execution to a network-isolated restore with the effect blocked.

Security notes

Never place real secret/customer literals in DML plan artifacts; use synthetic fixtures and redaction.

Alternatives

Use ordinary EXPLAIN on production and EXPLAIN ANALYZE only on an isolated physical restore.

Stop conditions

Any trigger/function/FDW/extension can cause external effects, target cardinality is uncertain, production business rows are involved, or rollback verification is absent.

instruction

Interpret rows, loops, buffers, I/O, spills, WAL, and settings together

read-only

For every important node, compare estimated rows with actual rows multiplied by loops, identify where rows are discarded, distinguish shared hits from reads and temporary I/O, inspect sort/hash memory and batches, join order/method, parallel workers, WAL and planner settings, then write one falsifiable root-cause hypothesis.

Why this step matters

Plan tuning fails when operators focus on the word `Seq Scan` or the largest cost. A sequential scan may be optimal for much of a table; an index scan repeated hundreds of thousands of loops can be worse. Estimated rows are per loop in many nodes, so compare with actual rows and loops correctly. Buffer hits avoid physical reads but still represent work. Temporary blocks show spills caused by plan shape, cardinality and memory context; increasing global `work_mem` multiplies across operations and sessions. WAL matters for writes and index creation. Settings explain why a plan differs, while planner costs are not milliseconds.

What to understand

Trace from the first major estimate divergence because later join/sort choices depend on earlier cardinality.

Multiply actual rows by loops and inspect rows removed by filter/join to find wasted work.

Separate shared hit/read/dirtied/written, local and temp blocks; correlate I/O timing and device state.

Inspect sort method, disk amount, hash batches, workers launched versus planned and skew across workers.

Compare custom/generic parameter cohorts and effective settings before blaming one access method.

System changes

None; creates a redacted interpretation and hypothesis.

Syntax explained

actual rows × loops: Total tuple flow through a repeatedly executed node.
rows removed: Work performed then discarded by filters or joins.
temp blocks: Temporary-file I/O from spills or materialization; not fixed safely by global memory alone.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Query Id{{queryId}}Selected normalized query ID from the named statistics window.

./operations/summarize-explain /secure-staging/analyze-{{queryId}}.json --show-estimate-error --show-buffers --show-temp --show-wal --show-settings

Example output / evidence

Node: Nested Loop
estimated rows total: 48
actual rows total: 182044 (loops accounted)
estimate error: 3792.6x
inner index loops: 182044
shared hit/read: 1,442,819 / 218,442
temp written: 920,441 blocks
Hypothesis: correlated predicates are underestimated; hash/sort spills and repeated inner lookups dominate

Checkpoint: One evidence-backed mechanism explains the objective

jq -e '.hypothesis!="" and .evidence.estimate_error and .evidence.buffers and .alternatives_rejected|length>0' performance/hypothesis-{{queryId}}.json

Continue whenHypothesis names exact node/mechanism, estimate/loop/buffer/temp/WAL/settings evidence and competing explanations, linked to user/resource objective.

Stop whenConclusion is only 'sequential scan bad', loops are ignored, costs treated as milliseconds, cache/test context differs, or evidence cannot explain user symptom.

If this step fails

Plan shows high buffer hits but low storage reads.

Likely causeCPU, memory bandwidth, repeated loops or cache work dominates rather than physical I/O.

Safe checks

Inspect tuple flow, loops, CPU and shared hits per call.

ResolutionReduce repeated work/query shape or cardinality error; do not buy storage based on hits.

Increasing work_mem removes spill in test but risks production.

Likely causeThe value applies per sort/hash operation and can multiply by sessions/parallel workers.

Safe checks

Estimate concurrent operations and memory envelope under representative load.

ResolutionPrefer query/session-local bounded setting or plan/query fix and load-test memory before rollout.

Security notes

Redacted plan still reveals architecture; restrict and expire it after remediation evidence retention.

Alternatives

Use local visualization tooling on redacted JSON, never a public upload.

Stop conditions

Conclusion is only 'sequential scan bad', loops are ignored, costs treated as milliseconds, cache/test context differs, or evidence cannot explain user symptom.

decision

Choose and stage one reversible fix

caution

Select the smallest change matching evidence: refresh or improve statistics, rewrite the query, add a narrowly justified index, change data model, or use a session-scoped planner/resource setting. Validate correctness, lock duration, build/storage/WAL/write cost and rollback. Never globally disable planner methods as the first fix.

Why this step matters

The first large estimate error often justifies better statistics before an index. Extended statistics can model cross-column dependencies and most-common-value combinations, but they do not change data and require ANALYZE to populate. Query rewrites may improve selectivity or eliminate repeated work but need correctness tests. Indexes consume storage/cache, slow writes, generate WAL and require build/drop locking analysis; `CREATE INDEX CONCURRENTLY` reduces blocking but performs more work, can leave invalid indexes on failure, and cannot run inside a transaction block. Global planner switches hide evidence and can regress unrelated queries.

What to understand

Map hypothesis to one mechanism and list rejected alternatives; do not combine stats, index and config in one test.

For statistics, choose columns/expressions and target from observed skew/correlation, then ANALYZE and inspect new estimates.

For query rewrite, assert identical results across edge cases, NULLs, ordering, concurrency and parameter cohorts.

For index, estimate size/build/WAL/write/vacuum/cache/replica impact and use approved concurrent procedure with invalid-index cleanup.

For settings, prefer transaction/session/application scope and capacity model; avoid permanent global changes from one plan.

System changes

Example creates extended statistics and runs ANALYZE; other selected fixes have their own reviewed impacts.

Syntax explained

dependencies: Captures functional-dependency statistics between columns for selectivity estimates.
mcv: Captures multicolumn common value combinations and frequencies.
ANALYZE: Samples table data to populate planner statistics and can consume I/O/CPU.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Database Name{{databaseName}}Approved database in which the extension and analysis are used.

sudo -u postgres psql -X -d {{databaseName}} -v ON_ERROR_STOP=1 -c "CREATE STATISTICS orders_customer_status_dep (dependencies, mcv) ON customer_id,status FROM app.orders;" -c "ANALYZE app.orders;"

Example output / evidence

CREATE STATISTICS
ANALYZE
Extended statistics: orders_customer_status_dep
Kinds: dependencies, mcv
ANALYZE duration: 00:00:18
No blocked production sessions in rehearsal

Checkpoint: The staged change targets one measured cause and can be undone

sudo -u postgres psql -X -d {{databaseName}} -c "SELECT statistics_name,attnames,kinds FROM pg_stats_ext WHERE statistics_name='orders_customer_status_dep';"

Continue whenOne change with correctness, new plan, resource/lock/WAL/storage/write analysis, owner, canary and tested rollback; no unrelated global planner override.

Stop whenChange bundles multiple hypotheses, correctness differs, build can block/exhaust storage/WAL, replica lag breaches, invalid index cleanup is absent, or rollback is untested.

If this step fails

Extended statistics do not change estimates.

Likely causeWrong columns/kind, parameter expression mismatch, sample target or another selectivity issue dominates.

Safe checks

Inspect pg_stats_ext data, predicates and per-node estimate error after ANALYZE.

ResolutionDrop the unused statistics or refine the hypothesis; do not stack random statistics objects.

Concurrent index build leaves an invalid index.

Likely causeBuild failed/canceled, uniqueness validation failed or resources were exhausted.

Safe checks

Inspect pg_index.indisvalid/indisready, logs, locks, disk and replicas.

ResolutionFollow approved DROP INDEX CONCURRENTLY cleanup and correct root cause before retry.

Security notes

Query/index/statistics names should not encode customer secrets; restrict DDL authority.

Alternatives

Partition, precompute, cache or redesign the access pattern when no local plan fix meets correctness and cost.

Stop conditions

Change bundles multiple hypotheses, correctness differs, build can block/exhaust storage/WAL, replica lag breaches, invalid index cleanup is absent, or rollback is untested.

verification

Validate correctness and performance, canary, then keep watching

caution

Re-run the same estimated and controlled actual plans for every parameter cohort, compare application percentiles and pg_stat_statements deltas under representative load, assert identical results, inspect CPU/I/O/temp/WAL/locks/storage/write latency/replication, deploy a small canary, and roll back immediately if any gate fails.

Why this step matters

A faster single EXPLAIN run is not a release result. Compare identical role, schema, statistics, parameter cohorts, cache labels and concurrency, then use the application percentile objective over a representative window. Correctness is non-negotiable. Index/statistics/query changes can shift write cost, WAL, vacuum, cache and other query plans. A canary bounds blast radius and provides real workload evidence, while pg_stat_statements query IDs may change after query rewrite and must be mapped carefully. Continue observation through peak/batch/maintenance cycles before declaring success or removing rollback assets.

What to understand

Run deterministic result comparisons for edge cases, NULLs, ordering, concurrency and each selectivity cohort.

Compare estimated/actual rows, loops, buffers, temp, WAL, planning/execution and instrumentation conditions.

Run representative concurrency and capture application p50/p95/p99, errors, throughput and pool state.

Measure host/database CPU/I/O/memory, writes, locks, index size/usage, vacuum, WAL/archive and replication lag.

Canary by application instances/traffic, watch unrelated top queries, then roll out gradually and observe a full workload cycle.

System changes

Runs controlled load and canary traffic; may roll out or revert the reviewed change.

Syntax explained

same observation window: Comparable duration/load/release/reset context for before and after counter deltas.
canary: Small production cohort with explicit automatic/manual rollback thresholds.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Query Id{{queryId}}Selected normalized query ID from the named statistics window.

./operations/compare-query-change --query-id {{queryId}} --before performance/before-{{queryId}}.json --after performance/after-{{queryId}}.json --require-correctness --require-no-regression && ./operations/canary-query-release --percent 5 --duration 30m

Example output / evidence

Correctness fixtures: 184/184 PASS
checkout p95: 418 ms -> 173 ms
query total_exec_time/call: 12.89 ms -> 4.11 ms
shared reads/call: 28.67 -> 3.12
temp blocks/call: 19.09 -> 0
write p95 change: +1.8% (budget <= 5%)
WAL change: +2.1% (budget <= 8%)
replication lag: within 16 MB
Canary 5% PASS; rollout approved

Checkpoint: Users improve without correctness or resource regression

jq -e '.correctness_pass and .target_objective_pass and .resource_gates_pass and .canary_pass and .rollback_tested' performance/after-{{queryId}}.json

Continue whenCorrectness complete, user objective met, plan mechanism improved, all resource/write/replication/unrelated-query gates pass, canary stable, rollback tested.

Stop whenAny result differs, percentile/resource/unrelated query regresses, query-ID comparison is mismatched, canary thresholds breach, or rollback cannot complete safely.

If this step fails

Plan improves but application p95 does not.

Likely causeDatabase statement was not the dominant tail, concurrency/queue/client work dominates, or cohort mapping is wrong.

Safe checks

Compare trace spans, waits, pool/queue and actual query-ID share during canary.

ResolutionRollback unnecessary cost if introduced and investigate the actual tail mechanism.

Target query improves while another top query regresses.

Likely causeCache, index, statistics or global setting changed planner/resource behavior elsewhere.

Safe checks

Compare top-query deltas, plans, cache/I/O and settings before/after.

ResolutionRollback and choose a narrower change or explicitly evaluate the total workload tradeoff.

Security notes

Performance reports should use query IDs and redacted plan summaries, not raw SQL or binds.

Alternatives

Use a feature flag/query-release rollback when DDL rollback would be slower or riskier during canary.

Stop conditions

Any result differs, percentile/resource/unrelated query regresses, query-ID comparison is mismatched, canary thresholds breach, or rollback cannot complete safely.

Finish line

Verification checklist

Extension readiness and privacy

sudo -u postgres psql -X -d {{databaseName}} -c "SHOW shared_preload_libraries; SHOW compute_query_id; SELECT extversion FROM pg_extension WHERE extname='pg_stat_statements'; SELECT dealloc,stats_reset FROM pg_stat_statements_info;"

PostgreSQL 18.4 preloads module, query IDs work, extension exists, deallocations acceptable and text/reset privileges match policy.

Candidate evidence

jq -e '.queryid=="{{queryId}}" and .selection_reason and .mechanism and .raw_query_public==false' performance/candidate-{{queryId}}.json

Query family is selected from a representative window and mapped to the user/resource objective with protected text.

Safe execution record

jq -e '.analyze_executed==true and .timeouts_applied==true and .effects_contained==true and .artifact_redacted==true' performance/after-{{queryId}}.json

ANALYZE execution, overhead, parameters, role/settings, timeouts and containment are explicit; DML rollback/external-effect proof is complete where applicable.

Correctness and canary./operations/compare-query-change --query-id {{queryId}} --require-correctness --require-no-regressionTarget objective improves under representative load with identical results and no resource, write, replication or unrelated-query regression.

Recovery guidance

Common problems and safe checks

`CREATE EXTENSION pg_stat_statements` reports that the module must be loaded via shared_preload_libraries.

Likely causeThe server was not restarted after adding the library, or another configuration source overrides the setting.

Safe checks

Inspect `SHOW shared_preload_libraries`, `pg_file_settings`, pending restart and postmaster start time.

ResolutionCorrect the authoritative configuration and perform the controlled restart; reload is insufficient.

The view exists but contains no statements.

Likely cause`compute_query_id` is disabled, tracking scope excludes calls, extension is queried in the wrong database, or workload has not run since reset.

Safe checks

Inspect settings, extension database, `pg_stat_statements_info`, reset time and a synthetic safe query.

ResolutionEnable query IDs/tracking through reviewed settings and observe a new representative window.

Non-superuser query text is blank or replaced.

Likely causePostgreSQL hides other users' text unless the viewer has `pg_read_all_stats` or equivalent privilege.

Safe checks

Check current_user, role membership and whether aggregate metrics suffice.

ResolutionGrant a dedicated monitored role only if privacy policy requires text; do not grant superuser.

Statistics show frequent deallocations.

Likely cause`pg_stat_statements.max` is too small for normalized query diversity or generated SQL creates excessive unique forms.

Safe checks

Inspect `pg_stat_statements_info.dealloc`, entry count and application query generation.

ResolutionFix pathological query generation or increase max with measured shared-memory/restart impact.

EXPLAIN ANALYZE runs much slower than normal execution.

Likely causePer-node timing instrumentation, cold cache, client/environment differences or concurrent load adds overhead.

Safe checks

Compare `TIMING OFF`, repeated controlled runs, cache/I/O and application latency without instrumentation.

ResolutionUse TIMING OFF for row/buffer evidence when appropriate and report instrumentation conditions; do not treat one run as production latency.

Estimated rows differ greatly from actual rows.

Likely causeStatistics are stale, sample is too small, columns are correlated, data is skewed, expression statistics are absent or parameters are atypical.

Safe checks

Inspect last analyze, `pg_stats`, extended statistics, selected bind values and estimate error per loop.

ResolutionRefresh or improve statistics and retest before forcing an index or planner setting.

A DML EXPLAIN changed data despite a later rollback expectation.

Likely causeThe statement ran outside the transaction, committed internally, or a trigger/function caused an external/nontransactional effect.

Safe checks

Freeze the session, inspect transaction/audit/external systems and preserve plan/query evidence.

ResolutionTreat as an incident, reconcile effects, and move future analysis to an isolated restore; ROLLBACK is not a universal sandbox.

An added index speeds reads but production writes and storage regress.

Likely causeIndex maintenance, WAL, vacuum, cache and build lock/capacity costs were omitted.

Safe checks

Compare write latency, WAL bytes, index size/usage, vacuum, I/O and replication lag.

ResolutionRollback through the approved concurrent/drop procedure if gates fail, then test a narrower query/statistics change.

After the procedure

Alternatives and next steps

Consider these alternatives

Use application tracing and database driver metrics when route-level percentiles and bind distributions are needed; correlate with query ID rather than logging every raw SQL value.
Use `auto_explain` briefly and selectively for hard-to-reproduce slow statements, with strict duration/sample/nested/timing/logging settings and privacy review because it writes plans to server logs.
Use an isolated physical restore for dangerous DML or expensive analysis when a transaction rollback cannot contain triggers, external calls or operational load.

Operate it safely

Build a redacted top-query dashboard with reset/deployment markers, total and per-call time, calls, rows, temp/WAL/block I/O and query-text access auditing.
Create a production-like performance corpus with representative parameter distributions and correctness assertions for the highest-cost query families.
Review `auto_explain`, sampling and application tracing only after privacy, log volume and instrumentation-overhead tests.

Reference

Frequently asked questions

Does pg_stat_statements store every literal?

It generally normalizes constants and aggregates a representative text, but it is not a privacy guarantee. Utility statements, comments, object names and surrounding logs can expose sensitive content.

Does EXPLAIN ANALYZE only inspect a query?

No. ANALYZE executes it. SELECT can be expensive or invoke volatile/external behavior; DML changes data and fires triggers unless safely contained, and external effects may survive rollback.

Can mean_exec_time replace p95?

No. The view exposes aggregate means/min/max/stddev, not request percentile distributions. Use application/tracing histograms and correlate them with query IDs.

Recovery

Rollback

Remove or revert only the one deployed change through its reviewed mechanism, then repeat correctness, plan and workload checks. Query/application changes revert by feature/release rollback. Extended statistics can be dropped after proving no other query depends on them. Indexes use an approved DROP INDEX CONCURRENTLY procedure where required. Session settings end with the transaction/session; global configuration reverts through the managed include and controlled reload/restart. Never reset statistics to hide a regression.

Freeze rollout and preserve before/after query-ID, plan, settings, reset, workload and regression evidence.
Identify the exact change: query release, statistics object/target, index, schema or configuration.
Route new traffic away from the canary or restore the previous application query release.
For an index, inspect validity/dependencies/locks and execute the reviewed concurrent drop where supported.
For extended statistics, verify dependent query plans, then drop the exact object and ANALYZE if the rollback plan requires it.
For configuration, restore the prior managed value at the narrowest scope and reload/restart only as required.
Run correctness, representative plans, application percentiles and resource/replication checks after rollback.
Retain redacted evidence and update the failed hypothesis; do not stack another untested fix.

Evidence

Sources and review

Verified 2026-07-24Review due 2027-01-20

PostgreSQL 18 pg_stat_statementsofficial PostgreSQL 18 EXPLAIN commandofficial PostgreSQL 18 using EXPLAINofficial PostgreSQL 18 monitoring database activityofficial PostgreSQL 18 cumulative statisticsofficial PostgreSQL 18 planner statisticsofficial PostgreSQL 18 routine vacuuming and ANALYZEofficial PostgreSQL 18 indexesofficial PostgreSQL 18 CREATE STATISTICSofficial PostgreSQL 18 CREATE INDEXofficial PostgreSQL 18.4 release notesofficial