Reading Execution Plans — EXPLAIN QUERY PLAN

The dataset for this article — perf_sales (50,000 rows of sales)

When you want to tell whether a query is fast or slow, the first thing to look at is the execution plan (the query plan).

Given a SQL statement, the database decides internally "which tables to read in what order, and how".

The tool that prints that procedure as text is EXPLAIN QUERY PLAN.

Prefix this keyword to a query and you'll see the execution steps instead of the actual results.

Before diving into the exercises, check the column definitions and a sample of the data for the perf_sales table used throughout this article. Generating the data takes a moment, so the first run may take a few seconds.

① Use PRAGMA table_info(perf_sales); to view the column names, types, and primary key.

② Use SELECT * FROM perf_sales LIMIT 5; to preview the first 5 rows.

③ Use SELECT COUNT(*) FROM perf_sales; to confirm there are 50,000 rows.

SCAN and SEARCH — full scans and index scans

The first two words to learn in an execution plan are SCAN and SEARCH.

SCAN perf_sales means read every row from the top of the table to the bottom (a full scan).

When you filter on a column that has no index, the database doesn't know which rows match, so it checks all 50,000 rows one by one.

Meanwhile SEARCH perf_sales USING INDEX index_name (column=?) means follow the index to land directly on the matching rows (an index scan).

Create an index with CREATE INDEX and filters on that column stop scanning every row — the plan changes from SCAN to SEARCH ... USING INDEX.

-- Filter by region: no index, so it reads every row
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- Build an index on region, then look at the same plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

Imagine the scenario: "counting sales for a specific rep is slow." You'll compare the execution plan before and after adding an index. Run the index creation and the plan check together in a single execution. (Run it correctly to reveal the explanation.)

① First, prefix EXPLAIN QUERY PLAN to a query that counts rows where emp_id equals 7 and check the plan. With no index, you'll see SCAN.

② Then drop any existing index with DROP INDEX IF EXISTS, build an index on emp_id with CREATE INDEX, and run the same EXPLAIN QUERY PLAN again. Confirm it changes to SEARCH ... USING INDEX.

USE TEMP B-TREE — a temporary area for sorting

USE TEMP B-TREE FOR ORDER BY (a temporary B-tree) means the ORDER BY column has no index, so the database is building a temporary scratch area to sort the results.

All 50,000 rows go into that temporary area to be reordered, so the cost grows with row count.

If there's an index that can be used for the sort, this line drops out of the plan and the index's existing order is used as-is.

-- Top 10 by amount ascending: no index, so a temp area is needed for the sort
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- Build an index on amount and the sort can reuse its order directly
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE drops from the plan

Look at the execution plan for "top 10 sales by ascending amount" while no index exists. The three exercises that follow run in order so you can compare the plans before and after the index.

① In this first console, prefix EXPLAIN QUERY PLAN to a query that orders perf_sales by amount ascending and pulls the first 10 (sale_id, amount) rows.

Confirm that the output contains two lines: SCAN perf_sales and the sort-related USE TEMP B-TREE FOR ORDER BY. With no index on the sort column amount, the database has to load 50,000 rows into a temporary area and reorder them.

② In this second console, run DROP INDEX IF EXISTS ix_amount; to clear the previous one, then create an index named ix_amount on the amount column of perf_sales.

Index creation is a one-off cost, so we don't include it in the pure plan comparison. The third console re-runs the plan check for the same query.

③ In this third console, re-run the same EXPLAIN QUERY PLAN as in ①.

Confirm that the USE TEMP B-TREE FOR ORDER BY line is gone from the plan, replaced by something like SEARCH perf_sales USING INDEX ix_amount. The index's order is reused directly, so the temp-area sort is no longer needed.

USING COVERING INDEX — an index that doesn't touch the table

USING COVERING INDEX (an Index-Only Scan, also called a covering index in English) means every column the query needs is contained inside the index, so the database can build the result from the index alone without reading the table itself.

