Reading SQL Execution Plans

Execution plans show how the database executes your query. Reading them helps identify bottlenecks, missing indexes, and inefficient operations for optimization.

Viewing Execution Plans

PostgreSQL

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123;

MySQL

EXPLAIN
SELECT * FROM orders
WHERE customer_id = 123;

SQL Server

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders
WHERE customer_id = 123;
GO

Key Operations to Look For

Table Scan (Bad - Slow)

Seq Scan on orders (cost=0.00..1500.00 rows=100000)
-- Reading every row - missing index!

Index Seek (Good - Fast)

Index Seek on idx_customer (cost=0.29..8.31 rows=1)
-- Using index - efficient!

Nested Loop Join

Nested Loop (cost=0.58..24.75 rows=5)
-- Good for small datasets, slow for large ones

Hash Join

Hash Join (cost=15.50..45.25 rows=500)
-- Good for large datasets with equality conditions

Common Performance Issues

Missing Index

-- Before (Table Scan)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- Seq Scan on orders (cost=0.00..1500.00)

-- Add index
CREATE INDEX idx_customer ON orders(customer_id);

-- After (Index Seek)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- Index Scan using idx_customer (cost=0.29..8.31)

SELECT * Instead of Specific Columns

-- Bad (more data to read)
SELECT * FROM orders WHERE status = 'shipped';

-- Good (only needed columns)
SELECT order_id, customer_id, total FROM orders WHERE status = 'shipped';

What to Watch For

• High cost values: Indicate expensive operations
• Table/Seq scans: Consider adding indexes
• Large row estimates: Update statistics if inaccurate
• Multiple sorts: Can slow down queries significantly