Skip to content
Tutorials Center
My learning

Topics

Lesson 87 of 125 ยท Performance

Reading Query Plans

Learn to read query plans with EXPLAIN QUERY PLAN. Tell SCAN from SEARCH, spot covering indexes and temp B-trees, and read EXPLAIN ANALYZE in PostgreSQL.

SQL is a declarative language: you describe the result you want, and the database decides how to produce it. That decision is called a query plan. For the same query, one plan might read three rows through an index while another reads every row in a ten-million-row table. Both return the same answer; one takes a millisecond and the other takes a minute.

You can't tune what you can't see. Every serious database lets you ask for the plan instead of the result: SQLite has EXPLAIN QUERY PLAN, PostgreSQL and MySQL have EXPLAIN and EXPLAIN ANALYZE, and SQL Server shows graphical execution plans. Reading plans is the core skill of SQL performance work, and it's less mysterious than it looks.

In this lesson you'll build a table with 50,000 orders, then read SQLite plans line by line: full scans versus index searches, covering indexes, temporary B-trees for sorting and grouping, join order, and subqueries. At the end you'll learn to read the equivalent output in PostgreSQL, MySQL and SQL Server.

Your first plan

Put EXPLAIN QUERY PLAN in front of any statement, and SQLite returns the plan instead of running the query. Every example in this lesson starts by generating test data with a recursive CTE: 2,000 customers and 50,000 orders, built in a fraction of a second.

SQL SCAN versus SEARCH

Press Run (or Ctrl + Enter) to see the result here.

The three plans read:

  1. SCAN orders: SQLite reads all 50,000 rows and checks each one. This is a full table scan.
  2. SEARCH orders USING INDEX idx_orders_customer (customer_id=?): SQLite jumps into the index, finds the 25 entries for customer 42, and fetches only those rows.
  3. SEARCH orders USING INTEGER PRIMARY KEY (rowid=?): an INTEGER PRIMARY KEY is the table's internal row id, so this is the fastest lookup there is.

