As SQL is a declarative language, the order (and kind) of operations in the actual execution of a particular query can be very different from those specified in the query itself. For example, join sides can be flipped, filters and projections can be pushed down below other operations, etc.
This most often means that the query optimizer deduced that some operations could be performed more efficiently, or even that some operations need not be performed at all (e.g. sorting inputs before aggregating them).
In most cases, that will result in an improved query performance, either in execution time or memory usage, and often both. But there are some cases when either the query optimizer might make a wrong assumption somewhere in the process and produce a worse-performing execution plan, or the original query was written in such a way that some reasonable optimizations could not be made (and there are many such potential pitfalls in SQL, especially when dealing with NULL values).
In either case, to help investigating potential performance issues, the MSQL provides the EXPLAIN statement which shows the actual execution plan produced for the given query by the query planner/optimizer.
EXPLAIN statementThe EXPLAIN statement displays details about the steps that will be taken during the execution of a single query, or about the expected query result.
The syntax of the EXPLAIN statement is:
EXPLAIN [(PHYSICAL | LOGICAL | TYPE)] [PLAN FOR] query
There are three variants of the EXPLAIN statement:
EXPLAIN PHYSICAL PLAN shows the physical execution plan, including the distribution of the parts of the query to different DBS-es. This is the default variant when none is specified (i.e. when the query is just EXPLAIN [PLAN FOR] ...).
EXPLAIN LOGICAL PLAN shows the logical execution plan, i.e. what operations (filtering, aggregating, joining) would be done (and in what order) during the execution of the query.
EXPLAIN TYPE PLAN shows the type of the query result, i.e. what would be the names and types of the result columns if the query were to be executed.
The following are the examples of each variant:
EXPLAIN -- (PHYSICAL PLAN FOR) (default)
SELECT registration_number, count(*) FROM st.segment JOIN ndb.vehicle ON vid = id WHERE vid = 1324 GROUP BY 1;
--> +----+-----------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
--> | id | parent_id | operation | details |
--> +----+-----------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
--> | 1 | NULL | 'Collect (MSQL)' | '' |
--> | 2 | 1 | 'Aggregate (MSQL)' | 'group: (1); aggregates: (COUNT())' |
--> | 3 | 2 | 'Join (MSQL)' | 'type: inner; kind: hash; condition: (true); left exprs: ($0); right exprs: ($0); hrect exprs: ([$0, $0 + 1, m, M, m, M, m, M, m, M, m, M, m, M, m, M, m, M])' |
--> | 4 | 3 | 'Ext Query (MYSQL)' | 'query: (SELECT id, registration_number FROM vehicle WHERE 1324 = id)' |
--> | 5 | 3 | 'Gather (ST)' | '' |
--> | 6 | 5 | 'Project (ST)' | 'exprs: ($0)' |
--> | 7 | 6 | 'Range query (ST)' | 'table: segment; hrects: ([1324..1325, *, *, *, *, *, *, *, *])' |
--> +----+-----------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN LOGICAL PLAN FOR
SELECT registration_number, count(*) FROM st.segment JOIN ndb.vehicle ON vid = id WHERE vid = 1324 GROUP BY 1;
--> +----+-----------+--------------------+------------------------------------------------------------------+
--> | id | parent_id | operation | details |
--> +----+-----------+--------------------+------------------------------------------------------------------+
--> | 1 | NULL | 'Aggregate' | 'group: (1); aggregates: (COUNT())' |
--> | 2 | 1 | 'Join' | 'type: inner; kind: none; condition: ($0 = $2)' |
--> | 3 | 2 | 'Project' | 'exprs: ($0; $3)' |
--> | 4 | 3 | 'Filter' | 'condition: ($0 = 1324)' |
--> | 5 | 4 | 'Table Scan (MY)' | 'table: vehicle' |
--> | 6 | 2 | 'Project' | 'exprs: ($0)' |
--> | 7 | 6 | 'Range query (ST)' | 'table: segment; hrects: ([1324..1325, *, *, *, *, *, *, *, *])' |
--> +----+-----------+--------------------+------------------------------------------------------------------+
EXPLAIN TYPE PLAN FOR
SELECT sum(len) AS total_len
FROM st.trip JOIN ndb.vehicle ON vid=id
WHERE vid IN (100, 101, 105, 118)
AND t[0] BETWEEN timestamp '2020-09-01' AND timestamp '2020-09-15';
--> +-------------+--------+----------+
--> | name | type | nullable |
--> +-------------+--------+----------+
--> | 'total_len' | 'f64?' | true |
--> +-------------+--------+----------+