How to get an execution plan from each database
Every engine has two kinds of plan. An estimated plan is the optimizer’s
prediction and never runs the statement. An actual plan runs the statement and
records real row counts - the only way to see where the optimizer guessed wrong. Actual capture
executes inserts, updates and deletes too, so test data changes inside a transaction you roll
back.
Actual plan - runs the query and returns one plan document per statement, after the results:
SET STATISTICS XML ON;
SELECT ... ; -- your query
SET STATISTICS XML OFF;
Estimated plan - compiles without running. SET SHOWPLAN_XML must be the only statement in its batch, hence the GO separators:
SET SHOWPLAN_XML ON;
GO
SELECT ... ; -- your query
GO
SET SHOWPLAN_XML OFF;
In SSMS, turn on Include Actual Execution Plan (Ctrl+M) or use
Display Estimated Execution Plan (Ctrl+L), then right-click the
graphical plan and choose Save Execution Plan As… to get a .sqlplan file you
can open here, or Show Execution Plan XML… to copy the XML. Plans already in the cache
or in Query Store work too: sys.dm_exec_query_plan(plan_handle) returns the XML, and
sys.query_store_plan.query_plan holds it as text. If you copy a large plan out of the
SSMS results grid and the viewer reports incomplete XML, the grid truncated it (its XML limit is
set under Tools > Options > Query Results > SQL Server > Results to Grid); save the
plan as a file instead.
PostgreSQL: EXPLAIN (FORMAT JSON)
-- Actual plan: runs the query
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ... ;
-- Estimated plan: does not run it
EXPLAIN (FORMAT JSON) SELECT ... ;
-- Measure a data change without keeping it
BEGIN;
EXPLAIN (ANALYZE, FORMAT JSON) UPDATE ... ;
ROLLBACK;
The viewer reads the JSON format, not PostgreSQL’s default text output. Pasting straight from
psql is fine - the QUERY PLAN header and the + at the end of each line are
removed - or run psql -XqAt -c "EXPLAIN (ANALYZE, FORMAT JSON) ..." for clean output.
For plans of queries your application ran, the auto_explain module can log them with
auto_explain.log_format = json.
-- Estimated plan as JSON: does not run the query
EXPLAIN FORMAT=JSON SELECT ... ;
-- Actual plan (MySQL 8.0.18 and later): runs the query
EXPLAIN ANALYZE SELECT ... ;
Both are read, and the viewer tells them apart by shape. The mysql client’s table borders,
\G row banners and “1 row in set” footers are stripped when you paste.
Newer MySQL releases can also emit a second JSON layout (explain_json_format_version = 2);
the viewer reads the original one, so run SET explain_json_format_version = 1; first if
your server defaults to version 2.
-- Estimated plan: does not run the query
EXPLAIN FORMAT=JSON SELECT ... ;
-- Actual plan with r_rows, r_loops and timings: runs the query
ANALYZE FORMAT=JSON SELECT ... ;
EXPLAIN QUERY PLAN SELECT ... ;
SQLite never runs the statement for EXPLAIN QUERY PLAN, and its plans carry no costs or
row estimates - just the steps, which is still enough to spot a SCAN that should be a
SEARCH. The sqlite3 shell’s tree (QUERY PLAN,
|--, `--) is read as-is, and so are the raw
id | parent | notused | detail rows other tools return. In the shell,
.eqp on prints the plan before every statement you run.
-- Estimated plan
EXPLAIN (FORMAT JSON) SELECT ... ;
-- Actual plan with per-operator rows and timing: runs the query
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;
Paste the JSON from the explain_value column. DuckDB has no cost units, so an actual plan shades operators by the time they took.
EXPLAIN json = 1, indexes = 1
SELECT ...
FORMAT TSVRaw;
indexes = 1 adds how many parts and granules each primary key, partition key and skip
index kept; that is how the viewer spots a read that pruned nothing. FORMAT TSVRaw
returns the JSON without escaping. ClickHouse has no EXPLAIN ANALYZE, so its plans are
always estimated.
-- Estimated plan: does not run the query
EXPLAIN PLAN FOR SELECT ... ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Actual plan: run the query with row-source statistics, then read its cursor
SET SERVEROUTPUT OFF
SET LINESIZE 300
SELECT /*+ GATHER_PLAN_STATISTICS */ ... ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
Paste the whole output, including the Predicate Information section - the viewer attaches each
access and filter predicate to its operation. DISPLAY_CURSOR
needs SELECT access to V$SESSION, V$SQL, V$SQL_PLAN and
V$SQL_PLAN_STATISTICS_ALL, granted directly to the user. Keep SERVEROUTPUT off in SQL*Plus or SQLcl, or the
“last cursor” it shows is the output call rather than your query, and widen
LINESIZE so the plan table does not wrap.