Query Optimization with EXPLAIN

You've created indexes, but are they being used? EXPLAIN shows exactly how the database executes your query. Master this and you'll never guess about performance again.

Basic EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Output:

                         QUERY PLAN
-------------------------------------------------------------
 Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=72)
   Index Cond: (email = 'john@example.com'::text)

Let's break down what this means.

Understanding Scan Types

Visual Comparison

EXPLAIN ANALYZE: Real Execution Stats

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Output:

 Index Scan using idx_users_email on users
   (cost=0.42..8.44 rows=1 width=72)
   (actual time=0.025..0.027 rows=1 loops=1)
   Index Cond: (email = 'john@example.com'::text)
 Planning Time: 0.085 ms
 Execution Time: 0.045 ms

Reading the Numbers

Common Issues and Solutions

Issue 1: Seq Scan When Index Exists

EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

 Seq Scan on users  (cost=0.00..20406.00 rows=5000 width=72)
   Filter: (lower(email) = 'john@example.com'::text)

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Issue 2: Index Scan With High Row Count

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';

 Index Scan using idx_orders_status on orders
   (cost=0.42..15234.56 rows=450000 width=120)
   (actual time=0.052..892.341 rows=456123 loops=1)

Issue 3: Wrong Index Chosen

-- Index exists on (user_id, created_at)
-- But query uses different index

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC;

CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);

Issue 4: Outdated Statistics

-- Planner estimates 100 rows, but actually returns 100,000
-- This causes poor plan choices

EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'electronics';

 Seq Scan on products  (cost=0.00..1234.00 rows=100 width=85)
                       (actual rows=98543)

ANALYZE products;
-- or for all tables
ANALYZE;

EXPLAIN Cheat Sheet

-- Basic plan (estimates only)
EXPLAIN SELECT ...;

-- With actual timings (runs the query!)
EXPLAIN ANALYZE SELECT ...;

-- With buffer/IO statistics
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Formatted output options
EXPLAIN (FORMAT JSON) SELECT ...;
EXPLAIN (FORMAT YAML) SELECT ...;

Reading Complex Plans

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id;

 HashAggregate  (cost=2345.67..2400.00 rows=500 width=40)
   Group Key: u.id
   ->  Hash Join  (cost=123.45..2000.00 rows=15000 width=36)
         Hash Cond: (o.user_id = u.id)
         ->  Index Scan using idx_orders_status on orders o
               (cost=0.42..1500.00 rows=15000 width=8)
               Index Cond: (status = 'completed'::text)
         ->  Hash  (cost=100.00..100.00 rows=1000 width=36)
               ->  Seq Scan on users u  (cost=0.00..100.00 rows=1000)

Read bottom-up, innermost operations first:

• Seq Scan on users - Reads all users (small table, fast)
• Hash - Builds hash table from users
• Index Scan on orders - Finds completed orders using index
• Hash Join - Matches orders to users using hash table
• HashAggregate - Groups and counts

Visual EXPLAIN Tools

For complex queries, use visual tools:

• pgAdmin - Built-in graphical EXPLAIN
• explain.depesz.com - Paste your EXPLAIN output
• explain.dalibo.com - Interactive visualization

Key Takeaways

Next up: Indexing Assessment - Test your knowledge with real-world scenarios.