EDB PostgreSQL Associate: Performance Monitoring and Tuning

Reading plans

EXPLAIN shows the estimated plan with costs in arbitrary units (seq_page_cost = 1); EXPLAIN ANALYZE executes the statement and adds actual rows and timing. Hunt for the largest gap between estimated and actual rows - that is a statistics problem, fixed by ANALYZE or ALTER TABLE ... ALTER COLUMN ... SET STATISTICS. Add BUFFERS to see shared hits versus reads. A high count of rows removed by filter suggests a missing or unusable index.

Where to look

• pg_stat_activity: sessions, state, wait_event, long-running queries, idle in transaction.
• pg_stat_statements: normalised query text with calls and total/mean time - the first stop for top offenders.
• pg_stat_user_tables / pg_stat_user_indexes: seq_scan, n_dead_tup, and idx_scan (zero means an unused index you can drop).
• pg_locks with pg_blocking_pids() for lock waits.
• log_min_duration_statement for slow-query logging.

Memory knobs

shared_buffers is the server-wide page cache. work_mem applies per sort or hash node per connection - a query with several sorts can consume several multiples, so raise it cautiously. maintenance_work_mem sizes VACUUM and index builds. effective_cache_size is only a planner hint. Lower random_page_cost to about 1.1 on SSD storage.

Maintenance

Autovacuum reclaims dead tuples for reuse; bloat grows when it falls behind. VACUUM marks space reusable but does not shrink files, while VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock - use pg_repack in production.

Rule of thumb

Fix statistics before adding indexes, and trust only EXPLAIN (ANALYZE, BUFFERS) - a high estimated cost alone never proves a query is slow.