Understanding EXPLAIN Plans

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- Original plan
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
 
-- Result: Hash Join selected (cost=2845.00)
-- Execution Time: 125.432 ms
 
-- Alternative 1: Force Merge Join
SET enable_hashjoin = off;
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
 
-- Result: Merge Join with Sort (cost=3567.00)
-- Execution Time: 178.234 ms
-- Conclusion: Hash Join was better
 
-- Alternative 2: Force Nested Loop
SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
 
-- Result: Nested Loop (cost=45678.00)
-- Execution Time: 12456.789 ms (10x slower!)
-- Conclusion: Nested Loop catastrophic for this query
 
-- Reset to defaults
RESET enable_hashjoin;
RESET enable_mergejoin;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Create comparison baseline
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT p.name, COUNT(s.id) as sales_count
FROM products p
JOIN sales s ON p.id = s.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.name
ORDER BY sales_count DESC
LIMIT 10;
 
-- Store results: 
-- {"Plan": {"Total Cost": 4521.34, "Actual Total Time": 234.567, ...}}
 
-- Test Alternative: CTE instead of direct join
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH recent_sales AS (
    SELECT product_id, COUNT(*) as cnt
    FROM sales
    WHERE sale_date >= '2024-01-01'
    GROUP BY product_id
)
SELECT p.name, rs.cnt
FROM products p
JOIN recent_sales rs ON p.id = rs.product_id
ORDER BY rs.cnt DESC
LIMIT 10;
 
-- Store and compare JSON results programmatically
 
-- Test Alternative: Force Index-based approach
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT p.name, COUNT(s.id) as sales_count
FROM products p
JOIN sales s ON p.id = s.product_id  
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.name
ORDER BY sales_count DESC
LIMIT 10;
 
RESET enable_seqscan;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Why was Seq Scan chosen instead of Index Scan?
 
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'completed';
--  Seq Scan on orders  (cost=0.00..15234.00 rows=85000 width=120)
--    Filter: (status = 'completed')
--    Rows Removed by Filter: 15000
 
-- 85000/100000 = 85% of rows match. Index would be slower!
-- Random I/O for 85K lookups > Sequential read of whole table
 
-- Check statistics driving this decision:
SELECT 
    tablename, 
    attname, 
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats 
WHERE tablename = 'orders' AND attname = 'status';
 
-- Output:
-- most_common_vals: {completed,pending,cancelled}
-- most_common_freqs: {0.85, 0.10, 0.05}
-- Optimizer correctly estimated 85% selectivity
 
-- For the rare value, index IS used:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'cancelled';
--  Index Scan using idx_status on orders (cost=0.29..1523.45 rows=5000)
-- Only 5% of rows, worth using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- PostgreSQL doesn't have traditional hints
-- but pg_hint_plan extension provides them
 
-- Install extension (if available)
CREATE EXTENSION pg_hint_plan;
 
-- Use hints in query
SELECT /*+ SeqScan(orders) */ * FROM orders WHERE id = 1;
SELECT /*+ IndexScan(orders orders_pkey) */ * FROM orders WHERE id = 1;
SELECT /*+ HashJoin(c o) */ * FROM customers c JOIN orders o ON c.id = o.customer_id;
SELECT /*+ NestLoop(c o) */ * FROM customers c JOIN orders o ON c.id = o.customer_id;
SELECT /*+ Leading((c o)) */ * FROM customers c JOIN orders o ON c.id = o.customer_id;
 
-- Without extension: session-level workarounds
SET enable_seqscan = off;  -- Discourage seq scans
SET random_page_cost = 1.1;  -- Make index scans more attractive (SSD adjustment)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- Capture plan baseline (store plan hash or full plan)
CREATE TABLE plan_baselines (
    query_id VARCHAR(64),
    captured_at TIMESTAMP DEFAULT now(),
    plan_hash VARCHAR(64),
    plan_json JSONB,
    execution_time_ms NUMERIC
);
 
-- Capture current plan for important query
INSERT INTO plan_baselines (query_id, plan_hash, plan_json, execution_time_ms)
SELECT 
    'critical_report_query',
    md5(plan::text),
    plan,
    (plan->>'Execution Time')::numeric
FROM (
    SELECT to_json(pg_catalog.pg_stat_statements.query_plan) as plan
    FROM ... -- Capture mechanism varies
) t;
 
-- Periodic comparison to detect drift
-- If plan_hash changes, investigate
 
-- pg_stat_statements for historical query performance
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
SELECT 
    query,
    calls,
    mean_time,
    stddev_time,
    rows
FROM pg_stat_statements
WHERE query LIKE '%orders%customer%'
ORDER BY mean_time DESC;
 
-- Watch for mean_time spikes on known queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- STEP 1: Current problematic plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.category,
    DATE_TRUNC('week', s.sale_date) as week,
    SUM(s.amount) as total_sales
FROM products p
JOIN sales s ON p.id = s.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.category, DATE_TRUNC('week', s.sale_date)
ORDER BY week, total_sales DESC;
 
