Engineering problem

PostgreSQL Query Optimization with EXPLAIN

Find expensive PostgreSQL query shapes, compare estimates with actual rows, and test statistics, query rewrites, and indexes against a baseline.

Editorial update

Decision in brief

Start with the SQL that consumes the most total database time or dominates an important endpoint’s tail latency. Capture representative parameter values and inspect its plan. Fix the first meaningful estimate or work mismatch you can reproduce, then compare one change at a time under concurrent load.

Tune one query: Find costly SQL. Inspect the plan. Change one thing.
Use typical parameters and compare before and after under representative concurrent load.

Find the first costly mistake in a query plan

Where available, use pg_stat_statements to find normalized statements by total execution time during a fixed interval. Cross-check those statements with application p95 and p99 traces. The database summary groups executions and gives aggregates, so use traces to find slow individual requests and representative parameter skew.

Inspect plain EXPLAIN before running EXPLAIN (ANALYZE, BUFFERS) on a safe staging read. ANALYZE executes the statement. Compare estimated rows with actual rows and account for loops, filter work, shared blocks and temporary I/O. A sequential scan can be efficient when a query needs a large share of a table. Test common and rare parameter values, especially where the application uses prepared statements.

Observed signalFirst change to testEvidence to keep
Early row estimates far from actual valuesRefresh statistics and consider extended statistics for correlated columns of the same tableEstimates and endpoint latency improve for multiple parameter buckets
Selective predicate still reads many heap pagesTest one index matching the predicate and ordering, or rewrite a mismatched expressionFewer buffers and lower latency within the write and storage budget
Each row is cheap, but the app repeats the queryBatch calls or change the ORM retrieval shapeFewer calls per request with unchanged results
Request slow, execution fast in stagingCheck lock waits, pool queueing and app/database round tripsTime is attributed to the component that actually waits

Two HN checks on benchmarks and specialized indexes

In an HN discussion of an experimental query planner, perrygeo argued that a faster execution plan can still lose once planning time is included and a workload has changing parameters. Measure combined request time and retain the separate planning and execution values where available.

In a separate HN thread about the Biscuit index, out_of_protocol asked for index sizes alongside query timings. Apply the same test to a routine B-tree proposal. Record its on-disk size, update cost and observed query improvement before adding another production index.

Run the same workload before and after one change

Use a staging database with realistic row counts and skew, safe SQL and bounded load. Set pass criteria for result equality, latency, write throughput and index size.

  1. Capture a slow query shape, its call rate and representative parameters from traces. Keep a common case, a slow case and an infrequent case so the following tests cover selectivity shifts.
  2. Measure a baseline with the same concurrency, role, client settings and cache warm-up. Capture estimated and actual rows, loop counts, buffer access, temporary I/O and application timing.
  3. Apply one change, such as updating statistics, adding one index or rewriting a query. Rerun the parameter set and a mixed read-write workload. Compare latency distributions, total database time, results and any write regression.
  4. Approve when the target endpoint meets the latency goal with equal results and other workload metrics within budget. Stop if a parameter class regresses beyond its allowance or the effect disappears on repeat runs.

Related articles