執行的是下面陳述句：
select
case when grouping(t1.group_type_code) = 1 then '999' else t1.group_type_code end as group_type_code, --機構層級
case when grouping(t1.regional_office_code) = 1 then '999' else t1.regional_office_code end as regional_office_code, --分公司
case when grouping(t1.cent_branch_no) = 1 then '999' else t1.cent_branch_no end as cent_branch_no, --中支公司
count(distinct t1.party_id) as cnt_cust --客戶數
from a01_app.tmp_lm3_m_marketing_cust_07 t1
group by rollup(t1.group_type_code),rollup(t1.regional_office_code,t1.cent_branch_no)
limit 111
;
其中表中 a01_app.tmp_lm3_m_marketing_cust_07 大概4000多萬的資料量，在執行該陳述句的時候，執行計劃顯示的是
采用GroupAggregate 聚合方式，資料執行查詢非常慢，
我想采用HashAggregate的聚合方式，請問如何能夠實作？
謝謝！
執行計劃：
"Limit (cost=7004242.25..7031135.83 rows=111 width=1608)"
" -> Append (cost=7004242.25..29796672.15 rows=94073 width=1608)"
" -> Append (cost=7004242.25..22730829.78 rows=78818 width=1608)"
" -> GroupAggregate (cost=7004242.25..7576875.47 rows=22883 width=1608)"
" Group By: t1.group_type_code, t1.cent_branch_no, t1.regional_office_code"
" -> Shared Scan (share slice:id 0:0) (cost=7004242.25..7066897.48 rows=40761624 width=57)"
" -> Materialize (cost=6596626.01..7004242.25 rows=40761624 width=57)"
" -> Gather Motion 156:1 (slice1; segments: 156) (cost=5638727.85..6555864.39 rows=40761624 width=57)"
" Merge Key: t1.group_type_code, t1.cent_branch_no, t1.regional_office_code"
" -> Sort (cost=5638727.85..5740631.91 rows=261293 width=57)"
" Sort Key: t1.group_type_code, t1.cent_branch_no, t1.regional_office_code"
" -> Append-only Columnar Scan on tmp_lm3_m_marketing_cust_07 t1 (cost=0.00..486314.24 rows=261293 width=57)"
" -> GroupAggregate (cost=7004242.25..7577028.01 rows=30510 width=1608)"
" Group By: t1.group_type_code, t1.cent_branch_no"
" -> Shared Scan (share slice:id 0:0) (cost=7004242.25..7066897.48 rows=40761624 width=57)"
" -> GroupAggregate (cost=7004242.25..7576926.31 rows=25425 width=1608)"
" Group By: t1.group_type_code"
" -> Shared Scan (share slice:id 0:0) (cost=7004242.25..7066897.48 rows=40761624 width=57)"
" -> GroupAggregate (cost=5638727.85..7065689.82 rows=15255 width=1608)"
" Group By: t1.group_type_code, t1.regional_office_code"
" -> Gather Motion 156:1 (slice2; segments: 156) (cost=5638727.85..6555864.39 rows=40761624 width=57)"
" Merge Key: t1.group_type_code, t1.regional_office_code, t1.cent_branch_no"
" -> Sort (cost=5638727.85..5740631.91 rows=261293 width=57)"
" Sort Key: t1.group_type_code, t1.regional_office_code, t1.cent_branch_no"
" -> Append-only Columnar Scan on tmp_lm3_m_marketing_cust_07 t1 (cost=0.00..486314.24 rows=261293 width=57)"
"Optimizer status: legacy query optimizer"
uj5u.com熱心網友回復：
將work_mem設定增大。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/115334.html
標籤：PostgreSQL