EXPLAIN

Shows generated Intermediate Representation (IR) code, identifying whether it is executed on GPU or CPU. This is primarily used internally by HEAVY.AI to monitor behavior.

1 EXPLAIN <STMT>

For example, when you use the EXPLAIN command on a basic statement, the utility returns 90 lines of IR code that is not meant to be human readable. However, at the top of the listing, a heading indicates whether it is IR for the CPU or IR for the GPU, which can be useful to know in some situations.

EXPLAIN CALCITE

Returns a relational algebra tree describing the high-level plan to execute the statement.

1 EXPLAIN CALCITE <STMT>

The table below lists the relational algebra classes used to describe the execution plan for a SQL statement.

Method	Description
Method	Description
`LogicalAggregate`	Operator that eliminates duplicates and computes totals.
`LogicalCalc`	Expression that computes project expressions and also filters.
`LogicalChi`	Operator that converts a stream to a relation.
`LogicalCorrelate`	Operator that performs nested-loop joins.
`LogicalDelta`	Operator that converts a relation to a stream.
`LogicalExchange`	Expression that imposes a particular distribution on its input without otherwise changing its content.
`LogicalFilter`	Expression that iterates over its input and returns elements for which a condition evaluates to true.
`LogicalIntersect`	Expression that returns the intersection of the rows of its inputs.
`LogicalJoin`	Expression that combines two relational expressions according to some condition.
`LogicalMatch`	Expression that represents a MATCH_RECOGNIZE node.
`LogicalMinus`	Expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator.
`LogicalProject`	Expression that computes a set of ‘select expressions’ from its input relational expression.
`LogicalSort`	Expression that imposes a particular sort order on its input without otherwise changing its content.
`LogicalTableFunctionScan`	Expression that calls a table-valued function.
`LogicalTableModify`	Expression that modifies a table. Similar to TableScan, but represents a request to modify a table instead of read from it.
`LogicalTableScan`	Reads all the rows from a RelOptTable.
`LogicalUnion`	Expression that returns the union of the rows of its inputs, optionally eliminating duplicates.
`LogicalValues`	Expression for which the value is a sequence of zero or more literal row values.
`LogicalWindow`	Expression representing a set of window aggregates. See Window Functions

For example, a SELECT statement is described as a table scan and projection.

1 heavysql> EXPLAIN CALCITE (SELECT * FROM movies);
2 Explanation
3 LogicalProject(movieId=[$0], title=[$1], genres=[$2])
4    LogicalTableScan(TABLE=[[CATALOG, heavyai, MOVIES]])

If you add a sort order, the table projection is folded under a LogicalSort procedure.

1 heavysql> EXPLAIN calcite (SELECT * FROM movies ORDER BY title);
2 Explanation
3 LogicalSort(sort0=[$1], dir0=[ASC])
4    LogicalProject(movieId=[$0], title=[$1], genres=[$2])
5       LogicalTableScan(TABLE=[[CATALOG, omnisci, MOVIES]])

When the SQL statement is simple, the EXPLAIN CALCITE version is actually less “human readable.” EXPLAIN CALCITE is more useful when you work with more complex SQL statements, like the one that follows. This query performs a scan on the BOOK table before scanning the BOOK_ORDER table.

1 heavysql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
2 FROM book b, book_customer bc, book_order bo, shipper s
3 WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
4 AND s.name = 'UPS';
5 Explanation
6 LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
7     LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
8         LogicalJoin(condition=[true], joinType=[INNER])
9             LogicalJoin(condition=[true], joinType=[INNER])
10                 LogicalJoin(condition=[true], joinType=[INNER])
11                     LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
12                     LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
13                 LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
14             LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])

Revising the original SQL command results in a more natural selection order and a more performant query.

1 heavysql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
2 FROM book_order bo, book_customer bc, book b, shipper s
3 WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
4 AND s.name = 'UPS';
5 Explanation
6 LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
7     LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
8         LogicalJoin(condition=[true], joinType=[INNER])
9             LogicalJoin(condition=[true], joinType=[INNER])
10                 LogicalJoin(condition=[true], joinType=[INNER])
11                   LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
12                   LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
13                 LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
14             LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])

EXPLAIN CALCITE DETAILED

Augments the EXPLAIN CALCITE command by adding details about referenced columns in the query plan.

For example, for the following EXPLAIN CALCITE command execution:

1 heavysql> EXPLAIN CALCITE SELECT x, SUM(y) FROM test GROUP BY x;
2 Explanation
3 LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
4   LogicalProject(x=[$0], y=[$2])
5     LogicalTableScan(table=[[testDB, test]])

EXPLAIN CALCITE DETAILED adds more column details as seen below:

1 heavysql> EXPLAIN CALCITE DETAILED SELECT x, SUM(y) FROM test GROUP BY x;
2 Explanation
3 LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])	{[$1->db:testDB,tableName:test,colName:y]}
4   LogicalProject(x=[$0], y=[$2])	{[$2->db:testDB,tableName:test,colName:y], [$0->db:testDB,tableName:test,colName:x]}
5     LogicalTableScan(table=[[testDB, test]])

1	heavysql> EXPLAIN CALCITE (SELECT * FROM movies);
2	Explanation
3	LogicalProject(movieId=[$0], title=[$1], genres=[$2])
4	LogicalTableScan(TABLE=[[CATALOG, heavyai, MOVIES]])

1	heavysql> EXPLAIN calcite (SELECT * FROM movies ORDER BY title);
2	Explanation
3	LogicalSort(sort0=[$1], dir0=[ASC])
4	LogicalProject(movieId=[$0], title=[$1], genres=[$2])
5	LogicalTableScan(TABLE=[[CATALOG, omnisci, MOVIES]])

1	heavysql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
2	FROM book b, book_customer bc, book_order bo, shipper s
3	WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
4	AND s.name = 'UPS';
5	Explanation
6	LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
7	LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
8	LogicalJoin(condition=[true], joinType=[INNER])
9	LogicalJoin(condition=[true], joinType=[INNER])
10	LogicalJoin(condition=[true], joinType=[INNER])
11	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
12	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
13	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
14	LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])

1	heavysql> EXPLAIN calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
2	FROM book_order bo, book_customer bc, book b, shipper s
3	WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
4	AND s.name = 'UPS';
5	Explanation
6	LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
7	LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
8	LogicalJoin(condition=[true], joinType=[INNER])
9	LogicalJoin(condition=[true], joinType=[INNER])
10	LogicalJoin(condition=[true], joinType=[INNER])
11	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_ORDER]])
12	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK_CUSTOMER]])
13	LogicalTableScan(TABLE=[[CATALOG, omnisci, BOOK]])
14	LogicalTableScan(TABLE=[[CATALOG, omnisci, SHIPPER]])

1	heavysql> EXPLAIN CALCITE SELECT x, SUM(y) FROM test GROUP BY x;
2	Explanation
3	LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
4	LogicalProject(x=[$0], y=[$2])
5	LogicalTableScan(table=[[testDB, test]])

1	heavysql> EXPLAIN CALCITE DETAILED SELECT x, SUM(y) FROM test GROUP BY x;
2	Explanation
3	LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)]) {[$1->db:testDB,tableName:test,colName:y]}
4	LogicalProject(x=[$0], y=[$2]) {[$2->db:testDB,tableName:test,colName:y], [$0->db:testDB,tableName:test,colName:x]}
5	LogicalTableScan(table=[[testDB, test]])