SQL Reference ยท Transactions and Performance
SQL EXPLAIN QUERY PLAN
Reference for EXPLAIN QUERY PLAN: how to read SCAN, SEARCH, COVERING INDEX and temp B-tree lines, and the equivalent plan commands in other databases.
EXPLAIN QUERY PLAN shows how SQLite intends to run a statement without running it: which tables it reads, in what order, and whether it uses an index or scans the whole table. It is the first tool to reach for when a query is slow, and the quickest way to confirm that a new index is actually used.
The output is a small tree of text lines. Each line describes one step, such as SCAN or SEARCH, and nested steps are indented under their parent (the site shows them as rows with id and parent columns).
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The first example prints SCAN orders before the index exists and SEARCH orders USING INDEX idx_orders_customer (customer_id=?) after. In the second, sorting by price needs USE TEMP B-TREE FOR ORDER BY; after the index on (price, name) the plan becomes SCAN products USING COVERING INDEX idx_products_price_name with no sort step, since the index already holds the rows in price order. The join plan loops over orders (SCAN o) and looks up each customer by primary key (SEARCH c USING INTEGER PRIMARY KEY).
Syntax
EXPLAIN QUERY PLAN statement;
| Part | Description |
|---|---|
statement |
Any SELECT, INSERT, UPDATE or DELETE. It is planned but not executed. |
Returns: rows with id, parent, notused and detail columns; read the detail text.
Reading the plan
Text in detail |
Meaning |
|---|---|
SCAN table |
Reads every row. Fine for tiny tables or when you need most rows; a warning sign on big ones. |
SEARCH table USING INDEX name (col=?) |
Jumps to matching rows through an index. This is what you want. |
SEARCH table USING INTEGER PRIMARY KEY (rowid=?) |
Looks up by primary key, the fastest path. |
USING COVERING INDEX name |
The index holds all the columns needed, so the table is not read at all. |
USE TEMP B-TREE FOR ORDER BY |
A separate sort step was needed because no index provides the order. |
USE TEMP B-TREE FOR GROUP BY / DISTINCT |
Grouping or de-duplication through a temporary structure. |
AUTOMATIC INDEX |
SQLite built a temporary index for a join. A hint that a permanent index is missing. |
CO-ROUTINE, MATERIALIZE, SCALAR SUBQUERY |
A subquery or CTE is evaluated separately. |
For a join, the order of the lines is the order of the nested loops: the first table is the outer loop, and each later table is searched once per outer row. Good plans search the inner tables with an index.
Dialect differences
| Database | Show the plan | Run it and measure |
|---|---|---|
| SQLite | EXPLAIN QUERY PLAN ... |
No timing. .eqp and .timer in the shell. |
| PostgreSQL | EXPLAIN ... |
EXPLAIN (ANALYZE, BUFFERS) ... runs the query and shows actual rows and timing. |
| MySQL | EXPLAIN ... (add FORMAT=TREE for a tree) |
EXPLAIN ANALYZE ... (8.0.18+). |
| SQL Server | SET SHOWPLAN_XML ON or "Display Estimated Execution Plan" |
"Include Actual Execution Plan" or SET STATISTICS IO, TIME ON. |
| Oracle | EXPLAIN PLAN FOR ... then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
DBMS_XPLAN.DISPLAY_CURSOR with the GATHER_PLAN_STATISTICS hint. |
-- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 3; -- Oracle EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 3; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Note that in SQLite a bare EXPLAIN (without QUERY PLAN) prints the low-level bytecode, which is rarely useful. In other databases EXPLAIN alone is the plan command.
Usage notes
- Plans depend on statistics and data size. On a tiny table a scan can beat an index; run
ANALYZEand test with realistic data. See query optimizer and statistics. - A function on a column hides it from the index.
WHERE lower(name) = 'x'scans unless you have an expression index. See sargable predicates. - Estimates are not measurements. SQLite's plan has no row counts or timings; PostgreSQL's
ANALYZEvariant does, and it really executes the statement, so wrap data-changing statements in a transaction you roll back. - Compare before and after. Run the plan, add the index, run it again, and confirm that
SCANbecameSEARCH. - Read from the innermost line out for nested subqueries and joins.
- Parameters: plans for placeholders (
?) may differ from plans for literal values.