Presto支持基于统计的查询优化。为了使查询能够利用这些优化,Presto必须具有该查询中表的统计信息。
表统计信息通过连接器提供给查询计划者。当前,唯一支持统计信息的连接器是Hive连接器
。
统计信息通过table layout
显示给查询计划者。table layout
代表表数据的子集,并包含有关该数据的组织属性的信息(例如排序顺序和存储分区)。
一个表可用的table layout
数量以及这些table layout
的详细信息特定于每个连接器。以Hive连接器
为例:
table layout
,代表表中的所有数据table layout
。每组要扫描的分区代表一个table layout
。 Presto会根据查询中的过滤谓词,尝试选择由最少数量的分区组成的table layout
。Hive连接器会自动收集有关INSERT
和CREATE TABLE AS
操作的基本统计信息(numFiles,numRows,rawDataSize,totalSize
)。
Hive连接器还可以收集列级别的统计信息:
image.png
写入时自动进行列级统计信息收集由collect-column-statistics-on-write
会话属性控制。
Hive连接器支持通过ANALYZE
语句收集表和分区统计信息。分析分区表时,可以通过可选的partitions
属性指定要分析的分区,该属性是一个包含分区键值的数组.
ANALYZE hive.sales WITH ( partitions = ARRAY[ ARRAY['partition1_value1', 'partition1_value2'], ARRAY['partition2_value1', 'partition2_value2']]);
该查询将使用键收集2个分区的统计信息。
Presto提供以下统计信息:
对于表:
table layout
中的总行数对于表中的每一列:
可用于特定查询的统计信息集取决于所使用的连接器,并且还可能因表甚至table layout
而异。例如,Hive连接器当前不提供有关数据大小的统计信息。
可以使用SHOW STATS for
命令通过Presto SQL界面显示表统计信息。
SHOW STATS for.png
EXPLAIN [ ( option [, ...] ) ] statement option: FORMAT { TEXT | GRAPHVIZ | JSON } TYPE { LOGICAL | DISTRIBUTED | VALIDATE | IO }
在计划过程中,将基于查询中表的表统计信息来计算与计划的每个节点关联的成本。计算出的成本将作为EXPLAIN
语句输出的一部分进行打印。
成本信息以{rows: XX (XX), cpu: XX, memory: XX, network: XX}
的格式显示在计划树中。rows
是指执行期间每个计划节点输出的预期行数。行数后括号中的值是指每个计划节点输出的数据的预期大小(以字节为单位)。其他参数指示计划节点的执行所使用的CPU,内存和网络的估计数量。这些值不代表任何实际单位,而是用于比较计划节点之间的相对成本的数字,从而使优化器可以选择最佳计划来执行查询
。如果不知道任何值,?
打印出来。
presto:default> EXPLAIN SELECT comment FROM tpch.sf1.nation WHERE nationkey > 3; - Output[comment] => [[comment]] Estimates: {rows: 22 (1.69kB), cpu: 6148.25, memory: 0.00, network: 1734.25} - RemoteExchange[GATHER] => [[comment]] Estimates: {rows: 22 (1.69kB), cpu: 6148.25, memory: 0.00, network: 1734.25} - ScanFilterProject[table = tpch:nation:sf1.0, filterPredicate = ("nationkey" > BIGINT '3')] => [[comment]] Estimates: {rows: 25 (1.94kB), cpu: 2207.00, memory: 0.00, network: 0.00}/{rows: 22 (1.69kB), cpu: 4414.00, memory: 0.00, network: 0.00}/{rows: 22 (1.69kB), cpu: 6148.25, memory: 0.00, network: 0.00} nationkey := tpch:nationkey comment := tpch:comment
通常,每个计划节点仅打印一个成本。但是,当将Scan
运算符与Filter
和/或Project
运算符组合在一起时,将打印出多个成本结构,每个成本结构都对应于组合运算符的单个逻辑部分。例如,将为ScanFilterProject
算子打印三个成本结构,分别与Scan
, Filter
, 与Project
部分相对应。
显示SQL语句的逻辑或分布式执行计划,或验证语句。使用TYPE DISTRIBUTED
选项显示分段计划。每个计划片段均由单个或多个Presto节点执行。片段分离代表Presto节点之间的数据交换。片段类型指定Presto节点如何执行片段以及片段之间的数据分配方式.
round-robin
方式分布.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
估计成本还可以使用EXPLAIN ANALYZE [VERBOSE] SQL
打印,分布式执行计划以及每个操作的成本。
使用VERBOSE
选项时,将提供更详细的信息和低级别的统计信息;要了解这些内容,需要了解Presto内部和实现细节。
可以看到每个阶段花费的CPU时间以及该阶段中每个计划节点的相对成本。然而,计划节点的相对成本基于wall time
,该时间可能会或可能不会与CPU时间相关。对于每个计划节点,您可以看到其他统计信息(如:每个节点实例的平均输入,相关计划节点的平均哈希碰撞数)。想要检测查询的数据异常(数据倾斜,异常哈希碰撞)时,此类统计非常有用。
presto:dm_db> EXPLAIN ANALYZE select a.pass_id ,a.channel_id from( select m.pass_id ,m.channel_id ,coalesce(a.task_gold,0) as task_gold from( select a.pass_id ,a.channel_id from( select a.pass_id ,a.first_channel_idas channel_id ,row_number() over(partition by a.pass_id order by a.first_channel_id desc) as row_num_desc from dw_db.dw_common_mobile_device_user_mapping as a where a.p_product='browser_app' and a.p_project='browser' and a.p_dt='2020-07-13' and a.last_date between '2020-06-13' and '2020-07-13' and coalesce(a.pass_id,0)<>0 ) as a where a.row_num_desc=1 )as m left join( select a.pass_id ,sum(a.task_gold) as task_gold from dw_db.dw_browser_app_xqlm_task_log as a where a.p_dt between '2020-06-13' and '2020-07-13' and a.status=0 group by a.pass_id ) as a on a.pass_id = m.pass_id ) as a where a.task_gold >0; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Fragment 1 [HASH] CPU: 24.60s, Scheduled: 48.18s, Input: 7115227 rows (302.06MB); per task: avg.: 2371742.33 std.dev.: 5189.23, Output: 265662 rows (9.24MB) Output layout: [first_channel_id, pass_id] Output partitioning: SINGLE [] Stage Execution Strategy: UNGROUPED_EXECUTION InnerJoin[("pass_id" = "pass_id_21")][$hashvalue, $hashvalue_65] │ Layout: [first_channel_id:varchar, pass_id:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ CPU: 10.89s (10.17%), Scheduled: 30.23s (0.90%), Output: 265662 rows (9.24MB) │ Left (probe) Input avg.: 9818.46 rows, Input std.dev.: 1.11% │ Right (build) Input avg.: 6401.85 rows, Input std.dev.: 1.08% │ Collisions avg.: 690.42 (100.63% est.), Collisions std.dev.: 141.48% │ Distribution: PARTITIONED ├─ FilterProject[filterPredicate = (("row_number" = BIGINT '1') AND (COALESCE("pass_id", BIGINT '0') <> BIGINT '0'))] │ │ Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue:bigint] │ │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}/{rows: ? (?), cpu: ?, memory: ?, network: ?} │ │ CPU: 33.00ms (0.03%), Scheduled: 37.00ms (0.00%), Output: 471286 rows (17.06MB) │ │ Input avg.: 9818.46 rows, Input std.dev.: 1.11% │ └─ TopNRowNumber[partition by (pass_id), order by (first_channel_id DESC_NULLS_LAST) limit 1][$hashvalue] │ │ Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue:bigint, row_number:bigint] │ │ CPU: 8.43s (7.87%), Scheduled: 12.03s (0.36%), Output: 471286 rows (21.10MB) │ │ Input avg.: 10191.81 rows, Input std.dev.: 1.20% │ │ row_number := row_number() │ └─ LocalExchange[HASH][$hashvalue] ("pass_id") │ │ Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue:bigint] │ │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ │ CPU: 271.00ms (0.25%), Scheduled: 311.00ms (0.01%), Output: 489207 rows (17.70MB) │ │ Input avg.: 10191.81 rows, Input std.dev.: 132.04% │ └─ RemoteSource[2] │ Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue_60:bigint] │ CPU: 28.00ms (0.03%), Scheduled: 35.00ms (0.00%), Output: 489207 rows (17.70MB) │ Input avg.: 10191.81 rows, Input std.dev.: 132.04% └─ FilterProject[filterPredicate = (COALESCE("sum", BIGINT '0') > BIGINT '0')] │ Layout: [pass_id_21:bigint, $hashvalue_65:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}/{rows: ? (?), cpu: ?, memory: ?, network: ?} │ CPU: 381.00ms (0.36%), Scheduled: 384.00ms (0.01%), Output: 307289 rows (5.27MB) │ Input avg.: 6401.85 rows, Input std.dev.: 1.08% │ $hashvalue_65 := combine_hash(bigint '0', COALESCE("$operator$hash_code"("pass_id_21"), 0)) └─ Aggregate(FINAL)[pass_id_21] │ Layout: [pass_id_21:bigint, sum:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ CPU: 2.37s (2.21%), Scheduled: 2.72s (0.08%), Output: 307289 rows (5.27MB) │ Input avg.: 138042.08 rows, Input std.dev.: 1.61% │ Collisions avg.: 8646.84 (1263.76% est.), Collisions std.dev.: 141.95% │ sum := sum("sum_59") └─ LocalExchange[HASH][$hashvalue_62] ("pass_id_21") │ Layout: [pass_id_21:bigint, sum_59:row(bigint, boolean, bigint, boolean), $hashvalue_62:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ CPU: 1.46s (1.37%), Scheduled: 1.55s (0.05%), Output: 6626020 rows (284.36MB) │ Input avg.: 138042.08 rows, Input std.dev.: 82.07% └─ RemoteSource[3] Layout: [pass_id_21:bigint, sum_59:row(bigint, boolean, bigint, boolean), $hashvalue_63:bigint] CPU: 520.00ms (0.49%), Scheduled: 536.00ms (0.02%), Output: 6626020 rows (284.36MB) Input avg.: 138042.08 rows, Input std.dev.: 82.07% Fragment 2 [SOURCE] CPU: 39.81s, Scheduled: 18.59m, Input: 131534564 rows (3.68GB); per task: avg.: 65767282.00 std.dev.: 5417075.00, Output: 489207 rows (17.70MB) Output layout: [first_channel_id, pass_id, $hashvalue_61] Output partitioning: HASH [pass_id][$hashvalue_61] Stage Execution Strategy: UNGROUPED_EXECUTION TopNRowNumber[partition by (pass_id), order by (first_channel_id DESC_NULLS_LAST) limit 1][$hashvalue_61] │ Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue_61:bigint] │ CPU: 2.64s (2.46%), Scheduled: 3.04s (0.09%), Output: 489207 rows (17.70MB) │ Input avg.: 6894.55 rows, Input std.dev.: 14.84% │ row_number := row_number() └─ ScanFilterProject[table = hive:dw_db:dw_common_mobile_device_user_mapping, grouped = false, filterPredicate = (("last_date" BETWEEN CAST('2020-06-13' AS varchar) AND CAST('2020-07-13' AS varchar)) AND (COALES Layout: [first_channel_id:varchar, pass_id:bigint, $hashvalue_61:bigint] Estimates: {rows: 131534564 (3.82GB), cpu: 4.55G, memory: 0B, network: 0B}/{rows: 118381094 (3.43GB), cpu: 9.10G, memory: 0B, network: 0B}/{rows: 118381094 (3.43GB), cpu: 12.54G, memory: 0B, network: 0B} CPU: 37.17s (34.71%), Scheduled: 36.34m (64.89%), Output: 489513 rows (17.71MB) Input avg.: 1852599.49 rows, Input std.dev.: 14.66% $hashvalue_61 := combine_hash(bigint '0', COALESCE("$operator$hash_code"("pass_id"), 0)) first_channel_id := first_channel_id:string:REGULAR pass_id := pass_id:bigint:REGULAR last_date := last_date:string:REGULAR p_product:string:PARTITION_KEY :: [[browser_app]] p_dt:string:PARTITION_KEY :: [[2020-07-13]] p_project:string:PARTITION_KEY :: [[browser]] Input: 131534564 rows (3.68GB), Filtered: 99.63% Fragment 3 [SOURCE] CPU: 42.88s, Scheduled: 10.14m, Input: 140221136 rows (2.48GB); per task: avg.: 46740378.67 std.dev.: 25798179.66, Output: 6626020 rows (284.36MB) Output layout: [pass_id_21, sum_59, $hashvalue_64] Output partitioning: HASH [pass_id_21][$hashvalue_64] Stage Execution Strategy: UNGROUPED_EXECUTION Project[] │ Layout: [pass_id_21:bigint, sum_59:row(bigint, boolean, bigint, boolean), $hashvalue_64:bigint] │ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?} │ CPU: 4.96s (4.63%), Scheduled: 8.06s (0.24%), Output: 6626020 rows (284.36MB) │ Input avg.: 46335.80 rows, Input std.dev.: 65.57% │ $hashvalue_64 := combine_hash(bigint '0', COALESCE("$operator$hash_code"("pass_id_21"), 0)) └─ Aggregate(PARTIAL)[pass_id_21] │ Layout: [pass_id_21:bigint, sum_59:row(bigint, boolean, bigint, boolean)] │ CPU: 14.09s (13.16%), Scheduled: 18.06s (0.54%), Output: 6626020 rows (227.49MB) │ Input avg.: 980565.59 rows, Input std.dev.: 68.56% │ Collisions avg.: 237826.72 (2083.20% est.), Collisions std.dev.: 353.84% │ sum_59 := sum("expr_28") └─ ScanFilterProject[table = hive:dw_db:dw_browser_app_xqlm_task_log, grouped = false, filterPredicate = (("status" = 0) AND (COALESCE("pass_id_21", BIGINT '0') <> BIGINT '0'))] Layout: [pass_id_21:bigint, expr_28:bigint] Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B} CPU: 23.83s (22.26%), Scheduled: 18.38m (32.82%), Output: 140220879 rows (2.35GB) Input avg.: 980567.38 rows, Input std.dev.: 68.56% expr_28 := CAST("task_gold" AS bigint) pass_id_21 := pass_id:bigint:REGULAR task_gold := task_gold:int:REGULAR status := status:int:REGULAR p_dt:string:PARTITION_KEY :: [[2020-06-13], [2020-06-14], [2020-06-15], [2020-06-16], [2020-06-17], [2020-06-18], [2020-06-19], [2020-06-20], [2020-06-21], [2020-06-22], [2020-06-23], [2020-06-24], [2020-06-25], [2020-06-26], [2020-06-27], [2020-06-28], [ Input: 140221136 rows (2.48GB), Filtered: 0.00%
本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。
我来说两句