An index is a small copy of just the columns you pull from the table, so skipping the table read is what makes it faster.

It's one step beyond SEARCH ... USING INDEX — when the plan shows USING COVERING INDEX, you're getting the most efficient read.

-- Index on emp_id only -> find rows via the index, then read amount from the table
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (table is also read)

-- Composite index on (emp_id, amount) -> every needed column is in the index, no table read
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

Take a query that "filters by emp_id and returns only amount" and change its plan into one that finishes inside the index without touching the table. Run the index creation and the plan check together in a single execution.

① Drop any existing index with DROP INDEX IF EXISTS, then use CREATE INDEX to build a composite index over emp_id and amount, in that order.

② Prefix EXPLAIN QUERY PLAN to a query that filters by emp_id = 7 and selects only emp_id and amount, and check the plan. Read off that the plan finishes inside the index alone, since every needed column lives in the index.

Subquery plans — CORRELATED and MATERIALIZE

Queries with subqueries get their own dedicated vocabulary in the plan.

CORRELATED SCALAR SUBQUERY means the subquery's value depends on the current outer row, so it gets re-executed once per outer row.

With 50,000 outer rows, the subquery can run 50,000 times — when this word shows up in the plan, it's a candidate for a rewrite.

Meanwhile MATERIALIZE means the result of a subquery or CTE is computed once, stored in a temporary table, and reused.

It's an optimization that pays the computation cost once for an intermediate result you reference multiple times.

Which of these two words appears tells you whether the subquery is "being computed every time" or "computed once and reused".

-- Correlated scalar subquery: re-fetch "max amount for this region" per outer row
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> a CORRELATED SCALAR SUBQUERY line appears

-- A derived table (subquery in the FROM clause) is computed once
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> a MATERIALIZE line may appear

Look at the plan for "for each row, pick only sales that exceed that rep's average amount, top 10" and read off the structure that re-runs the subquery per row.

① Prefix EXPLAIN QUERY PLAN to a query that, for each row in perf_sales, uses a correlated subquery to compute "the average amount for the same emp_id" and pulls 10 rows where amount exceeds that.

② Read off, in the output, the line indicating a correlated scalar subquery and the SCAN perf_sales for both the outer and inner queries. Note that this is a heavy shape on large tables because the inner subquery is evaluated once per outer row.

How this maps to other RDBMSs — MySQL / PostgreSQL / Oracle

The plan vocabulary in this article comes from SQLite's EXPLAIN QUERY PLAN, but MySQL / PostgreSQL / Oracle surface the same concepts under different names.

Start by checking how each database produces an execution plan, then compare the cheat sheet of plan terminology — you'll be able to read plans in another database without much extra learning.

How to print an execution plan in each database

• SQLite: Prefix EXPLAIN QUERY PLAN to the query (e.g. EXPLAIN QUERY PLAN SELECT ...;). It doesn't run the query, only prints the plan. Plain EXPLAIN shows the low-level bytecode, so for human reading you want EXPLAIN QUERY PLAN
• MySQL: Prefix EXPLAIN to the query (e.g. EXPLAIN SELECT ...;). MySQL 8.0+ also has EXPLAIN ANALYZE SELECT ...; for actual run time and actual row counts. For JSON output, use EXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL: Prefix EXPLAIN to the query (e.g. EXPLAIN SELECT ...;). That's the estimated plan only. For actuals, use EXPLAIN ANALYZE SELECT ...;, and for detailed I/O buffer info, use EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle: A two-step pattern — EXPLAIN PLAN FOR <query>; saves the plan to an internal table, and SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); prints it. For quick viewing, the SET AUTOTRACE ON session option also works

Cheat sheet of plan terminology

The wording differs, but what the database is doing internally is essentially the same. The goal of this article is to be able to read "is it reading every row, is it narrowing with an index, does it need a work area to sort, is it running a subquery over and over" from the EXPLAIN output of each database.