帮忙分析查询计划,在Gather Mo...
 
通知
清除全部

帮忙分析查询计划,在Gather Motion阶段耗时太长


wanglw
帖子: 8
Topic starter
青梅新星
已加入: 7月 前

Gather Motion 9:1 (slice2; segments: 9) (cost=0.00..477.90 rows=13120 width=24) (actual time=269.606..2617852.363 rows=1794 loops=1)
-> Result (cost=0.00..477.05 rows=1458 width=24) (actual time=267.948..268.144 rows=218 loops=1)
-> HashAggregate (cost=0.00..477.02 rows=1458 width=53) (actual time=267.933..268.013 rows=218 loops=1)
Group Key: staid, bizdate
Extra Text: (seg0) Hash chain length 3.5 avg, 9 max, using 63 of 64 buckets; total 1 expansions.

-> Redistribute Motion 9:9 (slice1; segments: 9) (cost=0.00..476.61 rows=1458 width=53) (actual time=252.059..267.142 rows=378 loops=1)
Hash Key: staid, bizdate
-> Result (cost=0.00..476.36 rows=1458 width=53) (actual time=253.225..253.477 rows=384 loops=1)
-> HashAggregate (cost=0.00..476.36 rows=1458 width=53) (actual time=253.225..253.332 rows=384 loops=1)
Group Key: staid, bizdate
Extra Text: (seg7) Hash chain length 3.1 avg, 7 max, using 125 of 128 buckets; total 2 expansions.

-> Sequence (cost=0.00..475.20 rows=4279 width=67) (actual time=226.128..252.844 rows=443 loops=1)
-> Partition Selector for chargebillsolap_p (dynamic scan id: 1) (cost=10.00..100.00 rows=12 width=4) (never executed)
Partitions selected: 1 (out of 72)
-> Dynamic Seq Scan on chargebillsolap_p (dynamic scan id: 1) (cost=0.00..475.20 rows=4279 width=67) (actual time=226.115..252.650 rows=443 loops=1)
Filter: (((bizdate)::text = '20210414'::text) AND ((iffullyopen)::text = '1'::text) AND ((isteststation)::text = '否'::text) AND (endtime >= (to_date((bizdate)::text, 'YYYYMMDD'::text) + '08:00:00'::time without time zone)) AND (begintime < (to_date((bizdate)::text, 'YYYYMMDD'::text) + '08:10:00'::time without time zone)) AND ((equipmentaccessmode)::text <> '3'::text) AND ((equipmentaccessmode)::text <> '4'::text))
Partitions scanned: Avg 1.0 (out of 72) x 9 workers. Max 1 parts (seg0).
Planning time: 79.471 ms
(slice0) Executor memory: 287K bytes.
(slice1) Executor memory: 1668K bytes avg x 9 workers, 1668K bytes max (seg1).
(slice2) Executor memory: 232K bytes avg x 9 workers, 232K bytes max (seg0).
Memory used: 47185920kB
Optimizer: Pivotal Optimizer (GPORCA)
Execution time: 2617878.313 ms

问题标签
1 Reply
wanglw
帖子: 8
Topic starter
青梅新星
已加入: 7月 前

这个表:chargebillsolap_p 列存压缩表 分布键是ID, 根据bizdate 按月进行分区的

执行的sql: 

select staid || '#' || bizdate as id,
staid as StationID,
count(*) as h8
from chargebillsolap_p where bizdate='20210414' and iffullyopen='1' and isteststation='否'
and endtime>=to_date(bizdate ,'YYYYMMDD') + time '08:00:00' and begintime<to_date(bizdate ,'YYYYMMDD') + time '08:10:00'
and equipmentaccessmode<>'3' AND equipmentaccessmode <>'4'
GROUP BY staid,bizdate

回复

关注微信公众号

Greenplum中文社区

Greenplum官方微信群

扫码加入我们的技术讨论,请备注“网站”