> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.
# EXPLAIN
Outputs the execution plan of a given SQL statement.
For the visual explain plan utility in *GAdmin*, see the *Explain*
feature under [SQL Tool](/content/admin/gadmin/query#gadmin-sql).
```sql title="EXPLAIN Syntax" theme={null}
EXPLAIN [PHYSICAL|ANALYZE|VERBOSE|VERBOSE ANALYZE] [FORMAT ]
```
Each supporting API endpoint call that is made in servicing the request is
listed as an execution plan step in the output, along with any input or output
tables associated with the call and the prior plan execution steps on which a
given execution step depends.
The following options can be specified:
* `PHYSICAL` - *(default)* outputs the physical execution plan with the
following endpoint-level details per step:
* `ID` - execution step number
* `ENDPOINT` - name of native API endpoint called
* `INPUT_TABLES` - input tables used by the endpoint *(if any)*
* `OUTPUT_TABLE` - output table created by the endpoint *(if any)*
* `DEPENDENCIES` - list of prior execution steps upon which this step
depends
* `ANALYZE` - same as `PHYSICAL`, including additional run-time details:
* `RUN_TIME` - execution time of each endpoint call
* `RESULT_ROWS` - number of records produced in the endpoint call
* `VERBOSE` - same as `PHYSICAL`, including endpoint parameter details:
* `COLUMNS` - columns passed to the endpoint call
* `EXPRESSIONS` - expressions passed to the endpoint call
* `OPTIONS` - option keys & values passed to the endpoint call
* `LAST_USE_TABLES` - list of tables that will not be used by any following
execution step
* `ADDITIONAL_INFO` - other parameters passed to the endpoint call
* `VERBOSE ANALYZE` - same as `VERBOSE` & `ANALYZE` together, including
the execution plan for any joins contained within the query
* `FORMAT JSON` - outputs the result in JSON format
* `FORMAT TABLE` - *(default)* outputs the result in tabular format
For `PHYSICAL` & `VERBOSE` modes, the
`/* KI_HINT_RECURSIVE_EXPLAIN */` hint can be used to show the plan
recursively through any materialized views involved in the query.
Specifying `ANALYZE` will cause the statement to be executed
in order to collect run-time statistics on the endpoint calls made.
For example, to output the execution plan for a query that aggregates the number
of taxi rides between boroughs:
```sql EXPLAIN Example theme={null}
EXPLAIN
SELECT
n_begin.ntaname AS boro_begin,
boro_end,
COUNT(*) AS total_trips
FROM
(
SELECT pickup_latitude, pickup_longitude, n_end.ntaname AS boro_end
FROM demo.nyctaxi t
JOIN example_geospatial.nyc_neighborhood n_end ON STXY_INTERSECTS(dropoff_longitude, dropoff_latitude, geom)
)
JOIN example_geospatial.nyc_neighborhood n_begin ON STXY_INTERSECTS(pickup_longitude, pickup_latitude, geom)
GROUP BY 1, 2
```
The execution plan is listed in table format, as follows:
```sql EXPLAIN Output theme={null}
+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+
| ID | ENDPOINT | INPUT_TABLES | OUTPUT_TABLE | DEPENDENCIES |
+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+
| 0 | /create/jointable | demo.nyctaxi AS TableAlias_0_,example_geospatial.nyc_neighborhood AS TableAlias_1_ | sys_sql_temp.Join_3_01234567_89ab_cdef_0123_456789abcdef | -1 |
| 1 | /create/jointable | sys_sql_temp.Join_3_01234567_89ab_cdef_0123_456789abcdef AS TableAlias_0_,example_geospatial.nyc_neighborhood AS TableAlias_1_ | sys_sql_temp.Join_5_01234567_89ab_cdef_0123_456789abcdef | 0 |
| 2 | /aggregate/groupby | sys_sql_temp.Join_5_01234567_89ab_cdef_0123_456789abcdef | sys_sql_temp.ShardedAggregate_7_01234567_89ab_cdef_0123_456789abcdef | 1 |
| 3 | /get/records/bycolumn | sys_sql_temp.ShardedAggregate_7_01234567_89ab_cdef_0123_456789abcdef | | 2 |
+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+
```
If there is an error processing a query, the error can be returned in the
JSON-formatted execution plan:
```sql EXPLAIN with Detail Example theme={null}
EXPLAIN VERBOSE ANALYZE FORMAT JSON
SELECT * /* KI_HINT_NO_DISTRIBUTED_OPERATIONS */
FROM example.explain_table t1
JOIN example.explain_table t2 ON t1.shard_column = t2.not_shard_column
```
The execution plan is listed in JSON format with the query error, as follows:
```EXPLAIN with Detail Output theme={null}
"PLAN": [
{
"ADDITIONAL_INFO": "Not all of the non-replicated tables are equated by shard keys. Number of sharded sets: 2, Number of components connected: 0 ([)",
"COLUMNS": "TableAlias_0_.shard_column AS shard_column,TableAlias_0_.not_shard_column AS not_shard_column,TableAlias_1_.shard_column AS shard_column0,TableAlias_1_.not_shard_column AS not_shard_column0",
"DEPENDENCIES": "-1",
"ENDPOINT": "/create/jointable",
"EXPRESSIONS": "inner join TableAlias_0_, TableAlias_1_ on (TableAlias_0_.shard_column = TableAlias_1_.not_shard_column ) ",
"ID": "0",
"INFO": "{"estimate_input_row_counts":"1.0, 1.0","estimate_row_count":"1.0"}",
"JSON_REQUEST": "{
"join_table_name": "Join_1_01234567_89ab_cdef_0123_456789abcdef",
"table_names": [
"example.explain_table AS TableAlias_0_",
"example.explain_table AS TableAlias_1_"
],
"column_names": [
"TableAlias_0_.shard_column AS shard_column",
"TableAlias_0_.not_shard_column AS not_shard_column",
"TableAlias_1_.shard_column AS shard_column0",
"TableAlias_1_.not_shard_column AS not_shard_column0"
],
"expressions": [
"inner join TableAlias_0_, TableAlias_1_ on (TableAlias_0_.shard_column = TableAlias_1_.not_shard_column ) "
],
"options": {
"create_explain": "true",
"show_filters": "true",
"ttl": "-1"
}
}",
"LAST_USE_TABLES": "",
"OPTIONS": "{create_explain,true} {show_filters,true} {ttl,-1}",
"RESULT_DISTRIBUTION": "NA / ;",
"RESULT_ROWS": "0",
"RUN_TIME": "0",
"TABLE_DEFINITIONS": "CREATE TABLE "example"."explain_table"\r
(\r
"shard_column" INTEGER (shard_key),\r
"not_shard_column" INTEGER\r
)\r
TIER STRATEGY (\r
( ( VRAM 1, RAM 5, DISK0 5, PERSIST 5 ) )\r
);
CREATE TABLE "example"."explain_table"\r
(\r
"shard_column" INTEGER (shard_key),\r
"not_shard_column" INTEGER\r
)\r
TIER STRATEGY (\r
( ( VRAM 1, RAM 5, DISK0 5, PERSIST 5 ) )\r
);"
},
{
"ADDITIONAL_INFO": "",
"COLUMNS": "shard_column AS shard_column, not_shard_column AS not_shard_column, shard_column0 AS shard_column0, not_shard_column0 AS not_shard_column0",
"DEPENDENCIES": "0",
"ENDPOINT": "/get/records/bycolumn",
"EXPRESSIONS": "",
"ID": "1",
"INFO": "",
"JSON_REQUEST": "{
"table_name": "sys_sql_temp.Join_1_01234567_89ab_cdef_0123_456789abcdef",
"column_names": [
"shard_column AS shard_column",
"not_shard_column AS not_shard_column",
"shard_column0 AS shard_column0",
"not_shard_column0 AS not_shard_column0"
],
"offset": 0,
"limit": -9999,
"encoding": "binary",
"options": {}
}",
"LAST_USE_TABLES": "Join_1_01234567_89ab_cdef_0123_456789abcdef",
"OPTIONS": "",
"RESULT_DISTRIBUTION": "",
"RESULT_ROWS": "0",
"RUN_TIME": "0",
"TABLE_DEFINITIONS": ""
}
]
```
]