Reading Query Plans with EXPLAIN

The database's query planner decides how to run each query: which indexes to use, in what order to join tables, and which algorithms to apply. EXPLAIN shows its plan, so you can see why a query is slow.

In SQLite

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

QUERY PLAN
`--SCAN orders

SCAN orders means reading every row. Add an index and ask again:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

SEARCH ... USING INDEX means the index lets SQLite go straight to the matching rows.

Joins in the plan

CREATE INDEX idx_order_items_order ON order_items (order_id);

EXPLAIN QUERY PLAN
SELECT c.name, oi.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.city = 'London';

QUERY PLAN
|--SCAN oi
|--SEARCH o USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

Read the plan as nested loops: for each row found at one level, look up matching rows at the next.

In PostgreSQL

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1;

Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=3 width=40) (actual time=0.021..0.024 rows=3 loops=1)
  Index Cond: (customer_id = 1)
Planning Time: 0.110 ms
Execution Time: 0.045 ms

• EXPLAIN shows the plan and the planner's estimates; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts.
• Look for Seq Scan on large tables, estimates wildly different from actual row counts (run ANALYZE to refresh statistics), and the steps with the largest actual time.

MySQL has EXPLAIN and EXPLAIN ANALYZE too; SQL Server shows graphical execution plans.

A workflow for slow queries

1. Find the slow queries — from logs, pg_stat_statements (PostgreSQL), or the slow query log (MySQL).
2. Run EXPLAIN ANALYZE and find the most expensive step.
3. Fix the cause: add or change an index, rewrite the query, or reduce the data it processes.
4. Measure again — and check the change didn't slow down writes too much.