1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL EXPLAIN plan: how to read query plans and fix slow queries

Key takeaways

  • 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

  1. Find the slow query with logs or monitoring, and measure it.
  2. Run EXPLAIN (or EXPLAIN ANALYZE) and look for full scans of big tables, temporary sorts and row estimates far from reality.
  3. Check the WHERE, JOIN and ORDER BY columns: are they indexed, and is any wrapped in a function?
  4. Make one change: add or adjust an index, rewrite a condition as a range, or remove an unneeded sort.
  5. 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →