SQL Reference ยท Statements and Clauses
SQL EXPLAIN statement
Reference for SQL EXPLAIN: see how the database will run a query, spot full table scans and index use, and compare EXPLAIN across SQLite, PostgreSQL and MySQL.
EXPLAIN asks the database to describe how it would execute a statement: which tables it reads first, whether it uses an index or scans the whole table, how it joins, and in what order. It does not return the data. It is the main tool for finding out why a query is slow.
EXPLAIN is not part of the SQL standard, so every database spells it differently. In SQLite the useful form is EXPLAIN QUERY PLAN; plain EXPLAIN there prints low-level virtual machine instructions.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 7;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
EXPLAIN [options] statement; EXPLAIN QUERY PLAN statement; -- SQLite EXPLAIN ANALYZE statement; -- PostgreSQL, MySQL 8.0.18+
| Part | Description |
|---|---|
statement |
A SELECT, INSERT, UPDATE or DELETE to analyze. |
QUERY PLAN |
SQLite: show the high-level plan instead of bytecode. |
ANALYZE |
Actually run the statement and report real timings and row counts. |
Returns: a result set describing the plan. In SQLite each row has an id, a parent id and a detail text such as SCAN orders or SEARCH orders USING INDEX idx_orders_customer (customer_id=?). Indented rows are nested steps.
Dialect differences
| Dialect | How to see the plan |
|---|---|
| SQLite | EXPLAIN QUERY PLAN ...; the shell also has .eqp on. SCAN means reading every row; SEARCH means using an index or rowid lookup. |
| PostgreSQL | EXPLAIN ... shows estimates; EXPLAIN (ANALYZE, BUFFERS) ... runs the query and adds actual times. |
| MySQL | EXPLAIN ... gives a table with type, key and rows; EXPLAIN ANALYZE (8.0.18+) runs it; FORMAT=JSON or TREE for detail. |
| SQL Server | SET SHOWPLAN_XML ON or SET SHOWPLAN_ALL ON, or "Display Estimated Execution Plan" in the client tools. |
| Oracle | EXPLAIN PLAN FOR ...; then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
-- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 7; -- Oracle EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 7; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Usage notes
- Look for scans on big tables.
SCAN(SQLite),Seq Scan(PostgreSQL) ortype: ALL(MySQL) on a large table with a selectiveWHEREusually means a missing or unusable index. - A plan is the optimizer's choice, not a promise. It depends on statistics; run
ANALYZEafter bulk changes. EXPLAIN ANALYZEreally executes the statement. ForINSERT,UPDATEorDELETEwrap it in a transaction and roll back.- Estimates vs actuals: large gaps between estimated and actual row counts point to stale statistics.
- Functions on columns (
WHERE LOWER(email) = ...) usually prevent index use; see sargable predicates. - Tiny tables are legitimately scanned: the optimizer is right to skip an index.
- Compare plans before and after a change, on realistic data volumes.