Reading PostgreSQL EXPLAIN to stop guessing

Performance meeting: "PostgreSQL cannot handle load." Bigger instance discussed. Someone projects EXPLAIN ANALYZE on product search:

Seq Scan on products (cost=0..84291 rows=1 width=120) (actual time=412..412 ms rows=50)

  Filter: (category_id = 7 AND price < 100)

  Rows Removed by Filter: 890000

412 ms for 50 rows — 890,000 rows filtered. Missing composite index (category_id, price). Not a cloud CPU problem: an execution plan problem.

EXPLAIN turns "it's slow" into readable mechanism. Without it, you optimize randomly — or pay for a bigger instance to scan the same full table faster.

Read a plan: essential vocabulary

Key metrics in EXPLAIN ANALYZE:

• actual time start..end per node
• rows estimated vs actual — bad stats → bad plan
• Buffers shared hit/read — warm vs cold cache

Estimated/actual rows gap ×100 → run ANALYZE table before creating a useless index.

Diagnostic workflow (15 min)

1. Enable pg_stat_statements:

``sql SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``

2. Copy guilty query → EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ...
3. Identify most expensive node (time × rows).
4. Hypothesis: index, join rewrite, cursor pagination.
5. Re-measure; compare mean_exec_time under real load.

Common web examples

Heavy OFFSET pagination

SELECT * FROM articles ORDER BY published_at DESC OFFSET 50000 LIMIT 20;

→ Cursor pagination: (published_at, id) < ($last, $id).

*COUNT() marketing dashboard**

Full scan — materialized view or denormalized counter.

JSONB without GIN index

WHERE data->>'status' = 'active' → GIN index or generated indexed column.

Stats and maintenance

• Regular VACUUM ANALYZE with autovacuum tuning.
• Higher default_statistics_target on skewed filter columns.
• After bulk migration: stale stats = temporarily catastrophic plans.

Managed Postgres (RDS, Scaleway, OVH): pg_stat_statements often enableable; EXPLAIN works the same as self-hosted. To choose managed vs VPS, see Managed PostgreSQL.

The peak: plan lies when stats lie — but less than intuition

Here is what "let's upgrade the instance" meetings forget to show.

Team culture: no "slow query" ticket closed without before/after plan — even if the fix is one index line.

Decide and move forward without blind spots

In half a day you can establish PostgreSQL diagnostic culture:

1. Enable pg_stat_statements in production.
2. Analyze top 5 costliest queries with EXPLAIN ANALYZE on staging.
3. Create an index or rewrite the query — measure numeric gain.
4. Run ANALYZE after every bulk data import.
5. Correlate slow app routes with SQL queries via your monitoring tool.

Start with the query that accumulates the most total time — not the one slowest in a single execution. See Database replication and Missing MySQL index for the MySQL parallel.

Frequently asked questions

Difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN estimates the plan without executing. EXPLAIN ANALYZE actually runs the query and shows measured times — use on staging or careful SELECTs, never on mass DELETE in production.

Is Seq Scan always bad?

No. On a small table or when most rows are returned, sequential scan can be optimal. It is a bad sign on a large table with few rows returned and many rows filtered.

How to spot a missing index?

Look for sequential scan with high rows removed by filter and dominant execution time. Create the index, rerun EXPLAIN ANALYZE and compare before/after times.

Is pg_stat_statements enough?

Not alone. pg_stat_statements identifies which queries consume the most aggregated time; EXPLAIN explains why. Both tools complement each other for actionable diagnosis.

Stop guessing why PostgreSQL struggles — let the plan name the guilty line.