EXPLAIN Statement

Display logical or physical execution plan for the SQL statement.

Plain EXPLAIN defaults to logical plan.

Use LOGICAL UNNEST | LOGICAL REWRITE | PHYSICAL to specify compilation step to show.

Use BEFORE | AFTER to specify moment of compilation step to output. The default is AFTER.

Two positive numbers can be passed to LOGICAL REWRITE to stop at specific rewriter index or rewrite loop cycle, respectively.

If only one positive number is passed to LOGICAL REWRITE, rewrite stop cycle defaults to 0.

SHOW DETAILS displays column properties, rewriter number of changes and time spent.

Syntax

EXPLAIN [BEFORE | AFTER] [step] [SHOW DETAILS] statement
	where step is LOGICAL UNNEST | LOGICAL REWRITE [posint] [posint] | PHYSICAL

Examples

-- Logical plan
EXPLAIN SELECT count(*) FROM sys.tables;
EXPLAIN SHOW DETAILS SELECT count(*) FROM sys.tables;
EXPLAIN BEFORE LOGICAL UNNEST SELECT count(*) FROM sys.tables;
EXPLAIN AFTER LOGICAL REWRITE SELECT count(*) FROM sys.tables;

-- Physical plan
EXPLAIN PHYSICAL SELECT count(*) FROM sys.tables;
EXPLAIN BEFORE PHYSICAL SELECT count(*) FROM sys.tables;
EXPLAIN AFTER PHYSICAL SELECT count(*) FROM sys.tables;