PostgreSQL EXPLAIN ANALYZE: Reading Query Plans and Fixing Slow Queries

-- Always use ANALYZE + BUFFERS in production investigations
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days';

-- ANALYZE: executes the query and shows actual vs estimated rows
-- BUFFERS: shows shared buffer hits vs disk reads (key for I/O diagnosis)
-- FORMAT TEXT: readable tree format (JSON is better for tooling)

-- WARNING: EXPLAIN ANALYZE actually runs the query
-- For destructive queries, wrap in a transaction and rollback:
BEGIN;
EXPLAIN ANALYZE DELETE FROM logs WHERE created_at < '2024-01-01';
ROLLBACK;

-- Example output:
Hash Join  (cost=1234.56..5678.90 rows=42 width=48)
           (actual time=12.345..234.567 rows=38 loops=1)
  Buffers: shared hit=1240 read=892
  ->  Seq Scan on orders  (cost=... rows=15000 ...)
        (actual time=0.012..198.123 rows=14823 loops=1)
        Filter: ((status = 'pending') AND (created_at > ...))
        Rows Removed by Filter: 985177
        Buffers: shared hit=200 read=888
  ->  Hash  (cost=823.00..823.00 rows=30000 ...)
        ->  Seq Scan on customers  ...

-- KEY NUMBERS:
-- cost=1234.56..5678.90  => estimated startup..total cost (planner units)
-- rows=42                => estimated output rows
-- actual time=12..234    => real ms: startup..total
-- actual rows=38         => real rows returned
-- loops=1                => how many times this node executed
-- Buffers shared hit=N   => pages served from cache (fast)
-- Buffers read=N         => pages read from disk (slow)

-- Bad: Seq Scan on orders (rows=1000000)
-- Cause: no index on the filter column, or statistics are stale

-- Fix: create index
CREATE INDEX idx_orders_status_created
    ON orders (status, created_at DESC)
    WHERE status = 'pending';  -- partial index if status has few values

-- Also run ANALYZE if statistics are stale:
ANALYZE orders;

-- Bad: rows=10 (estimate) vs actual rows=94823
-- Cause: stale statistics or highly skewed data

-- Fix 1: update statistics
ANALYZE VERBOSE orders;

-- Fix 2: increase statistics target for skewed columns
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- Fix 3: use extended statistics for correlated columns
CREATE STATISTICS orders_status_region ON status, region FROM orders;
ANALYZE orders;

-- Bad: Nested Loop with inner Seq Scan (O(n*m) complexity)
-- Nested Loop  (actual rows=50000 loops=50000)  -- 2.5B iterations!

-- Fix: ensure join columns are indexed
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Or force a Hash Join for large datasets:
SET enable_nestloop = OFF;  -- temporary, for testing only

-- Bad: Buffers read=50000 (50K disk pages)
-- Cause: working set larger than shared_buffers, or cold cache

-- Check buffer pool hit rate:
SELECT
    sum(blks_hit)   AS cache_hits,
    sum(blks_read)  AS disk_reads,
    ROUND(sum(blks_hit)::numeric /
          NULLIF(sum(blks_hit) + sum(blks_read), 0) * 100, 2) AS hit_pct
FROM pg_stat_database
WHERE datname = current_database();
-- Target: hit_pct > 99%

-- Fix: increase shared_buffers (typically 25% of RAM)
-- shared_buffers = 8GB  # in postgresql.conf

-- Bad: Sort  (cost=12345..12400 rows=50000)  using external merge (disk)
-- Cause: ORDER BY on unindexed column, or work_mem too low

-- Fix 1: index the sort column
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);

-- Fix 2: increase work_mem for sort-heavy sessions
SET work_mem = '256MB';  -- session-level, not global