## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR explainStatement_start((START)) explainStatement_start --> explainStatement_0_0[EXPLAIN]:::quoted explainStatement_0_0 --> explainStatement_0_1[ANALYZE]:::quoted explainStatement_0_0 --> explainStatement_0_2[<a href="Invantive UniversalSQL/Grammar/Query Statement/Select Statement#selectStatement" class="internal-link">selectStatement</a>] explainStatement_0_1 --> explainStatement_0_2 explainStatement_0_2 --> explainStatement_end((END)) ``` ## Purpose The `explain` statement shows how Invantive UniversalSQL executes a `select` statement. It returns one row per operation of the execution plan, such as the read of a table, a join, a filter or a sort. With `analyze`, the `select` statement is executed to the end and its rows are discarded. The `explain analyze` statement then returns one row per step that was executed, with the number of rows, the duration and what the step counted. The last row has the kind `Total` and holds the totals of the statement. Only a `select` statement can be explained, so `explain analyze` never changes data. The words `explain` and `analyze` remain available as names of columns, aliases and tables. Available from release 28.0 and the beta release 27.1 before it. The columns of the result: - `STEP_ID` and `PARENT_STEP_ID`: the number of the operation and of the operation that consumes its rows. The first row is the explained statement itself, which has no parent. The row of the totals has no number. - `DEPTH`: the depth below the explained statement. - `KIND`: what the operation does, such as `DataContainerRead`, `Join`, `SemiJoin`, `Filter`, `Sort`, `GroupBy`, `Top` or `Projection`. - `OPERATION`, `ALIAS` and `PARAMETERS`: the operation in words, the alias of its result and what it works on, such as the condition of a join or the order of a sort. - `ALGORITHM`: how a join matches its rows. The values are `index join on columns`, `index join on expressions` and `nested loop`, followed for a nested loop by the reason why no index could be used. When both tables of a join declare their number of rows, an index join on columns is followed by what the cost model decided on the key set: `key set of at most ... keys` when the distinct values of the left side may be handed to the right side as a filter, or `left input streamed, no key set` when the right side is estimated far smaller. When the cost model joined the tables in another order than the `from` clause, the operation `Columns in the order of the from clause` shows `join order by estimate:` followed by the aliases in the order chosen. For `explain` this is the plan. For `explain analyze` it is the choice that was made on the actual rows. - `ESTIMATED_ROWS`: for the read of a table, the number of rows of the table as the data container declares it, before any filter. For a join and a filter, the number of rows the cost model expects, worked out from the declared rows, the declared primary key and the conditions. It is empty when the data container declares nothing. Compare it with `ROWS_OUT` of the same row. - `ROWS_IN`, `ROWS_OUT`, `DURATION_MS` and `HEAP_BYTES_ALLOCATED`: the rows received and handed on, the duration in milliseconds and the bytes allocated, both including the steps below. On the row of the totals, `ROWS_IN` holds the rows read from all tables and `ROWS_OUT` the rows returned by the statement. - `DURATION_EXCLUSIVE_MS`, `CPU_EXCLUSIVE_MS` and `HEAP_BYTES_EXCLUSIVE`: the wall-clock time in milliseconds, the processor time in milliseconds and the bytes allocated by the step itself, without the steps below it. Rows are pulled through the plan from the top, so a table read lives from its first row to its last and `DURATION_MS` of that read also holds the time its consumers spent between two of its rows. The exclusive figures are measured instead on every request for a row: from the moment the consumer asks until the row is handed over, less the same figure of the steps below. They show where the time and the memory of a statement went. A step whose rows are not requested through the operators of the engine has empty exclusive figures, and its work is counted in the step above it. The processor time and the allocation are those of the thread which requested the rows; when a request of a step continues on another thread, as with a data container that waits for a platform, its processor time and allocation are empty, and so are those of the steps above it, which continued on that thread too. The wall-clock time is always measured. On the row of the totals, the three columns hold the sums over all steps, which add up to the figures of the explained statement. Available from release 28.0 and the beta release 27.1 before it. - One column per counter, counted by the step itself and not by the steps below it. `PLAN_BUILDS` counts the execution plans built, also those of a subquery that is planned again for every row. `EXPRESSION_CACHE_HITS` and `EXPRESSION_CACHE_MISSES` count the expressions found in or added to the cache of compiled expressions. `JOINS_BY_COLUMN_INDEX`, `JOINS_BY_EXPRESSION_INDEX` and `JOINS_BY_NESTED_LOOP` count the algorithm a join chose. `PAIRS_EVALUATED` counts the combinations of rows on which a join evaluated its condition. `ROWS_SORTED` counts the rows a sort put in order. `JOIN_ORDERS_CHOSEN_BY_ESTIMATE` counts the blocks of joins the cost model joined in another order than written, `JOIN_KEY_SETS_PUSHED` the joins which handed the distinct values of their left side to their right side as a filter, and `ROW_CAPS_BEHIND_FILTER` the reads of a table which handed the number of rows of a `limit` to the data container together with the conditions of the `where` clause. The other counters describe details of joins, of correlated subqueries and of layers which hand on their rows unchanged. For `explain`, the columns that are only known after execution are empty. ## Cost model Where the data containers declare the number of rows of their tables, Invantive UniversalSQL uses these declarations to plan a statement: - A block of inner joins over tables is joined in the order which is estimated to read the fewest rows, for example the small table first and the large table last, so that the values of the small table narrow down the large one. This is only done when the statement has an `order by`, because without it the rows come in the order of the `from` clause, and not when the block holds an outer join, an aggregate, a grouping, `row_number`, `rank` or `dense_rank`, or a join hint. Rows with equal values for the `order by` may come in another order than before. - A join hands up to half the estimated rows of a large right side, and at most 100,000 values, of its left side to the right side as a filter, instead of at most 10,000 values. A `join_set` or `no_join_set` hint for the join, and the setting `SQL_JOIN_SET_MAX_NUMBER_ROWS`, take precedence. - A `limit` below a `where` clause is handed to the data container together with the conditions, when the whole `where` clause consists of conditions the data container receives and the data container declares that it then applies every condition exactly before the limit, or ignores the limit. A driver which declares nothing is planned as before. The runtime settings `Invantive.Sql.V1.CostModel` switch the cost model off for an incident: `JoinOrderEnabled`, `JoinSetEnabled` and `RowCapEnabled`, each `true` by default. They are the section `Invantive` of the `configProperties` of `runtimeconfig.json` and are read when the process starts. Measuring the exclusive figures costs a reading of the processor time per row per step. It is only done for `explain analyze`; a statement that is not explained is not measured. The same step statistics, one line of JSON per step, are written by [[Invantive Data Hub/Command Line Options|Invantive Data Hub]] with `/steps` and returned by the tool `analyze_records` of [[Invantive MCP Server/Tools|Invantive MCP Server]]. The same figures are available afterwards for every retained statement in [[Invantive-SYSTEMSQLEXECUTIONSTEPS|SystemSqlExecutionSteps@DataDictionary]], in the columns `KIND`, `ALGORITHM`, `ESTIMATED_ROWS` and `COUNTERS`. ## Examples The following example shows the plan of a join on two tables of the data dictionary. The join is planned as an index join, since its condition is an equality of two columns: ```sql explain select pkg.package_name , fct.function_name from systempackages@datadictionary pkg join systempackagefunctions@datadictionary fct on fct.package_name = pkg.package_name STEP_ID PARENT_STEP_ID DEPTH KIND OPERATION ALIAS ALGORITHM ------- -------------- ----- ----------------- ------------------------------------------------------ ----- --------------------- 1 0 Statement Explained statement 2 1 1 Projection Select columns 3 2 2 Projection Column chaining into wider list 4 3 3 Join Inner join by index join on columns 5 4 4 DataContainerRead Data container I/O on 'PKG' (SYSTEMPACKAGES) PKG 6 4 4 DataContainerRead Data container I/O on 'FCT' (SYSTEMPACKAGEFUNCTIONS) FCT ``` The following example executes a join whose condition is not an equality, and shows the steps with the rows and the number of pairs evaluated. The figures depend on the release: ```sql explain analyze select pkg.package_name , count(*) from systempackages@datadictionary pkg join systempackagefunctions@datadictionary fct on fct.package_name < pkg.package_name group by pkg.package_name order by pkg.package_name limit 3 STEP_ID KIND OPERATION ALGORITHM ROWS_IN ROWS_OUT PAIRS_EVALUATED ROWS_SORTED ------- ----------------- ----------------------------------------------------- ----------------------------------------- ------- -------- --------------- ----------- 1 Statement Explained statement 3 0 0 2 ResultSet Data container I/O on '' 3 3 0 0 3 Top Top 3 3 0 0 4 ResultSet Data container I/O on '' 3 3 0 0 5 Sort Order by 3 3 0 18 6 Projection Select columns combined 18 18 0 0 7 GroupBy Group by 2295 18 0 0 8 Projection Column chaining into wider list 2295 2295 0 0 9 Join Inner join by nested loop: no equality of the two sides 150 2295 3689 0 10 DataContainerRead Data container I/O on 'FCT' (SYSTEMPACKAGEFUNCTIONS) 119 119 0 0 11 DataContainerRead Data container I/O on 'PKG' (SYSTEMPACKAGES) 31 31 0 0 Total Total of the statement 150 3 3689 18 ```