PostgreSQL Performance Analysis in Practice

Performance issues often show up as slow APIs or high database CPU/IO. This page shows how to use built-in tools to find the cause and fix it.

1. EXPLAIN execution plans

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... ;

• Large, expensive Seq Scan → consider an index or rewriting the query.
• Bitmap Heap Scan with many heap fetches → check Buffers: shared hit/read for disk I/O.
• Nested Loop with a large inner table and no good index → consider increasing work_mem so the planner can choose Hash Join, or add an index on the join key.

Focus on actual time, rows, and Buffers; when actual rows are much higher than estimated, run ANALYZE or add an expression index as needed.

2. pg_stat for object activity

• pg_stat_user_tables: seq_scan, idx_scan — high sequential scan counts may mean missing or unused indexes.
• pg_stat_user_indexes: idx_scan shows which indexes are used; drop or review indexes that are never used.
• pg_stat_statements (extension): Sort by total_time, calls, rows to find heavy or odd queries, then use EXPLAIN on them.

3. Slow-query logging

In postgresql.conf:

log_min_duration_statement = 1000   # log statements running longer than 1s

Use logs to see which statements are slow, then reproduce and run EXPLAIN to add indexes or change SQL.

4. Quick actions

Symptom	Action
Many seq scans	Add indexes on WHERE/JOIN columns or run ANALYZE
Sort/hash disk usage	Increase work_mem (per session or per query)
Dead tuples / bloat	Tune autovacuum or run VACUUM/ANALYZE on the table
Single slow query	EXPLAIN (ANALYZE, BUFFERS), then fix index or SQL
Too many connections	Use a connection pool and limit max_connections

See the PostgreSQL docs for performance tips, monitoring, and routine maintenance.