Skip to content
Tutorials Center
My learning

Topics

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.

SQL
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 7;

Try it

SQL A scan, then a search using an index ยท database: shop

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

SQL Plans for a join and a primary-key lookup ยท database: shop

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

Syntax

SQL
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);
SQL
-- 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) or type: ALL (MySQL) on a large table with a selective WHERE usually means a missing or unusable index.
  • A plan is the optimizer's choice, not a promise. It depends on statistics; run ANALYZE after bulk changes.
  • EXPLAIN ANALYZE really executes the statement. For INSERT, UPDATE or DELETE wrap 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.