EXPLAIN
¶
Explain or analyze the plan for a given statement.
Table of contents
Synopsis¶
EXPLAIN [ ANALYZE | VERBOSE ] statement
EXPLAIN [ ( option [, ...] ) ] statement
where option is:
ANALYZE [ boolean ]
COSTS [ boolean ]
VERBOSE [ boolean ]
Description¶
The EXPLAIN
command displays the execution plan that the planner generates
for the supplied statement. The plan is returned as a nested object containing
the plan tree.
The VERBOSE
option, available through EXPLAIN VERBOSE
or
EXPLAIN (VERBOSE TRUE)
, provides a breakdown of the steps performed by the
optimizer. An example output looks like this:
cr> EXPLAIN VERBOSE
... SELECT employees.id
... FROM employees, departments
... WHERE employees.dept_id = departments.id AND departments.name = 'IT';
+------------------------------------+----------------------------------------------------------------------+
| STEP | QUERY PLAN |
+------------------------------------+----------------------------------------------------------------------+
| Initial logical plan | Eval[id] (rows=0) |
| | └ Filter[(name = 'IT')] (rows=0) |
| | └ Join[INNER | (dept_id = id)] (rows=3) |
| | ├ Collect[doc.employees | [id, dept_id] | true] (rows=18) |
| | └ Collect[doc.departments | [id, name] | true] (rows=6) |
| optimizer_move_filter_beneath_join | Eval[id] (rows=3) |
| | └ Join[INNER | (dept_id = id)] (rows=3) |
| | ├ Collect[doc.employees | [id, dept_id] | true] (rows=18) |
| | └ Filter[(name = 'IT')] (rows=1) |
| | └ Collect[doc.departments | [id, name] | true] (rows=6) |
| optimizer_rewrite_join_plan | Eval[id] (rows=3) |
| | └ HashJoin[(dept_id = id)] (rows=3) |
| | ├ Collect[doc.employees | [id, dept_id] | true] (rows=18) |
| | └ Filter[(name = 'IT')] (rows=1) |
| | └ Collect[doc.departments | [id, name] | true] (rows=6) |
| optimizer_merge_filter_and_collect | Eval[id] (rows=3) |
| | └ HashJoin[(dept_id = id)] (rows=3) |
| | ├ Collect[doc.employees | [id, dept_id] | true] (rows=18) |
| | └ Collect[doc.departments | [id, name] | (name = 'IT')] (rows=1) |
| Final logical plan | Eval[id] (rows=3) |
| | └ HashJoin[(dept_id = id)] (rows=3) |
| | ├ Collect[doc.employees | [id, dept_id] | true] (rows=18) |
| | └ Collect[doc.departments | [id] | (name = 'IT')] (rows=1) |
+------------------------------------+----------------------------------------------------------------------+
EXPLAIN 5 rows in set (... sec)
When issuing EXPLAIN ANALYZE
or EXPLAIN (ANALYZE TRUE)
the plan of the
statement is executed and timings of the different phases of the plan are returned.
The COSTS
option is by default enabled and can be disabled by issuing
EXPLAIN (COSTS FALSE)
. The output of the execution plan does then exclude
the costs for each logical plan.
Note
The content of the returned plan tree as well as the level of detail of the timings of the different phases should be considered experimental and are subject to change in future versions. Also not all plan nodes provide in-depth details.
The output of EXPLAIN ANALYZE
also includes a break down of the query
execution if the statement being explained involves queries which are executed
using Lucene.
Note
When a query involves an empty partitioned table you will see no breakdown concerning that table until at least one partition is created by inserting a record.
The output includes verbose low level information per queried shard. Since SQL query expressions do not always have a direct 1:1 mapping to Lucene queries, the output may be more complex but in most cases it should still be possible to identify the most expensive parts of a query expression. Some familiarity with Lucene helps in interpreting the output.
A short excerpt of a query breakdown looks like this:
{
"BreakDown": {
"advance": 0,
"advance_count": 0,
"build_scorer": 0,
"build_scorer_count": 0,
"compute_max_score": 0,
"compute_max_score_count": 0,
"create_weight": 0.004095,
"create_weight_count": 1,
"match": 0,
"match_count": 0,
"next_doc": 0,
"next_doc_count": 0,
"score": 0,
"score_count": 0
},
"QueryDescription": "x:[1 TO 1]",
"QueryName": "PointRangeQuery",
"SchemaName": "doc",
"ShardId": 0,
"TableName": "employees",
"Time": 0.004096
}
The time values are in milliseconds. Fields suffixed with _count
indicate
how often an operation was invoked.
If the query is executed on a partitioned table, each query breakdown will also
contain the related PartitionIdent
entry.
Field |
Description |
---|---|
|
A |
|
A |
|
Shows the time it takes to score a particular document via its
|
|
Shows the time it takes to determine which document is the next match. |
|
A lower level version of |
|
Some queries use a two-phase execution, doing an |
|
Records the number of invocations of the particular method. For
example, |
Note
Individual timings of the different phases and queries that are profiled do
not sum up to the Total
. This is because there is usually additional
initialization that is not measured. Also, certain phases do overlap during
their execution.
Parameters¶
- statement:
The statement for which a plan or plan analysis should be returned.
Currently only
SELECT
andCOPY FROM
statements are supported.