EXPLAIN

Synopsis

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

where option can be one of:

1. FORMAT { TEXT | GRAPHVIZ | JSON }
2. 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 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:

1. presto:tiny> EXPLAIN SELECT regionkey, count(*) FROM nation GROUP BY 1;
2. Query Plan
3. ----------------------------------------------------------------------------------------------------------
4. - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
5. _col1 := count
6. - RemoteExchange[GATHER] => regionkey:bigint, count:bigint
7. - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
8. count := "count"("count_8")
9. - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
10. - RemoteExchange[REPARTITION][$hashvalue_9] => regionkey:bigint, count_8:bigint, $hashvalue_9:bigint
11. - Project[] => [regionkey:bigint, count_8:bigint, $hashvalue_10:bigint]
12. $hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("regionkey"), 0))
13. - Aggregate(PARTIAL)[regionkey] => [regionkey:bigint, count_8:bigint]
14. count_8 := "count"(*)
15. - TableScan[tpch:tpch:nation:sf0.1, originalConstraint = true] => [regionkey:bigint]
16. regionkey := tpch:regionkey

Distributed plan:

1. presto:tiny> EXPLAIN (TYPE DISTRIBUTED) SELECT regionkey, count(*) FROM nation GROUP BY 1;
2. Query Plan
3. ----------------------------------------------------------------------------------------------
4. Fragment 0 [SINGLE]
5. Output layout: [regionkey, count]
6. Output partitioning: SINGLE []
7. - Output[regionkey, _col1] => [regionkey:bigint, count:bigint]
8. _col1 := count
9. - RemoteSource[1] => [regionkey:bigint, count:bigint]

11. Fragment 1 [HASH]
12. Output layout: [regionkey, count]
13. Output partitioning: SINGLE []
14. - Aggregate(FINAL)[regionkey] => [regionkey:bigint, count:bigint]
15. count := "count"("count_8")
16. - LocalExchange[HASH][$hashvalue] ("regionkey") => regionkey:bigint, count_8:bigint, $hashvalue:bigint
17. - RemoteSource[2] => [regionkey:bigint, count_8:bigint, $hashvalue_9:bigint]

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

Validate:

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

IO:

1. presto:hive> EXPLAIN (TYPE IO, FORMAT JSON) INSERT INTO test_nation SELECT * FROM nation WHERE regionkey = 2;
2. Query Plan
3. -----------------------------------
4. {
5. "inputTableColumnInfos" : [ {
6. "table" : {
7. "catalog" : "hive",
8. "schemaTable" : {
9. "schema" : "tpch",
10. "table" : "nation"
11. }
12. },
13. "columns" : [ {
14. "columnName" : "regionkey",
15. "type" : "bigint",
16. "domain" : {
17. "nullsAllowed" : false,
18. "ranges" : [ {
19. "low" : {
20. "value" : "2",
21. "bound" : "EXACTLY"
22. },
23. "high" : {
24. "value" : "2",
25. "bound" : "EXACTLY"
26. }
27. } ]
28. }
29. } ]
30. } ],
31. "outputTable" : {
32. "catalog" : "hive",
33. "schemaTable" : {
34. "schema" : "tpch",
35. "table" : "test_nation"
36. }
37. }
38. }

See Also

EXPLAIN ANALYZE