EXPLAIN

Administration
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

Filter by Database
SQL EXPLAIN Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 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 Native 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 Native 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 Native 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 Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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