EXPLAIN and Performance

EXPLAIN options

EXPLAIN alone prints the plan the planner chose plus its estimates. ANALYZE actually runs the query and adds measured numbers, which is the only version worth reading when you are debugging.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT c.email, o.total
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at > now() - interval '30 minutes';

EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, or MERGE executes it. There is no dry-run mode. Wrap it:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

Reading a plan

Read it inside out and bottom up. Each indented -> is a child feeding its parent, and the top line's actual time is the whole query.

Nested Loop  (cost=0.72..250.50 rows=31 width=15)
             (actual time=0.005..0.018 rows=29 loops=1)
  Buffers: shared hit=91
  ->  Index Scan using orders_placed_at_idx on orders o
        (cost=0.43..8.97 rows=31 width=12) (actual time=0.002..0.003 rows=29 loops=1)
        Index Cond: (placed_at > (now() - '00:30:00'::interval))
  ->  Index Scan using customers_pkey on customers c
        (cost=0.29..7.79 rows=1 width=19) (actual time=0.000..0.000 rows=1 loops=29)
        Index Cond: (id = o.customer_id)
Planning Time: 0.374 ms
Execution Time: 0.031 ms

Both actual time and actual rows are per loop. Total work is the value times loops. A node showing actual time=0.4 rows=1 loops=50000 did 20 seconds of work, not 0.4 ms, and that is the single most misread line in any plan. (From 18 actual rows is printed with decimals because it is an average across loops.)

Node types

Join choice is not a bug in itself. A nested loop over 30 outer rows with an index on the inner side is the fastest possible plan. A nested loop over 3 million is a disaster.

Diagnosing a bad plan

Compare estimated rows against actual rows at every level. The first node where they diverge by more than about 10x is where the plan went wrong, and everything above it is a consequence.

->  Seq Scan on events  (cost=0.00..21.00 rows=1 width=40)
                        (actual time=0.02..812.4 rows=1204331 loops=1)

Estimated 1, got 1.2 million. The planner then chose a nested loop for the join above, and the query takes minutes. Causes, in the order worth checking:

1. Stale statistics. Run ANALYZE tbl and re-check. This is most of the cases. 2. Correlated columns. The planner multiplies per-column selectivities, so WHERE city = 'Paris' AND country = 'FR' is estimated far too low. Fix with extended statistics. 3. Not enough histogram detail on a skewed column. Raise the per-column statistics target. 4. An expression the planner cannot estimate, for example WHERE md5(x) = $1 or a function without statistics. Add an expression index, which also gets statistics. 5. A jsonb or array containment predicate, where selectivity is close to a guess. 6. Parameter sniffing on a generic plan. Check EXPLAIN (GENERIC_PLAN) and consider plan_cache_mode = force_custom_plan.

ANALYZE orders;                                   -- one table
ALTER TABLE events ALTER COLUMN kind SET STATISTICS 1000;  -- default is 100
ANALYZE events;

-- Teach the planner that city implies country
CREATE STATISTICS cities_corr (dependencies, ndistinct, mcv) ON city, country FROM cities;
ANALYZE cities;
SELECT * FROM pg_stats_ext WHERE statistics_name = 'cities_corr';

default_statistics_target (default 100) sets the histogram and MCV list size for every column. Raise it per column rather than globally, because it makes every ANALYZE slower. CREATE STATISTICS (10+, with mcv from 12) is the fix for correlated columns and nothing else is.

VACUUM, bloat, and autovacuum

Postgres never overwrites a row. An UPDATE writes a new version and leaves the old one dead, so space is reclaimed by vacuum, not by the update. Dead tuples that outnumber live ones are bloat, and they cost every scan.

Autovacuum handles this automatically. It triggers on a table when n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup, which defaults to 50 rows plus 20% of the table. On a large hot table 20% is far too much, so tune it per table.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay    = 2
);

-- Dead tuples and vacuum history, worst first
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Transaction-id age, the number to watch before wraparound
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind = 'r' ORDER BY xid_age DESC LIMIT 10;

-- Table and index sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS heap,
       pg_size_pretty(pg_indexes_size(relid))        AS indexes
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

A long-running transaction or an unused replication slot pins the oldest visible xid, so vacuum cannot remove anything newer, and bloat grows no matter how often it runs. Check pg_stat_activity for old xact_start values and pg_replication_slots for inactive slots first.

pg_stat_statements

The extension that tells you which queries actually cost you time, aggregated by normalized query text. Add it to shared_preload_libraries and restart, then create it.

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 by total time spent, which is what to fix first
SELECT round(total_exec_time)               AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)    AS mean_ms,
       round(stddev_exec_time::numeric, 2)  AS stddev_ms,
       rows,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 100)                     AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not mean_exec_time. A 2 ms query called 5 million times costs more than a 4-second report run nightly. High stddev_exec_time points at a parameter-sensitive plan.

SELECT pg_stat_statements_reset();   -- baseline before a load test

Two more server-wide numbers worth a look. Cache hit ratio below roughly 99% on an OLTP workload usually means shared_buffers is too small, and any temp_files growth means sorts and hashes are spilling, so work_mem is too low.

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       xact_commit, xact_rollback, temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes, deadlocks, stats_reset
FROM pg_stat_database WHERE datname = current_database();

Remember that work_mem is per sort or hash node, not per query and not per connection, so a query with four sorts and eight parallel workers can use many multiples of it. Raise it per session for a known-heavy job rather than globally.