SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries
A SQL explain plan shows how the database intends to run your query: which tables it scans, which indexes it uses, how it joins and whether it sorts in a temporary structure. We read plans for five common queries on a 300,000-row table, added two indexes, and recorded every plan and timing before and after, including one index the planner used that did not help at all.
- A SQL explain plan lists the steps the database will take; look first for full scans of big tables and for temporary sort structures.
- Indexes turned a 9.3 ms full scan for one customer into an index search under 0.1 ms, and a 19.6 ms sort for the latest orders into an instant index walk.
- Wrapping the date column in
substr()still scanned every entry (17.5 ms) even with an index on that column. - An index on a column where 60 percent of rows matched took 203 ms; a forced full scan took 195 ms.
When a query is slow, guessing is expensive: adding random indexes slows writes and wastes space, and rewriting SQL blindly can make things worse. A SQL explain plan tells you what the database is actually doing, so you can fix the real problem. Every major database has one: EXPLAIN in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite, and the execution plan view in SQL Server.
We used SQLite 3.50.4 for every measurement because it runs anywhere, and its plans are short and readable. The same concepts appear in PostgreSQL and MySQL output under slightly different names, which we map later; we did not run those databases.
What EXPLAIN shows
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
-- SCAN orders
Prefix any query with the explain keyword and, instead of results, the database returns its plan: the order in which it will read tables, which access method it will use for each (scan or index), how it will join them, and whether it needs extra work such as sorting. The query is not executed, so this is safe even for expensive statements.
Our test database
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT); -- 20,000 rows
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
total REAL, created TEXT); -- 300,000 rows
-- status is 'paid' for about 60% of orders; ANALYZE run after loading
Running ANALYZE gathers statistics about the data, such as how many distinct values each index has. Planners use those statistics to choose between plans, so stale statistics are a common cause of bad plans.
Plan vocabulary
| SQLite says | Meaning | PostgreSQL equivalent |
|---|---|---|
SCAN table |
Read every row | Seq Scan |
SEARCH table USING INDEX ix |
Find rows through an index, then read them | Index Scan / Bitmap Heap Scan |
USING COVERING INDEX |
Answer from the index alone | Index Only Scan |
USING INTEGER PRIMARY KEY |
Direct lookup by row id | Index Scan on the primary key |
USE TEMP B-TREE FOR ORDER BY / GROUP BY |
Build a temporary sorted structure | Sort / HashAggregate |
BLOOM FILTER |
Quick pre-check to skip non-matching join rows | (similar ideas in hash joins) |
Five plans before indexing
| Query | Plan | Time |
|---|---|---|
| Orders for one customer | SCAN orders | 9.3 ms |
| Count in a date range | SCAN orders | 17.2 ms |
| Totals for customers in one country (join) | SCAN o, BLOOM FILTER ON c, SEARCH c USING INTEGER PRIMARY KEY, USE TEMP B-TREE FOR GROUP BY | 60.4 ms |
Count where substr(created, 1, 7) = '2026-03' |
SCAN orders | 23.7 ms |
Latest 10 orders (ORDER BY created DESC LIMIT 10) |
SCAN orders, USE TEMP B-TREE FOR ORDER BY | 19.6 ms |
Every query started with a full scan of 300,000 orders. The join read all orders and looked up each customer by primary key, then built a temporary structure to group. The last query had to read and sort everything just to return 10 rows. Each line is a hint: “SCAN” on a big table with a selective filter means a missing index, and “TEMP B-TREE FOR ORDER BY” means a sort that an index could provide.
The same plans after two indexes
CREATE INDEX ix_orders_customer ON orders(customer_id);
CREATE INDEX ix_orders_created ON orders(created);
ANALYZE;
| Query | Plan | Time |
|---|---|---|
| Orders for one customer | SEARCH orders USING INDEX ix_orders_customer (customer_id=?) | under 0.1 ms |
| Count in a date range | SEARCH orders USING COVERING INDEX ix_orders_created (created>? AND created<?) | 0.8 ms |
| Join by country | SCAN c, SEARCH o USING INDEX ix_orders_customer (customer_id=?) | 47.3 ms |
| substr on created | SCAN orders USING COVERING INDEX ix_orders_created | 17.5 ms |
| Latest 10 orders | SCAN orders USING INDEX ix_orders_created | under 0.1 ms |
The single-customer query became a direct index search. The date range was answered from the index alone, a “covering” read, because counting needs no other columns. The join flipped direction: the planner now scans the 20,000 customers and searches orders by index for each, and the temporary GROUP BY structure disappeared because rows arrive grouped. For the latest orders, “SCAN … USING INDEX” with no temp B-tree means it walked the index from the newest end and stopped after 10 rows. Our SQL indexes guide explains why an index provides order for free.
Functions on columns hide the index
WHERE substr(created, 1, 7) = '2026-03' -- SCAN ... USING COVERING INDEX, 17.5 ms
WHERE created BETWEEN '2026-03-01' AND '2026-03-31' -- SEARCH ... (created>? AND created<?), 0.8 ms
Both filters ask for March, but the first wraps the column in a function, so the database cannot seek to a starting point. It read every index entry and applied substr to each; the plan says SCAN even though an index is mentioned. Rewriting the condition as a range on the raw column turned it into a SEARCH. The same applies to LOWER(email), as our case insensitive search guide measured, and to date functions covered in the SQL date functions guide.
Covering indexes
CREATE INDEX ix_cov ON orders(customer_id, total);
EXPLAIN QUERY PLAN SELECT customer_id, total FROM orders WHERE customer_id = 42;
-- SEARCH orders USING COVERING INDEX ix_cov (customer_id=?)
When every column a query needs is in the index, the database never touches the table itself. “COVERING INDEX” in SQLite and “Index Only Scan” in PostgreSQL are the best access paths you can get. Adding a frequently selected column to the end of an index can turn a hot query into a covering one, at the cost of a bigger index and slower writes.
When an index does not help
CREATE INDEX ix_status ON orders(status); ANALYZE;
SELECT * FROM orders WHERE status = 'paid';
-- SEARCH orders USING INDEX ix_status (status=?) 202.9 ms
SELECT * FROM orders NOT INDEXED WHERE status = 'paid';
-- SCAN orders 195.0 ms (179,596 matching rows)
Seeing “USING INDEX” is not automatically good. 179,596 of 300,000 orders, about 60 percent, were paid, so the index search still had to fetch most of the table, jumping between index and table for each row. A plain scan was slightly faster. Indexes pay off when a filter is selective, returning a small fraction of rows; low-cardinality columns such as status, gender or boolean flags rarely deserve an index of their own, though they can be useful as the second column of a composite index. SQLite’s NOT INDEXED clause, like PostgreSQL’s enable_seqscan setting, is a handy way to compare plans.
OR conditions and leading wildcards
WHERE customer_id = 42 OR created = '2026-03-05'
-- MULTI-INDEX OR: SEARCH USING INDEX ix_orders_customer, SEARCH USING INDEX ix_orders_created
WHERE status LIKE '%aid'
-- SCAN orders
SQLite combined two separate indexes for the OR, searching each and merging the results; PostgreSQL does something similar with a BitmapOr. A pattern starting with a wildcard can never use a normal index, because the index is sorted by the start of the string; see the SQL LIKE guide for alternatives such as full-text search.
Reading PostgreSQL and MySQL plans
-- PostgreSQL: the shape of the output (placeholders, not a real run)
Index Scan using ix_orders_customer on orders (cost=STARTUP..TOTAL rows=ESTIMATE width=BYTES)
Index Cond: (customer_id = 42)
PostgreSQL prints a tree: read it from the most indented node outwards, since children run first. Each node shows an estimated startup and total cost in arbitrary units, estimated rows and row width. MySQL’s EXPLAIN prints a table with one row per table, where type (ALL means full scan, ref and range mean index use), key (the index chosen) and rows (estimated rows examined) are the columns to read first. EXPLAIN FORMAT=TREE in MySQL 8 shows a PostgreSQL-like tree.
EXPLAIN vs EXPLAIN ANALYZE
Plain EXPLAIN shows the plan and estimates. PostgreSQL’s EXPLAIN ANALYZE and MySQL 8’s EXPLAIN ANALYZE actually run the query and add real row counts and timings to each node. The most valuable signal is a big gap between estimated and actual rows, which means the planner’s statistics are wrong and it may be choosing a bad plan; run ANALYZE (or update statistics) and compare again. Because it executes the statement, wrap EXPLAIN ANALYZE of an UPDATE or DELETE in a transaction you roll back. SQLite’s plain EXPLAIN is different again: it prints the low-level bytecode program (Init, OpenRead, Integer and so on), which is rarely what you want for tuning.
A tuning workflow
- Find the slow query with logs or monitoring, and measure it.
- Run EXPLAIN (or EXPLAIN ANALYZE) and look for full scans of big tables, temporary sorts and row estimates far from reality.
- Check the WHERE, JOIN and ORDER BY columns: are they indexed, and is any wrapped in a function?
- Make one change: add or adjust an index, rewrite a condition as a range, or remove an unneeded sort.
- Run EXPLAIN again and measure again. Keep the change only if both improved.
Pagination queries deserve special attention, since large OFFSET values scan and discard rows; our SQL pagination guide compares keyset and offset plans. For broader techniques, see the SQL query optimization guide.
Final thoughts
A SQL explain plan turns query tuning from guesswork into a short feedback loop. Read the access method for each table, watch for SCAN on large tables and temporary sort structures, and keep column expressions out of WHERE clauses. In our tests two well-chosen indexes removed four full scans and a sort, while a third index on a low-selectivity column looked good in the plan and did nothing for speed. Always confirm a plan change with a timing.
Frequently asked questions
What is an explain plan in SQL?
It is the database description of how it will execute a query: which tables it reads, whether it uses indexes or full scans, how it joins tables and whether it sorts. You get it by putting EXPLAIN before the query.
What does a full table scan mean?
The database reads every row of the table. That is fine for small tables or queries returning most rows, but on large tables with a selective filter it usually means an index is missing.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the planned steps with estimates and does not run the query. EXPLAIN ANALYZE runs it and adds actual row counts and timings, which reveals bad estimates.
Why is my index not used?
Common reasons are a function or cast on the column, a leading wildcard in LIKE, outdated statistics, or a filter that matches so many rows that a full scan is cheaper.
What is a covering index?
An index that contains every column a query needs, so the database can answer from the index without reading the table. SQLite reports it as a covering index and PostgreSQL as an index only scan.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.