EXPLAINAdministration
Buy Me A Coffee

Display the execution plan the query optimizer chose, optionally running the query to show actual statistics.

Shows the query execution plan (EXPLAIN) or executes the query and shows the plan plus runtime metrics (EXPLAIN ANALYZE). Essential for diagnosing slow queries and understanding how the planner accesses your data. Plans vary significantly between databases.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all EXPLAIN shows estimated plan. EXPLAIN ANALYZE (runs the query, shows actual stats) added in 8.0.18. EXPLAIN FORMAT=JSON or FORMAT=TREE for structured output.
PostgreSQL ✓ Supported all EXPLAIN (estimated) and EXPLAIN ANALYZE (executes and shows actual rows, loops, timing). Rich options: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). The most detailed output of any engine.
SQL Server ✓ Supported all SET SHOWPLAN_XML/SHOWPLAN_TEXT ON before query. SET STATISTICS IO/TIME ON for I/O and timing. Graphical execution plans in SSMS and Azure Data Studio.
Oracle ✓ Supported all Two-step: EXPLAIN PLAN FOR ... stores the plan, then query PLAN_TABLE to retrieve it. DBMS_XPLAN.DISPLAY provides formatted output. AUTOTRACE in SQL*Plus is a common shortcut.
SQLite ✓ Supported all EXPLAIN QUERY PLAN shows a readable high-level plan. EXPLAIN (without QUERY PLAN) shows raw virtual machine bytecode.

Details

PostgreSQL's EXPLAIN (ANALYZE, BUFFERS) gives the richest output of any engine. Oracle's EXPLAIN workflow is uniquely two-step (write to PLAN_TABLE, then read it). SQL Server plans are best viewed graphically in SSMS.

Standard Syntax

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

EXPLAIN shows estimated plan. EXPLAIN ANALYZE (runs the query, shows actual stats) added in 8.0.18. EXPLAIN FORMAT=JSON or FORMAT=TREE for structured output.

EXPLAIN SELECT * FROM tbl WHERE col = 1; EXPLAIN ANALYZE SELECT * FROM tbl WHERE col = 1; -- 8.0.18+ EXPLAIN FORMAT=TREE SELECT * FROM tbl WHERE col = 1;

PostgreSQL

EXPLAIN (estimated) and EXPLAIN ANALYZE (executes and shows actual rows, loops, timing). Rich options: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). The most detailed output of any engine.

EXPLAIN SELECT * FROM tbl WHERE col = 1; EXPLAIN ANALYZE SELECT * FROM tbl WHERE col = 1; EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM tbl WHERE col = 1;

SQL Server

SET SHOWPLAN_XML/SHOWPLAN_TEXT ON before query. SET STATISTICS IO/TIME ON for I/O and timing. Graphical execution plans in SSMS and Azure Data Studio.

SET SHOWPLAN_XML ON; GO SELECT * FROM tbl WHERE col = 1; GO SET SHOWPLAN_XML OFF; -- Or: SET STATISTICS IO ON; SET STATISTICS TIME ON;

Oracle

Two-step: EXPLAIN PLAN FOR ... stores the plan, then query PLAN_TABLE to retrieve it. DBMS_XPLAN.DISPLAY provides formatted output. AUTOTRACE in SQL*Plus is a common shortcut.

EXPLAIN PLAN FOR SELECT * FROM tbl WHERE col = 1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SQLite

EXPLAIN QUERY PLAN shows a readable high-level plan. EXPLAIN (without QUERY PLAN) shows raw virtual machine bytecode.

EXPLAIN QUERY PLAN SELECT * FROM tbl WHERE col = 1; EXPLAIN SELECT * FROM tbl WHERE col = 1; -- bytecode