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