SQL EXPLAIN and Query Execution Plans

SQL EXPLAIN and Query Execution Plans

EXPLAIN shows how the database plans to execute a query without actually running it. EXPLAIN ANALYZE runs the query and shows actual execution statistics. These tools are essential for diagnosing performance bottlenecks.

Basic Usage

-- Plan only (does not execute)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Plan with actual execution stats (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- MySQL
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

Reading the Plan

Key elements in an execution plan:

Scan Types:

• Seq Scan / Full Table Scan: Reads every row. Indicates a missing index
• Index Scan: Uses an index to find rows efficiently
• Index Only Scan: Answers the query entirely from the index (covering index)
• Bitmap Index Scan: Combines multiple index results before accessing the table

Join Methods:

• Nested Loop: Iterates over one table for each row of the other. Good for small datasets
• Hash Join: Builds a hash table from one input. Efficient for large unsorted datasets
• Merge Join: Merges two sorted inputs. Optimal when both are pre-sorted

What to Look For

1. Sequential scans on large tables: Usually indicates a missing or unused index
2. High row estimates vs actual rows: Stale statistics cause bad plans. Run ANALYZE
3. Sort operations: If ORDER BY causes a sort node, consider an index
4. Nested loops with large outer tables: May indicate a missing index on the inner table
5. Temp disk usage: Indicates insufficient work_mem for in-memory operations

Optimization Workflow

1. Run EXPLAIN ANALYZE on the slow query
2. Identify the most expensive node in the plan
3. Check if adding or modifying an index addresses the bottleneck
4. Update statistics with ANALYZE if estimates are inaccurate
5. Rewrite the query if the plan shape is suboptimal
6. Re-run EXPLAIN ANALYZE to verify the improvement

Tips

• Always use EXPLAIN ANALYZE for real measurements, not just estimates
• Be cautious with EXPLAIN ANALYZE on INSERT/UPDATE/DELETE as it actually executes them
• Use the BUFFERS option in PostgreSQL to see I/O statistics
• Compare plans before and after changes to quantify improvements

EXPLAIN is the most important tool for database performance tuning and should be the first step whenever a query is slow.