Diagnose slow queries, index use and bad estimates

Understand the likely causes

• Possible causes include unselective filters, mismatched index expressions, stale or insufficient statistics, correlated columns, generic prepared plans and resource contention. An index that helps reads also adds write and maintenance cost. Removing required joins or authorization predicates can make an incorrect query faster, so establish correctness before treating latency as the only success criterion.

Capture a bounded baseline

Choose representative common and extreme parameter values in an isolated training copy with realistic distribution. Confirm required rows, NULL handling and report grain, then record repeated latency under comparable cache and concurrency conditions. Ask whether the problem is too much work or waiting for otherwise reasonable work. Do not replay production traffic, run load tests against configured services or apply universal planner switches as part of this public diagnostic recipe.

Read the estimated plan first

EXPLAIN without ANALYZE estimates the statement rather than executing its ordinary data retrieval. Check access paths, index conditions versus filters, join order, estimated output rows and sorts. PostgreSQL cost units are not milliseconds. A broad query can correctly choose a sequential scan despite an available index. The training query below contains no invented plan output because cardinalities and choices depend on its actual fixture. If a predicate wraps a column, compare it with the indexed expression and operator support.

EXPLAIN (FORMAT JSON)
SELECT shipment_id, created_at FROM quay_lab.shipments
WHERE customer_id = 7
ORDER BY created_at, shipment_id LIMIT 20;

Use executed analysis only on an approved fixture

On an approved isolated read-only fixture, EXPLAIN (ANALYZE, BUFFERS) can compare actual rows and loops with estimates; it really executes the query and may consume resources. Do not apply it casually to modifying statements. Interpret per-loop averages correctly and check how much input a filter rejects. An estimate mismatch suggests investigating distribution, correlations and statistics, but does not prove that refreshing statistics alone will fix latency. ANALYZE maintenance and new index creation belong to separate reviewed changes.

Check waits before forcing a plan

The read-only activity sample can identify active waits and transaction age. Full visibility requires monitoring privileges; inspect sensitive query text privately. If work is selective but waits dominate, address the evidenced lock or capacity cause through the runbook. If actual work is excessive, test a matching index or semantics-preserving rewrite. Correlated filters may justify extended statistics. A generic prepared plan can behave differently across parameter classes, so a forced key based on one sample can worsen the rest of the workload.

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

Keep it from recurring

• Track high-cost query families and data growth, keep maintenance healthy and review indexes against the complete workload. Document expression and partial-index predicates, parameter types and statistics assumptions. Budget work_mem across operations and concurrent workers rather than copying a large server-wide value.