Diagnose slow PostgreSQL queries with EXPLAIN ANALYZE

Inspect the problem before adding an index

Adding an index by intuition may not improve a slow order list. The problem could be excessive rows, sorting, joins or resource contention. EXPLAIN shows the planner’s chosen path; EXPLAIN ANALYZE executes the query and adds actual observations. That distinction matters: ANALYZE is not a display-only operation. Our example uses SELECT against a disposable table so learning to interpret plans does not accidentally modify live business records.

Planner cost is not elapsed milliseconds. It is an estimate used to compare alternatives. Actual time, rows and loops describe execution. A repeatedly executed node needs interpretation alongside its loop count. BUFFERS adds evidence about cached and read blocks; a shared hit is not a physical disk read. Follow the nodes producing data and investigate large gaps between estimated and actual rows as possible signs of stale statistics or a poorly estimated condition.

Read the plan in its data context

The sample filters by customer_id and orders recent records by created_at. An appropriately ordered composite index can support that pattern, but it is not a universal prescription. Customer distribution, table size and other queries affect the result. Sequential scans can remain sensible for small tables or low-selectivity predicates. To understand the planner, use representative volume and distribution. Forcing an index scan is not an acceptance criterion.

Refresh statistics after loading or substantially changing data. Record the actual parameters: a large customer and an occasional buyer create different workloads. Prepared statements may use generic or custom plans, which can matter during diagnosis. Selecting only required columns and bounding results also reduces database work and transferred data. A logical query improvement can precede any infrastructure change and should not be overlooked while searching for a perfect index.

Change one variable at a time and keep the query, data and workload comparable. Do not attribute the difference between a cold first run and a warm subsequent run entirely to the index. Compare application latency, buffers, processed rows and write overhead. Every new index adds storage and maintenance work. On a frequently updated table, the read benefit must justify those costs. Include a rollback approach rather than accumulating indexes indefinitely.

CREATE TEMP TABLE demo_orders AS
SELECT g AS id, (g % 1000)::int AS customer_id,
       timestamptz '2026-01-01 00:00:00+00'
         + g * interval '1 minute' AS created_at
FROM generate_series(1,100000) AS g;
ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,created_at FROM demo_orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;

CREATE INDEX ON demo_orders (customer_id,created_at DESC);
ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,created_at FROM demo_orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;

Make one measurable change

A useful report includes the problematic query, observed cause, plans before and after and a measured result. Coordinate lock and build-time constraints before production deployment. The instructional CREATE INDEX is not a complete online migration procedure. If latency remains unchanged, revise the hypothesis instead of adding more indexes. The aim is reliable performance with acceptable operational costs, not a plan filled with index scans.

Practical explanations and recommendations are Liyan Knowledge editorial analysis.Sources: PostgreSQL 18 — Using EXPLAIN · PostgreSQL — Multicolumn indexes · PostgreSQL — ANALYZE

This Liyan Knowledge article is an editorial synthesis based on the original source.View original source