EXPLAIN: Inspect Query Plans
EXPLAIN SELECT * FROM tbl; QUERY PLAN------------------------------- ┌───────────────────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ │ │ │ Projections: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ │ │ │ ~3 rows │ └───────────────────────────┘The EXPLAIN statement displays the physical plan, i.e., the query plan that will get executed,
and is enabled by prepending the query with EXPLAIN.
The physical plan is a tree of operators that are executed in a specific order to produce the result of the query.
To generate an efficient physical plan, the query optimizer transforms the existing physical plan into a better physical plan.
To demonstrate, see the below example:
EXPLAIN SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%'; QUERY PLAN------------------------------------------------------------ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ name │ │ │ │ ~1 row │ (varies) │ HASH_JOIN │ │ ──────────────────── │ │ Join Type: INNER │ │ Conditions: sid = sid ├──────────────┐ │ │ │ │ ~1 row │ │ (varies) │ SEQ_SCAN ││ SEQ_SCAN │ │ ──────────────────── ││ ──────────────────── │ │ Table: exams ││ Table: students │ │ Type: Sequential Scan ││ Type: Sequential Scan │ │ Projections: sid ││ │ │ ││ Projections: │ │ ││ sid │ │ ││ name │ │ ││ │ │ ││ Filters: │ │ ││ ((name >= 'Ma') AND (name │ │ ││ < 'Mb')) │ │ ││ │ │ ~3 rows ││ ~1 row │ └───────────────────────────┘└───────────────────────────┘Note that the query is not actually executed – therefore, we can only see the estimated cardinality (shown as the ~N rows line) for each operator, which is calculated by using the statistics of the base tables and applying heuristics for each operator.
Table scan operators display the fully qualified table name including catalog and schema (e.g., memory.myschema.mytable).
Additional Explain Settings
The EXPLAIN statement supports additional settings that can be used to control the output. These settings are controlled via the explain_output pragma. The following values are available:
physical_only is the default setting. It shows only the physical plan.
PRAGMA explain_output = 'physical_only';EXPLAIN SELECT * FROM tbl; QUERY PLAN------------------------------- ┌───────────────────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ │ │ │ Projections: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ │ │ │ ~3 rows │ └───────────────────────────┘optimized_only shows only the optimized plan.
PRAGMA explain_output = 'optimized_only';EXPLAIN SELECT * FROM tbl; QUERY PLAN------------------------------- ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ Expressions: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ │ │ │ ~3 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ │ │ │ ~3 rows │ └───────────────────────────┘all shows both the physical and optimized plans.
PRAGMA explain_output = 'all';EXPLAIN SELECT * FROM tbl; QUERY PLAN------------------------------- ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ Expressions: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ Expressions: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ │ │ │ ~3 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ │ │ │ ~3 rows │ └───────────────────────────┘ ┌───────────────────────────┐ │ SEQ_SCAN │ │ ──────────────────── │ │ Table: tbl │ │ Type: Sequential Scan │ │ │ │ Projections: │ │ id │ │ name │ │ col1 │ │ col2 │ │ Some Column Name │ │ i │ │ a │ │ b │ │ c │ │ number │ │ number1 │ │ number2 │ │ │ │ ~3 rows │ └───────────────────────────┘See Also
For more information, see the "Profiling" page.