Reading query plans

Why look at the plan?

SQL says what you want, and the query planner decides how. Usually it chooses well, but when a query is slow you need to see its choice: is it reading the whole table? Ignoring your index? Sorting a million rows? The plan answers these questions without guessing.

SQLite: EXPLAIN QUERY PLAN

Put EXPLAIN QUERY PLAN in front of any query. The query is not run; SQLite describes its plan. In the sqlite3 shell it prints a small tree; on this site it returns rows whose detail column holds the same text.

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

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

For a join, each line is one table, in the order SQLite visits them. On SHOP with an index on orders(customer_id), a join from Pune customers to their orders gives "SCAN c" then "SEARCH o USING COVERING INDEX idx_orders_customer (customer_id=?)": read the small customers table once, and for each customer jump to its orders.

PostgreSQL: EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the plan with estimated costs. EXPLAIN ANALYZE runs the query for real and adds actual times and row counts, which is what you want when tuning. Read the plan from the most indented line outward: inner steps feed the outer ones.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1887.00 rows=10 width=40) (actual time=0.031..11.402 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99988
Planning Time: 0.095 ms
Execution Time: 11.431 ms

-- after CREATE INDEX idx_orders_customer ON orders (customer_id);
Index Scan using idx_orders_customer on orders  (cost=0.29..44.40 rows=10 width=40) (actual time=0.022..0.041 rows=12 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.120 ms
Execution Time: 0.062 ms

• Seq Scan: a full table scan. Rows Removed by Filter: 99988 is the waste: almost everything read was thrown away.
• Index Scan: searched the index, then fetched rows. Index Only Scan: a covering index. Bitmap Heap Scan: collects many matches from an index, then reads them in page order.
• cost=start..total is the planner's estimate in its own units, not milliseconds. rows= is the estimated row count.
• actual time is in milliseconds, per loop. When estimated rows and actual rows differ hugely (10 versus 50,000), statistics are stale: run ANALYZE.

MySQL: EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
type: ALL   key: NULL                 rows: 99823   Extra: Using where      -- before the index
type: ref   key: idx_orders_customer  rows: 12      Extra: NULL             -- after the index

The type column is the access method, from worst to best roughly: ALL (full scan), index (full index scan), range (an index range), ref (index lookup on a non-unique value), eq_ref (one row per join through a unique key) and const (at most one row). key is the index chosen, and rows is the estimated number of rows examined. "Using filesort" in Extra means an extra sort step, and "Using index" means a covering index.

Common mistakes

• Tuning by feel. Fix: read the plan before and after every change.
• Running EXPLAIN ANALYZE on a DELETE or UPDATE in production. Fix: wrap it in a transaction and roll back, or test on a copy.
• Panicking at a SCAN on a tiny table. Fix: on a few hundred rows, a scan is often the best plan; judge with realistic data.
• Reading cost numbers as milliseconds. Fix: compare costs only between plans of the same query; use actual time for real speed.