EXPLAIN

Synopsis

EXPLAIN [ ( option [, ...] ) ] statement

where option can be one of:

FORMAT { TEXT | GRAPHVIZ | JSON }
TYPE { LOGICAL | DISTRIBUTED | VALIDATE | IO }

Description

Show the logical or distributed execution plan of a statement, or validate the statement. Use TYPE DISTRIBUTED option to display a fragmented plan. Each plan fragment is executed by a single or multiple Presto nodes. Fragment type specifies how the fragment is executed by Presto nodes and how the data is distributed between fragments:

SINGLE

Fragment is executed on a single node.

HASH

Fragment is executed on a fixed number of nodes with the input data distributed using a hash function.

ROUND_ROBIN

Fragment is executed on a fixed number of nodes with the input data distributed in a round-robin fashion.

BROADCAST

Fragment is executed on a fixed number of nodes with the input data broadcast to all nodes.

SOURCE

Fragment is executed on nodes where input splits are accessed.

Examples

Logical plan:

presto:tiny> EXPLAIN SELECT regionkey, count(*) FROM nation GROUP BY 1;
                                                Query Plan
----------------------------------------------------------------------------------------------------------
 - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
         _col1 := count
     - RemoteExchange[GATHER] => regionkey:bigint, count:bigint
         - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
                count := "count"("count_8")
             - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
                 - RemoteExchange[REPARTITION][$hashvalue_9] => regionkey:bigint, count_8:bigint, $hashvalue_9:bigint
                     - Project[] => [regionkey:bigint, count_8:bigint, $hashvalue_10:bigint]
                             $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("regionkey"), 0))
                         - Aggregate(PARTIAL)[regionkey] => [regionkey:bigint, count_8:bigint]
                                 count_8 := "count"(*)
                             - TableScan[tpch:tpch:nation:sf0.1, originalConstraint = true] => [regionkey:bigint]
                                     regionkey := tpch:regionkey

Distributed plan:

presto:tiny> EXPLAIN (TYPE DISTRIBUTED) SELECT regionkey, count(*) FROM nation GROUP BY 1;
                                          Query Plan
----------------------------------------------------------------------------------------------
 Fragment 0 [SINGLE]
     Output layout: [regionkey, count]
     Output partitioning: SINGLE []
     - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
             _col1 := count
         - RemoteSource[1] => [regionkey:bigint, count:bigint]

 Fragment 1 [HASH]
     Output layout: [regionkey, count]
     Output partitioning: SINGLE []
     - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
             count := "count"("count_8")
         - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
             - RemoteSource[2] => [regionkey:bigint, count_8:bigint, $hashvalue_9:bigint]

 Fragment 2 [SOURCE]
     Output layout: [regionkey, count_8, $hashvalue_10]
     Output partitioning: HASH [regionkey][$hashvalue_10]
     - Project[] => [regionkey:bigint, count_8:bigint, $hashvalue_10:bigint]
             $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("regionkey"), 0))
         - Aggregate(PARTIAL)[regionkey] => [regionkey:bigint, count_8:bigint]
                 count_8 := "count"(*)
             - TableScan[tpch:tpch:nation:sf0.1, originalConstraint = true] => [regionkey:bigint]
                     regionkey := tpch:regionkey

Validate:

presto:tiny> EXPLAIN (TYPE VALIDATE) SELECT regionkey, count(*) FROM nation GROUP BY 1;
 result
-------
 true

IO:

presto:hive> EXPLAIN (TYPE IO, FORMAT JSON) INSERT INTO test_nation SELECT * FROM nation WHERE regionkey = 2;
            Query Plan
-----------------------------------
 {
   "inputTableColumnInfos" : [ {
     "table" : {
       "catalog" : "hive",
       "schemaTable" : {
         "schema" : "tpch",
         "table" : "nation"
       }
     },
     "columns" : [ {
       "columnName" : "regionkey",
       "type" : "bigint",
       "domain" : {
         "nullsAllowed" : false,
         "ranges" : [ {
           "low" : {
             "value" : "2",
             "bound" : "EXACTLY"
           },
           "high" : {
             "value" : "2",
             "bound" : "EXACTLY"
           }
         } ]
       }
     } ]
   } ],
   "outputTable" : {
     "catalog" : "hive",
     "schemaTable" : {
       "schema" : "tpch",
       "table" : "test_nation"
     }
   }
 }

DDL Statements

EXPLAIN can also be used with DDL statements such as CREATE TABLE and DROP TABLE. For these statements, the output shows a summary of the operation rather than an execution plan. This is useful for validating DDL syntax and understanding what operation will be performed without actually executing it.

CREATE TABLE

The EXPLAIN CREATE TABLE statement shows a summary of the table creation operation. It validates the syntax and table structure without actually creating the table.

Syntax
EXPLAIN CREATE TABLE [ IF NOT EXISTS ] table_name (
  column_name data_type [NOT NULL] [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
  [, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
CREATE TABLE Examples

Basic table creation:

presto:tiny> EXPLAIN CREATE TABLE new_table (id BIGINT, name VARCHAR);
        Query Plan
--------------------------
 CREATE TABLE new_table

Table creation with IF NOT EXISTS clause:

presto:tiny> EXPLAIN CREATE TABLE IF NOT EXISTS new_table (id BIGINT, name VARCHAR);
              Query Plan
--------------------------------------
 CREATE TABLE IF NOT EXISTS new_table

Table creation with column constraints and properties:

presto:tiny> EXPLAIN CREATE TABLE orders (
          ->   orderkey BIGINT NOT NULL,
          ->   orderstatus VARCHAR,
          ->   totalprice DOUBLE COMMENT 'Price in cents',
          ->   orderdate DATE
          -> )
          -> COMMENT 'Orders table'
          -> WITH (format = 'ORC');
              Query Plan
--------------------------------------
 CREATE TABLE orders

Table creation with LIKE clause:

presto:tiny> EXPLAIN CREATE TABLE new_orders (
          ->   LIKE orders INCLUDING PROPERTIES
          -> );
              Query Plan
--------------------------------------
 CREATE TABLE new_orders

Note

EXPLAIN CREATE TABLE validates the syntax and checks if the table structure is valid, but it does not verify if the table already exists. The actual table creation will fail if the table exists (unless IF NOT EXISTS is specified).

DROP TABLE

The EXPLAIN DROP TABLE statement shows a summary of the table drop operation.

DROP TABLE Examples

Basic table drop:

presto:tiny> EXPLAIN DROP TABLE test_table;
                        Query Plan
--------------------------------------------------------------
 DROP TABLE test_table

Table drop with IF EXISTS clause:

presto:tiny> EXPLAIN DROP TABLE IF EXISTS test_table;
                        Query Plan
--------------------------------------------------------------
 DROP TABLE IF EXISTS test_table

See Also

EXPLAIN ANALYZE