db-performance

# Skill: Database Performance > **Expertise:** EXPLAIN ANALYZE, index design (partial/covering), pg_stat_statements, autovacuum tuning, PgBouncer sizing. ## When to load When investigating slow queries, designing indexes, tuning PostgreSQL config, or sizing PgBouncer pools. ## Query Analysis with pg_stat_statements ```sql -- Enable (add to postgresql.conf, then restart or reload) -- shared_preload_libraries = 'pg_stat_statements' CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Top 10 slowest queries by total time SELECT left(query, 120) AS query_snippet, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, round(stddev_exec_time::numeric, 2) AS stddev_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; -- Top queries by mean execution time (find worst-per-call) SELECT left(query, 120), calls, round(mean_exec_time::numeric, 2) AS mean_ms, round(rows::numeric / calls, 1) AS rows_per_call FROM pg_stat_statements WHERE calls > 100 -- ignore one-offs ORDER BY mean_exec_time DESC LIMIT 10; -- Queries with high I/O (missing index candidates) SELECT left(query, 120), calls, round(mean_exec_time::numeric, 1) AS mean_ms, shared_blks_read + shared_blks_hit AS total_blocks, round(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS cache_hit_pct FROM pg_stat_statements WHERE calls > 50 ORDER BY shared_blks_read DESC LIMIT 10; -- Reset sta...