Explain_analyze

Chapter 23: Query Execution Plans

Understanding EXPLAIN and ANALYZE

---

23.1 Introduction to Query Execution Plans

When you execute a query, PostgreSQL doesn’t just run it - it creates an execution plan. Understanding this plan is crucial for performance optimization.

                    Query Execution Flow
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                    PostgreSQL Query Processing                      │
    │                                                                      │
    │   ┌──────────────┐                                                 │
    │   │ SQL Query   │                                                  │
    │   └──────┬───────┘                                                 │
    │          │                                                         │
    │          ▼                                                         │
    │   ┌──────────────┐                                                 │
    │   │   Parser    │ → Validates SQL syntax                          │
    │   └──────┬───────┘                                                 │
    │          │                                                         │
    │          ▼                                                         │
    │   ┌──────────────┐                                                 │
    │   │  Rewriter   │ → Applies rules/views                           │
    │   └──────┬───────┘                                                 │
    │          │                                                         │
    │          ▼                                                         │
    │   ┌──────────────┐                                                 │
    │   │  Planner    │ → Creates execution plan                        │
    │   │  (Optimizer)│ → Evaluates multiple plans                      │
    │   └──────┬───────┘                                                 │
    │          │                                                         │
    │          ▼                                                         │
    │   ┌──────────────┐                                                 │
    │   │  Executor   │ → Executes the chosen plan                      │
    │   └──────────────┘                                                 │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

    The Planner/Optimizer decides:
    • Which indexes to use
    • Join order and join method
    • Scan method (sequential vs index)
    • Aggregation method

---

23.2 EXPLAIN Basics

EXPLAIN shows the execution plan without actually executing the query.

-- Sample tables
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    total DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Insert sample data
INSERT INTO users (username, email, status)
SELECT
    'user_' || i,
    'user_' || i || '@example.com',
    CASE WHEN random() > 0.1 THEN 'active' ELSE 'inactive' END
FROM generate_series(1, 10000) i;

INSERT INTO orders (user_id, total, status)
SELECT
    (random() * 9999 + 1)::INTEGER,
    (random() * 1000)::DECIMAL(10,2),
    (ARRAY['pending', 'completed', 'cancelled'])[floor(random()*3)+1]
FROM generate_series(1, 50000);

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE user_id = 100;

-- EXPLAIN with more detail
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE user_id = 100;

---

23.3 Understanding Execution Plan Nodes

                    Common Plan Nodes
    ========================================================================

    Scan Operations:
    ───────────────
    • Seq Scan    → Full table scan
    • Index Scan  → Index-assisted scan, returns rows in index order
    • Index Only Scan → Uses only index, no table access
    • Bitmap Scan → Uses bitmap for multiple index conditions

    Join Operations:
    ───────────────
    • Nested Loop → For each row in outer, scan inner
    • Hash Join   → Builds hash table for inner table
    • Merge Join  → Sorts both, then merges

    Other Operations:
    ───────────────
    • Sort        → In-memory or disk sort
    • Aggregate   → Hash aggregate or group aggregate
    • Limit       → Returns limited rows
    • WindowAgg   → Window function execution
    • CTE Scan    → Common Table Expression scan

Example Plans

-- Sequential Scan (no index)
EXPLAIN
SELECT * FROM users WHERE status = 'active';

-- Index Scan
EXPLAIN
SELECT * FROM users WHERE user_id = 100;

-- Index Only Scan (PostgreSQL 9.2+)
EXPLAIN
SELECT user_id, email FROM users WHERE email LIKE 'user_1%';

-- Bitmap Scan
EXPLAIN
SELECT * FROM users WHERE status = 'active' OR status = 'inactive';

---

23.4 EXPLAIN ANALYZE

EXPLAIN ANALYZE actually executes the query and shows timing information.

-- EXPLAIN ANALYZE example
EXPLAIN (ANALYZE, TIMING, BUFFERS)
SELECT
    u.username,
    COUNT(o.order_id) as order_count,
    SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active'
GROUP BY u.username
ORDER BY total_spent DESC
LIMIT 10;

