26 — Query Optimization & EXPLAIN

-- PLANNER ESTIMATES ONLY — does NOT execute the query
-- cost = startup..total (arbitrary units, NOT milliseconds)
-- rows = estimated row count, width = avg bytes per row
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- EXECUTES the query — shows actual time (ms), actual rows, loops
-- The gap between estimated rows and actual rows reveals stale statistics
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- ADDS I/O breakdown: shared hit (cached) vs read (disk) vs dirtied vs written
-- Tells you if the query is I/O-bound (many reads) or CPU-bound (many hits, high time)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

-- ADDS per-step timing — essential for isolating which node dominates
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE customer_id = 42;

-- JSON output for programmatic parsing / diffing plans in CI
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE customer_id = 42;

-- VERBOSE adds schema-qualified names and full expression output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE customer_id = 42;

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET amount = amount * 1.1 WHERE customer_id = 42;
ROLLBACK;  -- revert the update; the plan + timing are still displayed

EXPLAIN SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'NYC';

                                 QUERY PLAN
──────────────────────────────────────────────────────────────────
 Hash Join                           -- ④ final join: probe orders with customer hash
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o          -- ③ full scan of orders (no filter — all rows)
   ->  Hash                          -- ② build hash table from filtered customers
         ->  Seq Scan on customers c  -- ① scan customers, filter city = 'NYC'
               Filter: (city = 'NYC')

-- Index Scan: for each matching tuple, fetch the heap page directly
--   O(matching_rows × random_page_cost)
--   Best when matching_rows is small (high selectivity)

-- Bitmap Index Scan: build a bitmap of matching tuple locations, then
--   batch-fetch heap pages in physical order (sorted by page number)
--   O(matching_pages × seq_page_cost) — amortizes random I/O
--   Best when matching_rows is moderate (many per page)

-- If the bitmap exceeds work_mem × 8 (in pages), it becomes LOSSY:
--   only page-level granularity is kept → must recheck the condition
--   per heap row. Raise work_mem to avoid this.

-- Nested Loop blowup: if the planner estimates 1 outer row but
-- actual is 10000, the inner is executed 10000 times. If the
-- inner is a Seq Scan, that's 10000 full table scans:

Nested Loop  (rows=1) (actual time=0.1..98000 rows=10000 loops=1)
  -> Index Scan on users  (rows=1) (actual rows=10000)   -- estimate: 1, actual: 10000
  -> Seq Scan on orders   (rows=1) (actual rows=500 loops=10000)  -- 10000 × full scan
         Filter: (customer_id = users.id)
         Rows Removed by Filter: 999995   -- discards nearly the entire table each loop

-- Total inner work: 10000 × 1000000 = 10 billion row examinations
-- Fix: CREATE INDEX ON orders(customer_id) → inner becomes Index Scan, O(log N + matches)
-- AND: ANALYZE users → fix the estimate so the planner picks Hash Join instead

-- "Find the top 10 customers by total spend in Q1 2024, with their email"
SELECT c.id, c.email, c.name, SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.ordered_on >= '2024-01-01'
  AND o.ordered_on <  '2024-04-01'
GROUP BY c.id, c.email, c.name
ORDER BY total_spend DESC
LIMIT 10;
-- Runtime: 45 seconds on 50M orders

