PostgreSQL Query Optimizer

Master EXPLAIN ANALYZE, choose the right index type, find slow queries with pg_stat_statements, and systematically eliminate bottlenecks in PostgreSQL.

EXPLAIN ANALYZE — Reading the Output

-- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for full detail
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 20;

-- Reading the output:
-- "Seq Scan on users" → full table scan → may need index on created_at
-- "cost=0.00..150.25" → estimated cost units (not ms)
-- "actual time=0.042..12.340" → real ms for first row..all rows
-- "rows=1234 width=64" → estimated rows
-- "actual rows=1289 loops=1" → real rows (compare to estimate)
-- "Buffers: shared hit=45 read=12" → 45 from cache, 12 from disk
-- "Planning Time: 0.5ms" → query planning overhead
-- "Execution Time: 12.8ms" → total execution time

Index Type Selection Guide

Common Index Patterns

-- Partial index (smaller, faster for frequent filter patterns)
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';   -- only indexes pending rows

-- Functional / expression index
-- BAD: WHERE LOWER(email) = $1 won't use regular index on email
CREATE UNIQUE INDEX idx_users_email_lower
  ON users (LOWER(email));    -- now LOWER(email) queries use this index

-- Composite index — column order matters
-- Serves: WHERE user_id = ? AND created_at > ?
-- Serves: WHERE user_id = ? (leftmost prefix)
-- Does NOT serve: WHERE created_at > ? (skips leftmost column)
CREATE INDEX idx_orders_user_date
  ON orders (user_id, created_at DESC);

-- Covering index (includes extra columns to avoid table lookup)
CREATE INDEX idx_users_email_covering
  ON users (email) INCLUDE (id, name);

-- GIN for JSONB queries
CREATE INDEX idx_products_metadata
  ON products USING GIN (metadata);
-- Enables: WHERE metadata @> '{"category": "electronics"}'

-- Full-text search with pg_trgm (substring/fuzzy search)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING GIN (name gin_trgm_ops);
-- Enables: WHERE name LIKE '%john%'  or ILIKE

Finding Slow Queries with pg_stat_statements

-- Enable (add to postgresql.conf then restart):
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by average execution time
SELECT
  left(query, 100) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Top 10 by total time (most impactful to optimize)
SELECT
  left(query, 100) AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset stats before a new performance test
SELECT pg_stat_statements_reset();

N+1 Query Fix with JSON Aggregation

-- BAD (N+1 pattern): 1 query for users + N queries for orders
-- SELECT * FROM users WHERE active = true;
-- For each user: SELECT * FROM orders WHERE user_id = $1;

-- GOOD: single query with JSON aggregation
SELECT
  u.id,
  u.name,
  u.email,
  COALESCE(json_agg(
    json_build_object(
      'id', o.id,
      'amount', o.total_amount,
      'created_at', o.created_at
    ) ORDER BY o.created_at DESC
  ) FILTER (WHERE o.id IS NOT NULL), '[]') AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name, u.email;

-- Result: each user row includes a JSON array of their orders
-- One round-trip instead of N+1

Optimization Checklist

• Run ANALYZE after large data changes to update planner statistics
• Check for Seq Scan on large tables — add index on WHERE and JOIN columns
• Check estimated vs actual rows — large discrepancy means stale statistics
• Avoid functions on indexed columns — use functional indexes or rewrite predicates
• Use LIMIT for pagination — avoid OFFSET on large tables; use keyset pagination instead
• Index foreign keys — PostgreSQL does not auto-index FK columns; always add them manually
• Use connection pooling — PgBouncer or RDS Proxy prevents connection overhead from being the bottleneck