EXPLAIN ANALYZE¶
概要¶
EXPLAIN ANALYZE [VERBOSE] [(format <TEXT|JSON>)] statement
說明¶
執行語句並顯示語句的分散式執行計畫，以及每個操作的成本。
VERBOSE 選項將提供更詳細的資訊和底層統計資訊；理解這些可能需要了解 Presto 內部結構和實作細節。使用者可以使用 format 選項設定輸出的格式。預設輸出格式為 TEXT。
注意
統計資料可能不完全準確，特別是對於快速完成的查詢。
範例¶
在下面的範例中，您可以看到每個階段所花費的 CPU 時間，以及階段中每個計畫節點的相對成本。請注意，計畫節點的相對成本基於實際時間，這可能與 CPU 時間相關，也可能不相關。對於每個計畫節點，您可以看到一些額外的統計資訊 (例如：每個節點實例的平均輸入、相關計畫節點的平均雜湊衝突次數)。當想要偵測查詢的資料異常 (偏斜、異常雜湊衝突) 時，此類統計資訊非常有用。
presto:sf1> EXPLAIN ANALYZE SELECT count(*), clerk FROM orders WHERE orderdate > date '1995-01-01' GROUP BY clerk;
Query Plan
-----------------------------------------------------------------------------------------------
Fragment 1 [HASH]
Cost: CPU 88.57ms, Input: 4000 rows (148.44kB), Output: 1000 rows (28.32kB)
Output layout: [count, clerk]
Output partitioning: SINGLE []
- Project[] => [count:bigint, clerk:varchar(15)]
Cost: 26.24%, Input: 1000 rows (37.11kB), Output: 1000 rows (28.32kB), Filtered: 0.00%
Input avg.: 62.50 lines, Input std.dev.: 14.77%
- Aggregate(FINAL)[clerk][$hashvalue] => [clerk:varchar(15), $hashvalue:bigint, count:bigint]
Cost: 16.83%, Output: 1000 rows (37.11kB)
Input avg.: 250.00 lines, Input std.dev.: 14.77%
count := "count"("count_8")
- LocalExchange[HASH][$hashvalue] ("clerk") => clerk:varchar(15), count_8:bigint, $hashvalue:bigint
Cost: 47.28%, Output: 4000 rows (148.44kB)
Input avg.: 4000.00 lines, Input std.dev.: 0.00%
- RemoteSource[2] => [clerk:varchar(15), count_8:bigint, $hashvalue_9:bigint]
Cost: 9.65%, Output: 4000 rows (148.44kB)
Input avg.: 4000.00 lines, Input std.dev.: 0.00%
Fragment 2 [tpch:orders:1500000]
Cost: CPU 14.00s, Input: 818058 rows (22.62MB), Output: 4000 rows (148.44kB)
Output layout: [clerk, count_8, $hashvalue_10]
Output partitioning: HASH [clerk][$hashvalue_10]
- Aggregate(PARTIAL)[clerk][$hashvalue_10] => [clerk:varchar(15), $hashvalue_10:bigint, count_8:bigint]
Cost: 4.47%, Output: 4000 rows (148.44kB)
Input avg.: 204514.50 lines, Input std.dev.: 0.05%
Collisions avg.: 5701.28 (17569.93% est.), Collisions std.dev.: 1.12%
count_8 := "count"(*)
- ScanFilterProject[table = tpch:tpch:orders:sf1.0, originalConstraint = ("orderdate" > "$literal$date"(BIGINT '9131')), filterPredicate = ("orderdate" > "$literal$date"(BIGINT '9131'))] => [cler
Cost: 95.53%, Input: 1500000 rows (0B), Output: 818058 rows (22.62MB), Filtered: 45.46%
Input avg.: 375000.00 lines, Input std.dev.: 0.00%
$hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("clerk"), 0))
orderdate := tpch:orderdate
clerk := tpch:clerk
當使用 VERBOSE 選項時，某些運算子可能會報告額外資訊。例如，視窗函式運算子將輸出以下內容
EXPLAIN ANALYZE VERBOSE SELECT count(clerk) OVER() FROM orders WHERE orderdate > date '1995-01-01';
Query Plan
-----------------------------------------------------------------------------------------------
...
- Window[] => [clerk:varchar(15), count:bigint]
Cost: {rows: ?, bytes: ?}
CPU fraction: 75.93%, Output: 8130 rows (230.24kB)
Input avg.: 8130.00 lines, Input std.dev.: 0.00%
Active Drivers: [ 1 / 1 ]
Index size: std.dev.: 0.00 bytes , 0.00 rows
Index count per driver: std.dev.: 0.00
Rows per driver: std.dev.: 0.00
Size of partition: std.dev.: 0.00
count := count("clerk")
...