-- OUTPUT:
--  Sort  (cost=58234.56..58245.67 rows=4444 width=64)
--        (actual time=15234.567..15234.890 rows=520 loops=1)
--    Sort Key: (date_trunc('week', s.sale_date)), (sum(s.amount)) DESC
--    Sort Method: quicksort  Memory: 58kB
--    ->  HashAggregate  (cost=57890.12..57934.56 rows=4444 width=64)
--              (actual time=15232.123..15233.456 rows=520 loops=1)
--          Group Key: p.category, (date_trunc('week', s.sale_date))
--          ->  Hash Join  (cost=325.00..54567.89 rows=444444 width=20)
--                    (actual time=2.345..14890.234 rows=500000 loops=1)
--                Hash Cond: (s.product_id = p.id)
--                ->  Seq Scan on sales s  (cost=0.00..43210.00 rows=1000000 width=16)
--                          (actual time=0.015..8234.567 rows=1000000 loops=1)
--                          Filter: (sale_date >= '2024-01-01')
--                          Rows Removed by Filter: 0
--                ->  Hash  (cost=225.00..225.00 rows=8000 width=12)
--                          (actual time=2.234..2.234 rows=8000 loops=1)
--                      ->  Seq Scan on products p  (rows=8000 loops=1)
--    Buffers: shared hit=15234 read=28456  <- Lots of disk reads!
--  Execution Time: 15238.456 ms  <- 15 SECONDS!
 
-- ANALYSIS:
-- 1. Seq Scan on sales reads entire table (no date index being used)
-- 2. Large buffer read count indicates cold cache / missing index
-- 3. No index on sale_date or composite index available

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- STEP 2: Check what indexes exist
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'sales';
 
-- Result: No index on sale_date!
 
-- STEP 3: Generate alternative with index
CREATE INDEX idx_sales_date ON sales(sale_date);
ANALYZE sales;
 
-- STEP 4: Compare new plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.category,
    DATE_TRUNC('week', s.sale_date) as week,
    SUM(s.amount) as total_sales
FROM products p
JOIN sales s ON p.id = s.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.category, DATE_TRUNC('week', s.sale_date)
ORDER BY week, total_sales DESC;
 
-- NEW OUTPUT:
--  Sort  (cost=8234.56..8245.67 rows=4444 width=64)
--        (actual time=234.567..234.890 rows=520 loops=1)  <- FROM 15s to 235ms!
--    ->  HashAggregate  (cost=7890.12..7934.56 rows=4444 width=64)
--          ->  Hash Join  (cost=325.00..5567.89 rows=444444 width=20)
--                ->  Index Scan using idx_sales_date on sales s  <- NOW USING INDEX
--                      (actual time=0.056..156.234 rows=500000)
--                      Index Cond: (sale_date >= '2024-01-01')
--                ->  Hash  (cost=225.00..225.00 rows=8000 width=12)
--                      ->  Seq Scan on products p
--    Buffers: shared hit=12456 read=234  <- Dramatially fewer disk reads
--  Execution Time: 238.456 ms  <- 64x FASTER!
 
-- COMPARISON SUMMARY:
-- | Metric          | Before    | After     | Improvement |
-- |-----------------|-----------|-----------|-------------|
-- | Execution Time  | 15238 ms  | 238 ms    | 64x         |
-- | Buffer Reads    | 28456     | 234       | 122x        |
-- | Access Method   | Seq Scan  | Idx Scan  | Selective   |
-- | Est. Cost       | 58234     | 8234      | 7x          |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

# Example: Automated plan comparison in CI/CD
import psycopg2
import json
import hashlib
 
def capture_plan(conn, query):
    """Capture execution plan as JSON"""
    with conn.cursor() as cur:
        cur.execute(f"EXPLAIN (FORMAT JSON) {query}")
        return cur.fetchone()[0]
 
def plan_hash(plan):
    """Generate hash of plan structure for comparison"""
    # Normalize plan (remove volatile fields like costs)
    normalized = normalize_plan(plan)
    return hashlib.md5(json.dumps(normalized).encode()).hexdigest()
 
def compare_plans(baseline_hash, current_hash):
    """Compare plan hashes, alert if different"""
    if baseline_hash != current_hash:
        print("⚠️ PLAN CHANGE DETECTED!")
        print(f"Baseline: {baseline_hash}")
        print(f"Current:  {current_hash}")
        return False
    return True
 
# Usage in CI/CD pipeline
critical_queries = [
    "SELECT * FROM orders WHERE customer_id = $1",
    "SELECT COUNT(*) FROM sales WHERE sale_date >= $1",
    # ... more critical queries
]
 
for query in critical_queries:
    current_plan = capture_plan(conn, query)
    current_hash = plan_hash(current_plan)
    
    baseline_hash = load_baseline(query)  # From version control
    
    if not compare_plans(baseline_hash, current_hash):
        # Fail pipeline or alert, depending on policy
        raise Exception(f"Plan regression for: {query[:50]}...")