EXPLAIN (ANALYZE, BUFFERS) [query above];

 Limit  (cost=3987521.20..3987521.22 rows=10 width=48) (actual time=45210.3..45210.5 rows=10 loops=1)
   Buffers: shared hit=512 read=984321
   ->  GroupAggregate  (cost=3987521.20..4012345.67 rows=1000000 width=48) (actual time=45210.3..45210.4 rows=10 loops=1)
         Group Key: c.id, c.email, c.name
         Buffers: shared hit=512 read=984321
         ->  Sort  (cost=3987521.20..3995678.90 rows=3263072 width=48) (actual time=45100.2..45150.8 rows=3263072 loops=1)
               Sort Key: c.id
               Sort Method: external merge  Disk: 198456kB     -- ← spilled to disk!
               Buffers: shared hit=8 read=984321
               ->  Hash Join  (cost=15432.00..3789001.20 rows=3263072 width=48) (actual time=12.3..44000.5 rows=3263072 loops=1)
                     Hash Cond: (o.customer_id = c.id)
                     Buffers: shared hit=4 read=984321
                     ->  Seq Scan on orders o  (cost=0.00..3765000.00 rows=3263072 width=12) (actual time=0.1..42000.0 rows=3263072 loops=1)
                           Filter: ((ordered_on >= '2024-01-01') AND (ordered_on < '2024-04-01'))
                           Rows Removed by Filter: 46736928
                           Buffers: shared read=984321            -- ← 984321 pages read from disk!
                     ->  Hash  (cost=12345.00..12345.00 rows=100000 width=36) (actual time=11.0..11.0 rows=100000 loops=1)
                           Buckets: 131072  Batches: 1  Memory Usage: 8234kB
                           Buffers: shared hit=4
                           ->  Seq Scan on customers c  (cost=0.00..12345.00 rows=100000 width=36) (actual time=0.1..10.0 rows=100000 loops=1)

-- FIX 1: Add a partial index on the date range (only Q1 2024 orders)
-- This turns the 50M-row seq scan into a targeted index scan
CREATE INDEX idx_orders_q1_2024 ON orders(customer_id, ordered_on)
  INCLUDE (amount)
  WHERE ordered_on >= '2024-01-01' AND ordered_on < '2024-04-01';

-- FIX 2: Raise work_mem for this session to avoid the sort spill
SET work_mem = '256MB';

-- FIX 3: Rewrite using a covering index approach
-- The INCLUDE (amount) makes the index a covering index for this query
-- Now the planner can do an Index Only Scan — no heap fetch needed

