Query Execution Plans | Relational Databases

In this chapter

We read a full query execution plan end to end — a join, a GROUP BY, and an ORDER BY — and see exactly which step an index fixes, which step still needs a temporary sort, and why adding an index to the wrong table changes nothing.

12–15 min

The Problem in Real Life

Mike asks the question that's been bugging him since the last two chapters: "When I hit run, what does the database actually do? Like, in what order?"

Sarah realizes she's been reading EXPLAIN QUERY PLAN's output without ever really explaining what it represents. "It's not just SCAN or SEARCH," she says. "For a real query — with a join, a GROUP BY, an ORDER BY — there's a whole sequence of steps. Let me show you all of it, not just pieces."

So there's a whole plan behind every query, not just one decision?

Mike

One Step vs. a Whole Sequence of Them

A real query is several steps, not one

A join alone produces one plan row per table; GROUP BY and ORDER BY can each add their own extra step.

The planner picks a strategy before running anything

SQLite decides the whole sequence of steps upfront, based on the query and whatever indexes exist.

Sorting needs a temp B-Tree without a matching index

USE TEMP B-TREE FOR ORDER BY / GROUP BY means SQLite is building a throwaway sorted structure just to answer this one query.

Not every added index changes the plan

An index only helps if it targets the step that was actually the bottleneck — indexing the wrong table or column changes nothing.

Reading a Real Execution Plan

A query execution plan is the complete, ordered sequence of steps SQLite will actually take to answer a query — which table to read first, how to find matching rows in each one, and what extra work (sorting, grouping) has to happen along the way. Everything so far has looked at one step in isolation; a real query usually has several.

The plan isn't guessed at runtime from scratch each time — SQLite's query planner looks at the query, the tables involved, and whatever indexes exist, and picks what it estimates is the cheapest overall sequence of steps before running anything.

A join's plan has one row per table involved. SQLite picks which table to read through first (the outer loop) and how to find each match in the other (the inner lookup) — here, it reads AllOrders and looks up each match in Customers by primary key, since Customers is small.

A Join's Plan, Before Any Index

EXPLAIN QUERY PLAN
SELECT c.Name, o.OrderID
FROM AllOrders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE o.Status = 'Cancelled';

SCAN o, then SEARCH c USING INTEGER PRIMARY KEY (rowid=?) — reasonable, but AllOrders itself is still a full scan since Status has no index yet.

The join's inner lookup on Customers was already efficient — the actual bottleneck was AllOrders' full scan. Indexing the column the plan is genuinely filtering on (Status) is what changes the outer step from a SCAN into a SEARCH.

Indexing the Column the Plan Actually Filters On

CREATE INDEX idx_allorders_status ON AllOrders (Status);

EXPLAIN QUERY PLAN
SELECT c.Name, o.OrderID
FROM AllOrders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE o.Status = 'Cancelled';

SEARCH o USING INDEX idx_allorders_status (Status=?), then the same SEARCH c as before — both sides of the join now use an index.

GROUP BY and ORDER BY aren't free — if there's no index that already produces rows in the needed order, SQLite has to build a temporary B-Tree on the fly, just to sort or group the results before returning them.

GROUP BY and ORDER BY Add Their Own Steps

EXPLAIN QUERY PLAN
SELECT c.Name, COUNT(*) AS OrderCount
FROM AllOrders o
JOIN Customers c ON c.CustomerID = o.CustomerID
WHERE o.Status = 'Delivered'
GROUP BY c.Name
ORDER BY OrderCount DESC
LIMIT 5;

The plan now includes USE TEMP B-TREE FOR GROUP BY and USE TEMP B-TREE FOR ORDER BY, on top of the same join steps as before.

Because a B-Tree index is already sorted, ordering by an indexed column can just walk the index in order — no separate sorting step needed. Ordering by a column with no index still means building a temporary sorted structure from scratch.

Sorting an Indexed Column vs. an Unindexed One

CREATE INDEX idx_allorders_orderdate ON AllOrders (OrderDate);

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders ORDER BY OrderDate LIMIT 10;

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders ORDER BY CustomerID LIMIT 10;

OrderDate: SCAN AllOrders USING INDEX idx_allorders_orderdate — no temp B-Tree. CustomerID: SCAN AllOrders, then USE TEMP B-TREE FOR ORDER BY — the extra step shows up.

Notice that adding an index doesn't automatically help — it only changes the plan when it targets the actual bottleneck step. The Customers side of the join was never the slow part; indexing AllOrders' filtered column was what mattered.

A query execution plan is really a small to-do list: which table to touch first, how to find rows in each one, and what extra sorting or grouping work is unavoidable. Reading that list is the single most useful skill for diagnosing why a specific query is slow, instead of guessing.

Key Takeaway

A query execution plan is an ordered sequence of steps, not one decision — reading which step is a full scan, which needs a temporary sort, and which table drives the join is how you find the actual bottleneck instead of guessing at it.

Why This Matters

Real production queries almost always involve joins, grouping, or sorting — reading a full execution plan, not just checking for a single SCAN, is what separates guessing at a performance fix from actually finding the step that's costing the most.

Mike can finally see the whole sequence behind a query, not just one number. Next: how to actually reshape a query so the planner has better options to choose from in the first place.