SQL › Peeking Under the Hood

EXPLAIN and Query Plans

Before running a query, PostgreSQL picks a plan: which tables to read, in which way, and in which order. EXPLAIN shows that plan. EXPLAIN ANALYZE also runs the query and shows what really happened.

Syntax

Order of execution in a plan: rows flow upwards. The most indented steps read the tables, each step hands its rows to the step above it, and the top line gives the result.

Code
EXPLAIN query;                                                  -- the plan with estimated costs
EXPLAIN (COSTS OFF) query;                                      -- only the steps
EXPLAIN (ANALYZE) query;                                        -- runs it: real rows and times
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) query;    -- real rows, nothing that changes between runs
CREATE INDEX index_name ON table_name (column1, column2);

Every database shows plans, with its own commands and step names. MySQL has EXPLAIN and EXPLAIN ANALYZE, Oracle has EXPLAIN PLAN FOR with DBMS_XPLAN, and SQL Server shows an execution plan in its tools or with SET SHOWPLAN_TEXT ON.

Examples

Read a plan

The same plan with and without the estimated costs.

Code
EXPLAIN SELECT * FROM products WHERE price > 5000;
EXPLAIN (COSTS OFF) SELECT * FROM products WHERE price > 5000;

The indexes a table already has

A primary key comes with an index, so finding one order by its id doesn't read the whole table.

Code
EXPLAIN (COSTS OFF) SELECT * FROM orders WHERE order_id = 42;
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders' ORDER BY indexname;

In SQL Server and MySQL, a table's rows are stored in the order of one index, the clustered index, usually the primary key; a table can have only one. Every other index is non-clustered: a sorted list that points back to the rows. PostgreSQL keeps the rows apart from all its indexes, so each one works like a non-clustered index; its CLUSTER command sorts a table once but doesn't keep it sorted.

Real numbers with ANALYZE

ANALYZE runs the query. Times change on every run; switch them off to compare plans.

Code
EXPLAIN (ANALYZE) SELECT * FROM payments WHERE method = 'cod';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT * FROM payments WHERE method = 'cod';

Joins in a plan

Each table is read by its own indented step, and the join step above combines them.

Code
EXPLAIN (COSTS OFF)
SELECT c.full_name, r.rating
FROM reviews r
JOIN customers c ON c.customer_id = r.customer_id;

Add an index

Compare the plan before and after. On a small table, reading every row can still be the cheapest choice, so the planner may ignore a new index.

Code
EXPLAIN (COSTS OFF) SELECT * FROM payments WHERE customer_id = 7 AND status = 'failed';
CREATE INDEX idx_payments_customer_status ON payments (customer_id, status);
EXPLAIN (COSTS OFF) SELECT * FROM payments WHERE customer_id = 7 AND status = 'failed';

When an index isn't used

An index on a column helps only when the query compares the column itself. A calculation or a cast on the column hides it, and so does LIKE with a leading %, because the index is sorted by the start of the value.

The planner also skips an index when the condition matches a large share of the rows: then reading the whole table is cheaper.

Code
EXPLAIN (COSTS OFF) SELECT order_id FROM order_items WHERE product_id = 19;      -- the index
EXPLAIN (COSTS OFF) SELECT order_id FROM order_items WHERE product_id + 0 = 19;  -- the whole table
 
CREATE INDEX idx_items_discount ON order_items (discount_pct);
EXPLAIN (COSTS OFF) SELECT * FROM order_items WHERE discount_pct = 80;   -- a rare value: the index
EXPLAIN (COSTS OFF) SELECT * FROM order_items WHERE discount_pct = 0;    -- a common value: the whole table

An index on an expression

When queries keep filtering on the same calculation, index the calculation itself. The query must write it exactly as the index does.

PostgreSQL, Oracle and MySQL (from 8.0.13) index an expression directly; SQL Server indexes a computed column instead.

Code
CREATE INDEX idx_items_rounded_price ON order_items ((ROUND(unit_price)));
EXPLAIN (COSTS OFF) SELECT * FROM order_items WHERE ROUND(unit_price) = 24999;

What an index costs

An index makes reads faster but writes slower: every INSERT, UPDATE and DELETE must change the index too, and it takes disk space. Index the columns you often filter, join or sort on, not every column.

A primary key and a UNIQUE constraint each create a unique index. A covering index also stores extra columns (INCLUDE in PostgreSQL and SQL Server), so a query that needs only those columns can skip the table.

ANALYZE really runs the statement

With UPDATE or DELETE, EXPLAIN ANALYZE makes the change.

Code
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
UPDATE products SET stock = stock + 1 WHERE product_id = 1;
SELECT stock FROM products WHERE product_id = 1;      -- one more than before

Remember

• Read a plan from the most indented line up.
• EXPLAIN only plans; EXPLAIN ANALYZE runs the query, changes included.
• In a two-column index, put the column compared with = first and the range column second.
• A calculation, cast or leading % on a column stops its index being used; index the expression instead.
• Indexes speed up reads and slow down writes.
• Turn COSTS, TIMING and SUMMARY off for a plan that's the same on every run.

Practice

1. The Plan for Returned Orders (Easy)
2. Real Row Counts for Big Orders (Easy)
3. An Index for Both Conditions (Medium)
4. An Index for a Calculated Value (Medium)