EXPLAIN (ANALYZE, BUFFERS) [query above with SET work_mem = '256MB'];

 Limit  (cost=50234.12..50234.14 rows=10 width=48) (actual time=340.5..340.7 rows=10 loops=1)
   Buffers: shared hit=15234 read=0       -- ← all cache hits, 0 disk reads!
   ->  GroupAggregate  (cost=50234.12..50456.78 rows=100000 width=48) (actual time=340.5..340.6 rows=10 loops=1)
         Group Key: c.id, c.email, c.name
         Buffers: shared hit=15234
         ->  Sort  (cost=50234.12..50289.45 rows=22130 width=48) (actual time=320.1..330.2 rows=22130 loops=1)
               Sort Key: c.id
               Sort Method: quicksort  Memory: 2845kB    -- ← in-memory now!
               Buffers: shared hit=15234
               ->  Hash Join  (cost=15432.00..49001.20 rows=22130 width=48) (actual time=12.3..310.0 rows=22130 loops=1)
                     Hash Cond: (o.customer_id = c.id)
                     Buffers: shared hit=15234
                     ->  Index Only Scan using idx_orders_q1_2024 on orders o  (cost=0.42..32000.00 rows=22130 width=12) (actual time=0.2..280.0 rows=22130 loops=1)
                           Index Cond: (ordered_on >= '2024-01-01' AND (ordered_on < '2024-04-01')
                           Heap Fetches: 0                    -- ← covering index, zero heap access
                           Buffers: shared hit=15230
                     ->  Hash  (cost=12345.00..12345.00 rows=100000 width=36) (actual time=11.0..11.0 rows=100000 loops=1)
                           ->  Seq Scan on customers c  (cost=0.00..12345.00 rows=100000 width=36) (actual time=0.1..10.0 rows=100000 loops=1)

-- ❌ WRONG: Adding indexes blindly "because it might help"
CREATE INDEX idx_orders_amount ON orders(amount);
CREATE INDEX idx_orders_amount_customer ON orders(amount, customer_id);
CREATE INDEX idx_orders_amount_date ON orders(amount, ordered_on);
-- None of these help the actual query pattern. Each one slows down
-- every INSERT/UPDATE/DELETE on orders and wastes disk + cache.
-- You've made writes slower for zero read benefit.

-- ✅ RIGHT: Measure first, index the actual bottleneck
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;  -- see which node dominates
-- Identify: Seq Scan on a 50M-row table with a selective filter
-- Then add the ONE index that turns it into an Index Scan
CREATE INDEX idx_orders_customer_date ON orders(customer_id, ordered_on) INCLUDE (amount);
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;  -- verify the improvement

-- ❌ WRONG: join everything, filter at the end
WITH all_orders AS (
  SELECT c.id, c.email, o.amount, o.ordered_on
  FROM customers c JOIN orders o ON c.id = o.customer_id
)
SELECT id, email, SUM(amount) AS total
FROM all_orders
WHERE ordered_on >= '2024-01-01'  -- applied AFTER the full join
GROUP BY id, email;

-- ✅ RIGHT: push the predicate into the join — filter orders first
SELECT c.id, c.email, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
  AND o.ordered_on >= '2024-01-01'  -- filter before join
GROUP BY c.id, c.email;

-- Query: SELECT customer_id, amount FROM orders WHERE customer_id = 42;
-- With a plain index: Index Scan → 1000 heap fetches to get `amount`
CREATE INDEX ON orders(customer_id);

-- With a covering index: Index Only Scan → 0 heap fetches
CREATE INDEX ON orders(customer_id) INCLUDE (amount);
-- The INCLUDE column is stored in the index but not part of the sort key
-- It's smaller than a full composite (customer_id, amount) because
-- amount isn't used for tree navigation, only for covering

-- Partition by month, prune automatically
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id BIGINT,
  amount NUMERIC(10,2),
  ordered_on TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (ordered_on);

CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... etc

-- The planner prunes partitions that can't match the WHERE clause
EXPLAIN SELECT * FROM orders WHERE ordered_on >= '2024-01-15' AND ordered_on < '2024-01-20';
-- → Append
--     -> Seq Scan on orders_2024_01   -- only Jan is scanned
-- Feb, Mar, etc. are not even touched

-- The planner auto-parallelizes large scans/aggregations
-- Tune the worker count per session:
SET max_parallel_workers_per_gather = 4;  -- up to 4 workers per query node
SET min_parallel_table_scan_size = '8MB'; -- minimum table size to parallelize
SET parallel_setup_cost = 100;            -- lower = more eager to parallelize

-- Plan with parallelism:
-- Gather  (workers=4)
--   -> Parallel Seq Scan on orders
--        Filter: (amount > 100)
-- Each worker scans a subset of pages; results are merged at the Gather node
-- Overhead: worker startup + result merging. Not worth it for small tables.

-- Manually collect statistics (autovacuum does this, but sometimes stale)
ANALYZE orders;

-- Analyze a specific column (faster than full table)
ANALYZE orders(customer_id);

-- View stored statistics the planner uses
SELECT attname, n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, null_frac, avg_width
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

-- For skewed distributions (a few values dominate), increase statistics target
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders(customer_id);  -- default is 100; higher = more histogram buckets
-- More buckets = better estimate for skewed data, at the cost of larger pg_statistic

-- Per-session tuning for a heavy analytical query
SET work_mem = '512MB';
SET random_page_cost = 1.1;          -- SSD
SET effective_cache_size = '8GB';    -- hint: 8GB of OS+PG cache available
SET max_parallel_workers_per_gather = 4;
-- Run the query, then reset:
RESET work_mem;
RESET random_page_cost;

Nested Loop  (cost=0.58..12.87 rows=1 width=52) (actual time=0.05..87000.0 rows=5000 loops=1)
  ->  Index Scan using users_email_idx on users  (cost=0.29..8.31 rows=1 width=4) (actual time=0.03..0.04 rows=1 loops=1)
        Index Cond: (email = 'vip@enterprise.com')
  ->  Index Scan using orders_customer_id_idx on orders  (cost=0.29..4.55 rows=1 width=12) (actual time=0.02..17.4 rows=5000 loops=1)
        Index Cond: (customer_id = users.id)