Practical guide

How to find a slow SQL query with EXPLAIN ANALYZE

Do not read only the first line. Find the node where time, rows, or database buffers are being consumed.

25 minutes · PostgreSQL

In short

EXPLAIN estimates; ANALYZE measures

PostgreSQL builds a query plan as a tree of operations. EXPLAIN shows planner estimates; EXPLAIN ANALYZE actually executes the query and adds real time, row counts, and loops.

Be careful with data-changing SQL. An UPDATE or DELETE really runs under ANALYZE. Start with safe EXPLAIN in production and measure writes in a controlled environment.

Prepare

What you need

The plan must match the real query, parameters, and data. An anonymized query with different values may choose another plan.

Exact SQL and typical parameters from a slow request or pg_stat_statements.
Representative data, the same indexes, and PostgreSQL settings similar to production.
A safe way to run a SELECT; analyze writes on a copy or inside a transaction that is safely rolled back.
Baseline duration and context: frequency, concurrency, returned rows, and expected result.

Steps 1 to 3

Read the plan from the actual work

Cost is not milliseconds. For performance, inspect actual time, rows, loops, buffers, and how they multiply.

1. Run the measurement safely

Run EXPLAIN without ANALYZE first. It reveals plan shape without executing the query.
For a safe SELECT, add ANALYZE and BUFFERS. Use FORMAT JSON when a tool will process the plan.
ANALYZE adds overhead and really runs the query. Do not launch an expensive query blindly during peak traffic.
In a test environment, a write can be wrapped in BEGIN and ROLLBACK, but rollback cannot undo trigger side effects outside the database.

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) SELECT ...;

Official PostgreSQL guide to EXPLAIN

2. Find the node doing the most real work

Read the plan from the lowest nodes upward. Every parent consumes rows produced by its children.
Actual time describes one execution of a node and loops says how often it ran. A small time multiplied by many loops can dominate the query.
Look for filtered rows, large sequential scans, repeated inner Nested Loops, expensive Sort nodes, and temporary files.
Shared read means storage I/O and shared hit means cache. Compare warmed and colder cache behavior according to production reality.

actual time × loops; actual rows versus estimated rows; Buffers: shared hit/read

Official EXPLAIN command reference

3. Fix the cause and measure again

When row estimates are far from reality, refresh statistics or consider a higher statistics target for the problematic column.
Add an index only for a concrete filter, join, or ordering. A sequential scan over much of a table can remain correct.
Reduce selected columns and rows, remove unnecessary sorting, or change the join shape. The query itself may be the problem.
After every change, run the same plan with the same parameters and verify result correctness too.

ANALYZE orders;

Official PostgreSQL ANALYZE documentation

Step 4

Compare like with like

One fast execution is not proof. Verify the plan with different typical parameters and realistic concurrency.

Save plans before and after

Compare execution time, rows, loops, buffers, sort method, and temporary files.
```
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;
```
Try different parameter values

A common and a rare value may need different plans. Verify both important cases.
Monitor after deployment

Compare calls, averages, upper percentiles, and total time in pg_stat_statements. Improving one run must not hurt normal traffic.
```
SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
```

When it goes wrong

Common mistakes

Cost looks high but the query is fast

Cost is a relative planner estimate, not milliseconds. Evaluate actual time, buffers, and behavior under load.

Estimated rows are far from actual rows

Refresh statistics and inspect column correlation, data types, and value distribution. A bad estimate can choose the wrong join and scan.

ANALYZE table_name;

The second execution is fast but the first is slow

The second run benefits from cache. Inspect shared read and hit and compare a state that matches production.

EXPLAIN ANALYZE changed data

ANALYZE really executes the query. Test writes on a safe copy or in a rolled-back transaction and account for external side effects.

BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;

Done

The bottleneck is measured, not guessed.

You now evaluate the SQL query by actual rows, loops, and I/O. Start the next optimization the same way: exact query, safe plan, one change, and a new measurement.

Read SQL in the glossary All guides