SQL EXPLAIN

What does the SQL EXPLAIN statement do?

Displays the execution plan the database optimizer will use to run a given SQL statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EXPLAIN: PostgreSQL, MySQL, MariaDB, Oracle, SQL Server (via SHOWPLAN), SQLite, Snowflake, Redshift, BigQuery (EXPLAIN), DuckDB

SQL EXPLAIN Full Explanation

EXPLAIN is a diagnostic keyword that asks the database engine to reveal how it will execute (or has executed) a query. The output, often called the execution plan, lists each processing step, the order of operations, estimated or actual rows processed, cost estimates, and sometimes I/O, CPU, or memory statistics. By studying the plan, developers can verify that appropriate indexes are used, detect full table scans, gauge join algorithms, and identify performance bottlenecks.Behavior differs slightly by dialect:- PostgreSQL: EXPLAIN returns estimated plans. Adding ANALYZE actually runs the query and appends real execution times and row counts.- MySQL and MariaDB: EXPLAIN gives an estimated plan; EXPLAIN ANALYZE (v8.0+) executes the statement and produces timing data.- Oracle: EXPLAIN PLAN populates the PLAN_TABLE; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) is required to view it.- SQL Server: SET SHOWPLAN_XML ON or the GUI Display Estimated Execution Plan serves the same purpose.- SQLite: EXPLAIN QUERY PLAN shows a simplified plan.Key caveats:- Plain EXPLAIN does not mutate data; with ANALYZE it WILL execute the statement and may modify data for INSERT/UPDATE/DELETE queries.- Cost units are relative to the optimizer and are not wall-clock times.- Output format varies (text, XML, JSON); always consult the dialect docs when parsing programmatically.

SQL EXPLAIN Syntax

-- PostgreSQL style
EXPLAIN [ ( option [, ...] ) ]
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE
UPDATE users SET last_login = NOW() WHERE id = 15;

SQL EXPLAIN Parameters

ANALYZE (boolean) - Execute the statement and append actual run statistics.
VERBOSE (boolean) - Include additional details such as target columns.
COSTS (boolean) - Show estimated startup and total costs.
BUFFERS (boolean) - Include shared/local/temp page statistics (requires ANALYZE).
FORMAT (text) - Output format: TEXT, XML, JSON, YAML.
TIMING (boolean) - Show per-node timing when ANALYZE is on.
Note - Exact option names vary by dialect; if unsupported, specify "None".

Example Queries Using SQL EXPLAIN

-- Identify whether an index is used
EXPLAIN SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

-- Compare estimated vs actual execution
EXPLAIN SELECT * FROM line_items WHERE order_id = 99999;
EXPLAIN ANALYZE SELECT * FROM line_items WHERE order_id = 99999;

-- JSON formatted plan for programmatic analysis
EXPLAIN (FORMAT JSON) SELECT * FROM users u JOIN teams t ON u.team_id = t.id;

Expected Output Using SQL EXPLAIN

Each EXPLAIN statement returns a result set (or text block) detailing the plan tree
Columns or JSON keys typically include node type (Seq Scan, Index Scan, Hash Join, etc
), estimated rows, estimated cost, and for ANALYZE variants, actual time and rows
No data rows from the original query are returned

Use Cases with SQL EXPLAIN

Tuning slow queries by inspecting join order and scan types.
Verifying that newly created indexes are picked up by the optimizer.
Comparing execution plans before and after schema or configuration changes.
Capturing baseline performance metrics in CI pipelines.
Teaching newcomers how the optimizer works.

Common Mistakes with SQL EXPLAIN

Assuming plain EXPLAIN shows real execution times – it only shows estimates.
Forgetting that EXPLAIN ANALYZE actually runs data-modifying statements.
Misreading cost units as milliseconds; they are arbitrary optimizer units.
Ignoring row estimate mismatches which can signal outdated statistics.
Parsing the text format instead of using stable JSON/XML when tooling requires it.

First Introduced In

PostgreSQL 6.0 (1997); similar functionality later adopted by other databases

Frequently Asked Questions

Does EXPLAIN change my data?

No. EXPLAIN only inspects the query. However, EXPLAIN ANALYZE will run the statement, so an UPDATE or DELETE will modify rows.

Why are the costs not in milliseconds?

Cost is an internal unit used by the optimizer to compare alternate plans. It combines estimated I/O and CPU work, not elapsed time.

How can I get the plan in JSON?

In PostgreSQL use: `EXPLAIN (FORMAT JSON) SELECT ...;`. MySQL 8.0 has `EXPLAIN FORMAT=JSON`.

What if the estimated rows are far off?

Large gaps between estimated and actual rows indicate stale statistics or data skew. Run ANALYZE (or equivalent) to refresh stats, or create more selective indexes.