SCAN means "read everything" and SEARCH means "jump to the matching part". On a large table, a SCAN in a query that only needs a few rows is the first thing to look for. (A scan isn't always bad: if you really need most of the rows, reading them all in order is the cheapest way.)

The columns of the output

EXPLAIN QUERY PLAN returns four columns:

Column Meaning
id A number identifying this step
parent The id of the step this one belongs to (0 = top level)
notused Reserved; ignore it
detail The human-readable description, which is what you actually read

The parent column turns the rows into a tree. The SQLite command-line shell draws that tree for you with indentation; here you read it from the numbers. Steps with the same parent run in the order listed, and in a join, the first table listed is the outer loop.

Here's a cheat sheet of the detail texts you'll meet most often:

Detail text What it means
SCAN t Read every row of t
SCAN t USING INDEX i Read every row, but in index order (usually to avoid a sort)
SCAN t USING COVERING INDEX i Read the whole index only; the table itself isn't touched
SEARCH t USING INDEX i (a=?) Look up matching rows through index i, then fetch each row from the table
SEARCH t USING COVERING INDEX i (a=?) Look up through the index; every needed column is in it
SEARCH t USING INTEGER PRIMARY KEY (rowid=?) Direct lookup by row id
(a=? AND b>?) Which index columns are used, and how (= for equality, >/< for ranges)
USE TEMP B-TREE FOR ORDER BY Results must be sorted in a temporary structure
USE TEMP B-TREE FOR GROUP BY / DISTINCT Grouping or de-duplication needs a temporary structure
AUTOMATIC COVERING INDEX SQLite builds a throwaway index just for this query
BLOOM FILTER ON t A quick pre-check that skips lookups that can't match
CORRELATED SCALAR SUBQUERY n A subquery that runs again for every outer row
LIST SUBQUERY n An IN (SELECT ...) list computed once
MATERIALIZE x / CO-ROUTINE x A subquery or CTE stored as a temporary table, or produced row by row on demand
COMPOUND QUERY A UNION, INTERSECT or EXCEPT
MULTI-INDEX OR An OR answered by combining several index lookups

The exact wording changes between SQLite versions. Older versions printed SCAN TABLE orders and SEARCH TABLE orders; newer ones drop the word TABLE. The ideas are stable, so focus on SCAN vs SEARCH, which index, and whether a temp B-tree appears.

Covering indexes: never touching the table

A normal index lookup is a two-step process: find the matching entries in the index, then use each entry's row id to fetch the full row from the table. If every column the query needs is already in the index, SQLite skips the second step. That's a covering index, and the plan says so:

SQL When an index covers the query

Press Run (or Ctrl + Enter) to see the result here.

The first plan uses INDEX, the other two use COVERING INDEX. Every index entry also carries the row id, so id is always "free". Covering indexes matter most for queries that run constantly or read many rows; Partial, Expression and Covering Indexes shows how to design them.

Sorting and grouping: temp B-trees

ORDER BY, GROUP BY and DISTINCT need rows in a particular order. If no index delivers that order, SQLite collects the rows into a temporary B-tree (a sorted structure in memory) first. The plan shows this as USE TEMP B-TREE FOR .... For a few hundred rows that's nothing; for millions it's a big, memory-hungry step, and it means the query can't stream its first row until it has read them all.

An index on the sort column gives the rows in order for free, and with LIMIT the query can stop after a handful of rows:

SQL Avoiding a sort with an index

Press Run (or Ctrl + Enter) to see the result here.

Read the four plans in turn:

  1. SCAN orders plus USE TEMP B-TREE FOR ORDER BY: read all 50,000 rows, keep the pending ones, sort them, return five.
  2. SEARCH orders USING COVERING INDEX idx_orders_status_date (status=?) and no temp B-tree. Within status = 'pending', the index is already sorted by ordered_on, so SQLite reads it backwards and stops after five rows. It's even covering, because the query only asks for id and ordered_on.
  3. The index finds the pending rows, but they're not sorted by total, so the temp B-tree is back.
  4. SCAN orders USING COVERING INDEX idx_orders_status_date: grouping by the leading index column means equal statuses are already next to each other, and the count needs nothing else.

The lesson: an index can serve both the WHERE and the ORDER BY when the equality columns come first and the sort column comes right after them.

Joins: reading the loop order

SQLite runs joins as nested loops: for each row of the outer table, it looks up matching rows in the inner table. The plan lists tables from outermost to innermost, and the important question is how each inner table is accessed. A SCAN inside a loop is the classic performance disaster, because it multiplies.

SQL How a join is executed

Press Run (or Ctrl + Enter) to see the result here.

Without an index, SQLite picks the only sane option: SCAN o as the outer loop, then SEARCH c USING INTEGER PRIMARY KEY for each order, checking the country after the lookup. That's 50,000 primary-key lookups. Once orders.customer_id is indexed, the plan flips: SCAN c outside (2,000 customers, filtered to Japan) and SEARCH o USING INDEX idx_orders_customer (customer_id=?) inside. The query planner chose the join order by itself. You wrote the same SQL both times.

Sometimes, when no index exists and the inner table would be scanned again and again, SQLite builds an index on the fly. You'll see AUTOMATIC COVERING INDEX (often with a BLOOM FILTER) in the plan. It's a helpful safety net, but it's rebuilt on every execution: seeing it in a frequent query is a hint that a real index is missing.

Subqueries, CTEs and UNION

Plans also reveal how SQLite handles nested queries:

SQL Plans for subqueries and compound queries

Press Run (or Ctrl + Enter) to see the result here.

  • The first plan shows CORRELATED SCALAR SUBQUERY 1 with a SCAN o underneath it (its parent points to the subquery step). That scan runs for each of the 10 customers. Ten scans of 50,000 rows is 500,000 row reads for a 10-row result. An index on orders.customer_id would turn each into a quick SEARCH, as you'll practice in Writing Fast Queries.
  • The second shows MATERIALIZE spend: the CTE runs once into a temporary table (with its own USE TEMP B-TREE FOR GROUP BY), then the outer query scans it. Without MATERIALIZED, SQLite decides for itself whether to inline a CTE or materialize it; see Common Table Expressions.
  • The third shows COMPOUND QUERY with a LEFT-MOST SUBQUERY and UNION USING TEMP B-TREE: removing duplicates needs a temporary structure, which UNION ALL would skip.

EXPLAIN: the bytecode underneath

Plain EXPLAIN (without QUERY PLAN) shows something much lower level: the program of virtual machine instructions that SQLite compiles your query into. It's useful to SQLite developers, but you'll rarely need it for tuning. Here's a peek; look for SeekRowid, the instruction that performs the primary key lookup:

SQL Bytecode with plain EXPLAIN

Press Run (or Ctrl + Enter) to see the result here.

EXPLAIN ANALYZE in PostgreSQL

Other databases show richer plans because their planners are cost-based in more detail and they can measure an actual run. In PostgreSQL, EXPLAIN shows the estimated plan and EXPLAIN ANALYZE executes the query and adds real timings and row counts:

SQL
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42;

The output looks roughly like this (numbers vary by machine and data):

TEXT
Bitmap Heap Scan on orders  (cost=4.48..80.29 rows=25 width=14) (actual time=0.030..0.071 rows=25 loops=1)
  Recheck Cond: (customer_id = 42)
  Heap Blocks: exact=25
  Buffers: shared hit=27
  ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..4.47 rows=25 width=0) (actual time=0.018..0.018 rows=25 loops=1)
        Index Cond: (customer_id = 42)
        Buffers: shared hit=2
Planning Time: 0.105 ms
Execution Time: 0.094 ms

How to read it:

  • Nodes form a tree. Indented nodes marked -> are children and run first; their output feeds the parent.
  • cost=4.48..80.29: the planner's estimate in arbitrary units: the cost before the first row, then the total cost.
  • rows=25 (in the first parentheses): estimated rows. actual ... rows=25: rows really produced. A big gap between estimate and reality (say 25 versus 250,000) is the most common root cause of bad plans; it usually means statistics are stale, and running ANALYZE fixes it.
  • loops: how many times the node ran. Multiply its time and rows by loops for the total, which matters inside nested loops.
  • Buffers: how many data pages were read from memory (hit) or disk (read).

