PostgreSQL / Advanced: evidence and reliable operations / Lesson 13

Performance investigation and estimate quality

Diagnose a slow dispatch report from query work, estimates and resource waits. Make performance changes with representative evidence instead of universal memory or planner switches.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Separate query inefficiency, estimation error and contention.
Evaluate statistics, plans and bounded workload experiments.

Define the user-visible problem

Capture the exact statement, relevant parameter classes, result contract and latency objective. One customer may own half the shipments while another owns only twenty, so average behavior can hide skew. Measure repeated latency and throughput under representative concurrency, not one warm-cache run. Record server version and data size with the evidence. A query can be logically efficient yet slow because it waits for a lock or storage; reducing its output accidentally is not an acceptable performance improvement.

Begin with a nonexecuting plan

This EXPLAIN estimates a training report counting pending shipments by destination. Compare node cardinalities, filter placement and join methods with known data shape before executing analysis. JSON format is useful for programmatic inspection but does not turn estimates into observed measurements. If the predicate distribution changed after a bulk import, outdated statistics may explain a poor plan. Do not invent cost or timing output for an unpopulated fixture; the example provides a diagnostic pattern and the questions to ask of its real result.

sql Illustrative SQL; expected results are reasoned, not measured.

EXPLAIN (FORMAT JSON)
SELECT destination_id, COUNT(*) AS waiting
FROM quay_lab.shipments
WHERE state = 'pending' AND created_at < timestamptz '2026-10-20 00:00+00'
GROUP BY destination_id;

Inspect estimates only. Actual cardinalities and timings require a representative isolated execution.

Correlated facts can defeat independent estimates

Single-column statistics summarize distributions, but predicates such as warehouse_id and city_id can be strongly correlated. PostgreSQL extended statistics can describe selected dependencies and combinations when the planner supports their use. Collect evidence before creating a statistics object, and remember that collecting or refreshing statistics is a separate maintenance action. Parameter-sensitive plans add another dimension: a generic prepared plan may be reasonable on average and poor for an extreme value. Inspect representative parameters rather than forcing one access path globally.

Observe waits alongside plans

The activity query lists current-database sessions and their wait categories without cancelling anything. wait_event can identify that a backend is waiting, but interpretation depends on state and the particular event. An active backend is not necessarily consuming CPU continuously. Complete query text and other-user details require appropriate monitoring privileges and may be sensitive. Correlate the timestamps with host storage, CPU and application queueing evidence instead of declaring every long statement an indexing failure.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT pid, state, wait_event_type, wait_event, query_start,
 clock_timestamp() - query_start AS query_age
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

The result is live observation if run by an authorized operator; no such execution occurred during authoring.

Budget memory and instrumentation deliberately

work_mem is a limit used by individual sort or hash operations, not a single total allowance for the entire server. Multiple operations, workers and connections can multiply memory demand; hash behavior also has related controls. A universal large value can cause memory pressure even when one query improves. pg_stat_statements can help identify aggregate workload cost when installed and configured, but it is not assumed present here. Accept a change only after correctness, representative latency, write effects, memory and rollback criteria have been checked together.

Common errors and fixes

Planner cost is reported as milliseconds

Why it happens: Relative estimate units were confused with elapsed time.
How to fix it: Label costs and collect executed timing separately.
Prevent it next time: Record the command and metric definitions with every comparison.

A memory increase causes peak-load failures

Why it happens: Per-operation and concurrent allocations were underestimated.
How to fix it: Use the reviewed rollback and model realistic concurrent demand.
Prevent it next time: Tune resource budgets from workload evidence, not copied constants.

One forced plan harms other customers

Why it happens: A skewed parameter distribution was reduced to one sample.
How to fix it: Compare representative parameter classes and plan behavior.
Prevent it next time: Include common and extreme parameter values in acceptance tests.

Check your understanding

1. A query has similar examined work but changes from 20 ms alone to 2 s at peak. What should be investigated first?

Show worked solution

Investigate waits, queueing and resource saturation at matching timestamps. Similar logical work can take much longer while storage, CPU or locks are contested. Reproduce realistic concurrency in an approved isolated environment before proposing global settings. A new index may help some work but does not establish that the bottleneck is missing access structure.

2. An estimate is wrong only when warehouse and city filters are combined. Why consider extended statistics?