-- Understanding the output:
/*
 Limit  (cost=.... rows=10 width=...) (actual time=0.045..0.052 rows=10 loops=1)
   Buffers: shared hit=3
   ->  Sort  (cost=.... rows=... width=...) (actual time=0.044..0.050 rows=10 loops=1)
         Sort Key: (sum(o.total))
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=3
         ->  HashAggregate  (cost=.... rows=... width=...) (actual time=0.037..0.040 rows=15 loops=1)
               Group Key: u.username
               Buffers: shared hit=3
               ->  Hash Right Join  (cost=.... rows=... width=...) (actual time=0.018..0.028 rows=100 loops=1)
                     Hash Cond: (o.user_id = u.user_id)
                     Buffers: shared hit=3
                     ->  Seq Scan on orders o  (cost=0.00..10.00 rows=1000 width=...) (actual time=0.003..0.010 rows=1000 loops=1)
                     ->  Hash  (cost=8.00..8.00 rows=100 width=...) (actual time=0.012..0.012 rows=100 width=...) (actual time=0.011..0.011 rows=100 loops=1)
                           ->  Seq Scan on users u  (cost=0.00..8.00 rows=100 width=...) (actual time=0.002..0.006 rows=100 loops=1)
                                 Filter: (status = 'active')
 Planning Time: 0.156 ms
 Execution Time: 0.089 ms
*/

---

23.5 Interpreting Costs

                    Understanding Cost Units
    ========================================================================

    Cost is measured in "cost units" - abstract units representing:
    • Disk page fetches (1 unit = sequential disk page read)
    • CPU operations (smaller units for CPU-intensive tasks)

    Format: cost=startup_cost..total_cost
    ─────────────────────────────────────────

    • startup_cost - cost before first row is returned
    • total_cost   - cost to return all rows

    Cost Values in Output:
    ─────────────────────
    • rows     - estimated number of rows output
    • width    - average row width in bytes
    • actual   - actual values from ANALYZE

    What to Look For:
    ─────────────────
    ✓ Sequential scans on large tables (potential optimization)
    ✓ Nested loops with large outer tables
    ✓ Missing indexes on WHERE/JOIN columns
    ✓ Sorts using disk instead of memory (work_mem)
    ✓ High row estimates vs actual (stale statistics)

---

23.6 Common Performance Issues in Plans

-- Issue: Sequential scan on large table
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'completed';

-- Solution: Add index
CREATE INDEX idx_orders_status ON orders(status);

-- Verify improvement
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE status = 'completed';

-- Issue: Poor join order
EXPLAIN (ANALYZE)
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'inactive';

-- Issue: Missing composite index
EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

-- Solution: Composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

---

23.7 Analyzing Join Performance

-- Nested Loop Join (can be slow with large tables)
EXPLAIN (ANALYZE)
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id <= 100;

-- Hash Join (better for large datasets)
EXPLAIN (ANALYZE)
SELECT u.username, SUM(o.total)
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username;

-- Merge Join (efficient when sorted)
EXPLAIN (ANALYZE)
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id BETWEEN 1000 AND 2000
ORDER BY u.user_id;

---

23.8 Using BUFFERS to Analyze I/O

-- Check buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email LIKE '%example.com';

-- Understanding buffer output:
/*
 BUFFERS: shared hit=5 read=2 dirtied=1
 ─────────────────────────────────────
 • shared hit    - pages found in shared buffer
 • shared read  - pages read from disk
 • shared dirtied - pages modified
 • temp         - pages in temp storage (for sorts)
*/

-- Good vs Bad Buffer Performance:
-- Good:  hit=1000 read=0 (all from cache)
-- Bad:   hit=100 read=900 (mostly disk reads)
-- Mixed: hit=500 read=500 (partial cache)

---

23.9 Checking Statistics

-- View table statistics
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
WHERE relname IN ('users', 'orders')
ORDER BY relname;

-- Check index usage
SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname IN ('users', 'orders')
ORDER BY relname, indexrelname;

-- Check for missing indexes (tables with seq scans)
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC;

---

23.10 Practical Examples

-- Optimize a complex query step by step
-- Original slow query
EXPLAIN (ANALYZE, TIMING)
SELECT
    u.username,
    u.email,
    COUNT(o.order_id) as order_count,
    SUM(o.total) as total_spent,
    AVG(o.total) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= '2024-01-01'
  AND (o.status = 'completed' OR o.status IS NULL)
GROUP BY u.user_id, u.username, u.email
ORDER BY total_spent DESC
LIMIT 20;

-- Step 1: Add indexes
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Step 2: Verify improvement
EXPLAIN (ANALYZE, TIMING)
-- Re-run the query

---

Summary

Command	What It Shows
EXPLAIN	Estimated plan without execution
EXPLAIN ANALYZE	Actual execution time and results
EXPLAIN (BUFFERS)	I/O statistics
EXPLAIN (COSTS)	Cost estimates

---

Next: Chapter 24: Query Optimization Techniques