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

-- 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 stats after tuning
SELECT pg_stat_statements_reset();

EXPLAIN ANALYZE (reading execution plans)

-- Always use ANALYZE BUFFERS for real cost data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '7 days';

/* Reading the output:
   Seq Scan          → full table scan, may need index
   Index Scan        → good, using index
   Index Only Scan   → best, covering index (no heap access)
   Nested Loop       → OK for small datasets; bad for large
   Hash Join         → good for large joins
   Merge Join        → good for pre-sorted data

   Key numbers:
   - actual time=START..END ms per row
   - rows=N vs rows=N (estimated vs actual — big diff = stale stats)
   - Buffers: hit=N read=N  (high 'read' = cache miss → index opportunity)
*/