Common PostgreSQL node names are Seq Scan (full scan), Index Scan, Index Only Scan (covering), Bitmap Heap Scan (collect many matches from an index, then read the table pages in order), Nested Loop, Hash Join, Merge Join, Sort and HashAggregate.

EXPLAIN ANALYZE really runs the statement. For an UPDATE or DELETE, the rows really change. Wrap it in a transaction you roll back: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;. SQLite's EXPLAIN QUERY PLAN never runs the query.

MySQL and SQL Server

MySQL prints one row per table in the plan:

SQL
-- MySQL
EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42;

The columns to read are type (how the table is accessed: const and eq_ref for single-row lookups, ref for index lookups, range for index ranges, index for a full index scan, and ALL for a full table scan), key (the index chosen), rows (estimated rows examined) and Extra, where Using index means covering, Using filesort means an extra sort and Using temporary means a temporary table. MySQL 8.0.18 and later also support EXPLAIN ANALYZE, which runs the query and prints a tree with real timings, and EXPLAIN FORMAT=TREE for the estimated tree.

SQL Server shows graphical execution plans in SQL Server Management Studio and Azure Data Studio ("Include Actual Execution Plan"), and text or XML plans with SET SHOWPLAN_XML ON. SET STATISTICS IO, TIME ON; reports pages read and time per statement. Look for Table Scan or Clustered Index Scan (full scans), Index Seek (search), Key Lookup (fetching columns the index didn't cover), Sort, and thick arrows (many rows).

The same ideas under different names:

Idea SQLite PostgreSQL MySQL SQL Server
Show the plan EXPLAIN QUERY PLAN EXPLAIN EXPLAIN Estimated plan / SHOWPLAN
Run and measure (not built in) EXPLAIN ANALYZE EXPLAIN ANALYZE Actual plan, STATISTICS IO, TIME
Full scan SCAN t Seq Scan type: ALL Table Scan / Clustered Index Scan
Index lookup SEARCH ... USING INDEX Index Scan type: ref / range Index Seek
Covering COVERING INDEX Index Only Scan Extra: Using index Seek with no Key Lookup
Extra sort USE TEMP B-TREE FOR ORDER BY Sort Using filesort Sort

A routine for reading any plan

  1. Find the big tables. Is each one reached with a search (index) or a scan? A scan of a large table for a small result is suspect number one.
  2. Look inside loops. A scan inside a nested loop or a correlated subquery runs many times.
  3. Look for sorts and temporary structures. Could an index provide the order instead?
  4. Check covering. For hot queries, could the index include the few extra columns so the table isn't touched?
  5. Compare estimates with reality where the database reports both (PostgreSQL, MySQL). Big gaps mean stale statistics: run ANALYZE.
  6. Change one thing, then look at the plan again. And measure, because a plan is a strategy, not a stopwatch.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A plan says SCAN orders for SELECT * FROM orders WHERE email = '[email protected]' on a table with 5 million rows. What does it mean?

    SCAN means a full table scan. It doesn't say anything about how many rows are returned, only that all of them are read. An index on email would turn it into a SEARCH.
  2. 2. What does SEARCH orders USING COVERING INDEX idx_x (customer_id=?) tell you?

    A covering index contains every column the query needs, so the lookups stay inside the index and the table is never touched.
  3. 3. Which plan line means the result has to be sorted in an extra step?

    A temp B-tree for ORDER BY means no index delivered the rows in the requested order, so SQLite sorts them itself.
  4. 4. With an index on (status, ordered_on), which query can skip the sort step?

    With equality on the first index column, the matching index entries are already ordered by the second column, ordered_on.
  5. 5. In PostgreSQL, EXPLAIN ANALYZE shows rows=12 estimated but actual rows=480000. What is the most likely fix?

    Large estimate errors usually mean the planner's statistics are outdated. ANALYZE refreshes them so it can choose a better plan.

Summary

  • A query plan is the strategy the database picks for your query. EXPLAIN QUERY PLAN shows it in SQLite without running the query.
  • SCAN reads every row; SEARCH jumps to matching rows through an index or the INTEGER PRIMARY KEY.
  • COVERING INDEX means the table isn't touched at all; USE TEMP B-TREE means an extra sort, grouping or de-duplication step.
  • Joins are nested loops: the first table is the outer loop, and a scan of an inner table multiplies its cost.
  • Watch for correlated subqueries, automatic indexes and temp B-trees in frequently run queries.
  • PostgreSQL's EXPLAIN ANALYZE adds real timings, row counts and loops; compare estimates with actual rows. MySQL's type, key, rows and Extra columns and SQL Server's graphical plans express the same ideas.

Next: Writing Fast Queries, where you'll use plans and timings to rewrite slow queries into fast ones.