Query planning and performance tuning

Reading EXPLAIN ANALYZE, planner statistics, autovacuum tuning, connection pooling and prepared statements under load.

Reading a plan

explain (analyze, buffers, verbose, settings, format text)
select c.name, count(*)
from customer c
join invoice i on i.customer_id = c.id
where i.issued_at >= now() - interval '90 days'
group by c.name
order by 2 desc
limit 20;

-- rows=estimate vs actual rows=N is how you spot a bad estimate
--   Seq Scan on invoice  (cost=0.00..1834.00 rows=100 width=8)
--                        (actual time=0.015..12.4 rows=48213 loops=1)
--   Buffers: shared hit=180 read=612

-- look for these first
--   Nested Loop with high loops      -> missing index on the inner side
--   Sort with external merge Disk    -> work_mem too small for this query
--   Rows Removed by Filter: 900000   -> index or predicate problem
--   Heap Fetches: 50000              -> visibility map needs vacuum

Statistics and autovacuum

alter table invoice alter column status set statistics 500;
create statistics invoice_customer_status (dependencies)
  on customer_id, status from invoice;
analyze invoice;

select relname, last_autovacuum, last_autoanalyze, n_dead_tup, n_mod_since_analyze
from pg_stat_user_tables order by n_dead_tup desc limit 10;

-- per-table autovacuum tuning for a hot, heavily updated table
alter table queue_item set (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

• The default autovacuum_vacuum_scale_factor of 0.2 means a table must grow 20 percent in dead tuples before it is vacuumed - far too late for a large, hot table.
• Extended statistics let the planner understand correlation between columns; without them, a query filtering on two correlated columns gets a wildly wrong estimate.
• Increasing statistics target helps for heavily skewed columns, at the cost of a slightly slower analyze.
• Bloat from long-running transactions is worse than any tuning problem: an idle-in-transaction session holds back the xmin horizon and blocks cleanup.

Pooling and prepared statements

-- find long-running and idle-in-transaction sessions
select pid, now() - xact_start as xact_age, state, wait_event_type, wait_event,
       left(query, 80) as query
from pg_stat_activity
where state <> 'idle'
order by xact_start asc;

select pg_terminate_backend(pid) from pg_stat_activity
where state = 'idle in transaction' and xact_start < now() - interval '5 minutes';

-- the statement-level view of where time goes
create extension if not exists pg_stat_statements;
select calls, round(total_exec_time) as total_ms, round(mean_exec_time, 2) as mean_ms,
       rows, left(query, 70) as query
from pg_stat_statements
order by total_exec_time desc limit 15;

• PostgreSQL forks a process per connection. Fifty connections per application instance across ten instances is 500 processes competing for memory - use PgBouncer in transaction mode.
• In transaction pooling mode, server-side prepared statements do not survive across transactions unless the pooler manages them. Many drivers need prepareThreshold=0 or the pooler's own caching.
• Set statement_timeout and idle_in_transaction_session_timeout per role so a runaway query cannot pin resources indefinitely.
• Tune by total time, not mean: pg_stat_statements ordered by total execution time finds the queries worth fixing.

alter role app_write set statement_timeout = '30s';
alter role app_write set idle_in_transaction_session_timeout = '60s';
alter role web_report set work_mem = '64MB';
alter role web_report set statement_timeout = '5min';

FAQ

Related

Indexes: B-tree, GIN, GiST, partial and expression Next steps: the PostgreSQL ecosystem and migrations

Last refreshed 2026-09-18.