Synopsis
Description
TheEXPLAIN
command displays the execution plan that would be generated for the given statement.
Parameters
statement
The SQL statement.
The statement can be any SELECT
, UPDATE
, INSERT
, or DELETE
statement.
Outputs
The output of the command is the execution plan in tabular format, with the following details:Column | Description |
---|---|
ID | Unique identifier assigned to each operation in the query plan. |
Parent ID | The Identifier of the parent operation. |
Operation | The name of the operation, e.g. FILTER, FULL SCAN, JOIN, etc. |
Information | Either the name of the object (e.g. table or index) referenced by the operation, or an expression the operation applies (e.g. a JOIN operation condition). |
FULL SCAN
– a scan of all the rows in a table.
PARTIAL SCAN
– a scan of one or more rows in a table.
VALUES SCAN
– a sequential scan of all the values that are either given in the SQL statement or produced by another operation.
INDEX SCAN
– a scan on an index. That includes full scan, range scan, or lookup of one or more specific values.
INDEX RANGE
– when an index scan has a range predicate, this specifies the range.
FILTER
– filter the results of another operation based on a boolean predicate.
SELECTION
– an operation that applies a set of filters to a set of records produced by another operation. The operation and the filters are children of the SELECTION operation.
SORT
– sort the results of another operation.
UNION
– combine the results of two other operations.
DISTINCT
– eliminate duplicates from a sorted list of records produced by another operation.
JOIN
– intersect the results of two other operations. Each such operation has two parameters, algorithmic and functional. See the list of options below.
GROUP BY
– group the results of another operation according to the value of a certain set of columns. OFFSET – Trim the beginning of the results of another operation by a given number of records.
LIMIT
– Trim the end of the results of another operation, leaving only the first given number of records. INSERT – insert rows into a table.
UPDATE
– update existing rows of a table.
DELETE
– delete rows of a table.
MULTI OP
– this represents a node in the execution tree which aggregates two or more operations that are performed in combination in parallel. (For example, FULL SCAN
and FILTER
.) Those operations are children of this node in the execution tree.
SINGLE ROW SUBQUERY
– a query that is expected to return a single record. If it returns more than one record, the execution fails.
The
UNION
operation stands for combining two sets or records without eliminating duplicate records. When duplicate records should be eliminated, the output contains a DISTINCT
operation which is parent of the UNION
operation, and applies to the result of the UNION
operation.algorithm variant JOIN
algorithm is one of the following: HASH
, MERGE
, LOOP
variant is one of the following: INNER
, LEFT
[SEMI]
, RIGHT
[SEMI]
, FULL
, CROSS
Note: By algorithm we refer to the algorithm used to process the two input record sets inside each module participating in the join operation. There is also the distribution algorithm, which is either
broadcast
or hash partitioning
, but this is not included in the EXPLAIN
output.Examples
Query on a table, filtering by a column that is not indexed:Compatibility
TheEXPLAIN
statement isn’t part of the SQL standard.