Can my DB?

-- Estimated plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Actual execution with timings
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

-- JSON output format
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 42;

• EXPLAIN shows estimated plan; EXPLAIN ANALYZE executes the query and shows actual row counts and timings.
• Supports multiple output formats: TEXT, JSON, XML, YAML.
• BUFFERS option shows buffer hit/read statistics.
• EXPLAIN ANALYZE will execute DML statements (INSERT/UPDATE/DELETE); wrap in a transaction and rollback to avoid side effects.

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Analyze (executes the query, 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- JSON format
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

• EXPLAIN shows estimated plan with table access types, possible keys, and row estimates.
• EXPLAIN ANALYZE was added in MySQL 8.0.18 and executes the query to show actual timings.
• FORMAT=JSON provides additional cost and optimizer detail.
• EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

ANALYZE SELECT * FROM orders WHERE customer_id = 42;

• EXPLAIN EXTENDED shows additional filtered/extra columns.
• EXPLAIN FORMAT=TREE added in newer versions.
• ANALYZE executes the query and shows actual row counts.

-- Generate plan into PLAN_TABLE
EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;

-- Display the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Actual execution statistics
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

• EXPLAIN PLAN FOR writes the plan to PLAN_TABLE; use DBMS_XPLAN.DISPLAY to view it.
• DBMS_XPLAN.DISPLAY_CURSOR shows the actual plan from the cursor cache.
• The GATHER_PLAN_STATISTICS hint enables actual row count collection.
• Also available: AUTOTRACE in SQL*Plus and the V$SQL_PLAN views.

-- Estimated text plan
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE customer_id = 42;
GO
SET SHOWPLAN_TEXT OFF;
GO

-- Actual execution plan with statistics
SET STATISTICS PROFILE ON;
SELECT * FROM orders WHERE customer_id = 42;
SET STATISTICS PROFILE OFF;

• SET SHOWPLAN_TEXT/XML/ALL ON shows estimated plan without executing the query.
• SET STATISTICS PROFILE/XML ON shows actual plan with execution statistics.
• SQL Server Management Studio provides a graphical execution plan viewer.
• Include Actual Execution Plan button in SSMS is the most common way to analyze plans.

-- Query plan overview
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;

-- Full bytecode explain
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

• EXPLAIN QUERY PLAN shows a high-level overview of table scans, index usage, and join order.
• Plain EXPLAIN shows the low-level VDBE bytecode instructions (rarely used for optimization).
• No EXPLAIN ANALYZE equivalent; SQLite does not report actual execution timings in the plan.
• Output format changed in SQLite 3.24.0 to a tree-like structure.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

• Output shows query plan with estimated costs, node types, and join strategies.
• Redshift's EXPLAIN output uses a different format than PostgreSQL, showing a distributed query plan with slice/segment information.
• EXPLAIN does not execute the query.
• No equivalent of EXPLAIN ANALYZE.

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;

• DB2's explain output is stored in EXPLAIN tables (systools.explain_statement, etc.) rather than returned directly.
• Must call db2expln or use IBM Data Studio to visualize.
• The SET CURRENT EXPLAIN MODE = EXPLAIN statement enables automatic explain capture.
• db2exfmt formats explain output for readability.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

• EXPLAIN is a native SQL statement in Snowflake and returns the logical execution plan.
• Output is tabular/structured rather than a PostgreSQL-style text tree.
• EXPLAIN USING TABULAR is the default; JSON and TEXT output formats are also supported.
• Snowflake does not provide a direct EXPLAIN ANALYZE statement with PostgreSQL-style actual execution timings.

-- Run a query job, then inspect its execution details in the console
-- or via jobs.get / INFORMATION_SCHEMA metadata.
SELECT * FROM orders WHERE customer_id = 42;

• BigQuery does not expose a SQL EXPLAIN statement, but it does expose detailed query plan and timeline metadata for executed queries.
• The Google Cloud console shows an execution graph with stages and step details.
• The API and INFORMATION_SCHEMA metadata expose job statistics including stage-level query plan information.
• This is functionally similar to EXPLAIN-style plan analysis, but retrieved from job metadata rather than inline SQL syntax.