HashAggregate的性能缓慢 [英] HashAggregate got slow perfomance
本文介绍了HashAggregate的性能缓慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个喜欢的表：
我的索引是id_station_date_hour
我的查询：
选择id_station，area_type，
总和（goinside = 1并且（zone1 + zone2 + zone3 + cashiertime + special）的情况）> 0和日期> ='2017-10-01'和日期< ='2017 -10-31'and hour> = 9 and hour< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end）as ex_z1z2z3z4z5_time，
sum（goinside = 1和（zone1 + zone2 + zone3 + cashiertime + special）> 0和日期> ='2017-10-01'和日期< ='2017-10-31'和小时> = 9并且小时< 22然后1其他0结束）作为ex_z1z2z3z4z5_num，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> 0和date> ='2017-08-31'和date< ='2017-09 -30'，小时> = 9，小时< 22，然后zone1 + zone2 + zo ne3 + cashiertime + special else 0 end）as ex_z1z2z3z4z5_time_prev，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）的情况） 0和日期> ='2017-08-31'和日期< ='2017-09-30'和小时> = 9和小时< 22然后1个其他0结束）作为ex_z1z2z3z4z5_num_prev，
总和（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 180并且（zone1 + zone2 + zone3 + zone3 + cashiertime + special）< ; 360和日期> ='2017-10-01'并且日期< ='2017-10-31'和小时> = 9并且小时< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end ）作为ex_z1z2z3z4z5_num_3to6，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 180和（zone1 + zone2 + zone3 + cashiertime + special）< 360 and date> ='2017-08-31'并且日期< ='2017-09-30'并且小时> = 9并且小时< 22然后1否则0结束）如ex_z1z2z3z4z5_num_3to6_prev，
sum（goinside = 1，并且（zone1 + zone2 + zone3 + cashiertime + special）≥360，并且（zone1 + zone2 + zone3 + cashiertime + special）≤600，日期> ='2017-10-01'，日期< =' 2017-10-31'并且小时> = 9并且小时< 22然后是zone1 + zone2 + zone3 + cashiertime + special else 0 end）作为ex_z1z2z3z4z5_num_6to10，
sum（goinside = 1和（zone1 + zone2的情况） + zone3 + cashiertime + special）> = 360和（zone1 + zone2 + zone3 + cashiertime + special）< 600和日期> ='2017-08-31'和日期< ='2017-09-30'和小时> = 9和小时< 22然后1个其他0结束）作为ex_z1z2z3z4z5_num_6to10_prev，
sum（goinside = 1和（zone1 + zone2 + zone3 + cashiertime + special）的情况<180和日期> ='2017-10-01'和日期< ='2017-10-31'and hour> = 9 and hour< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end）as ex_z1z2z3z4z5_num_less3，
sum（goinside = 1 and （zone1 + zone2 + zone3 + cashiertime + special）< 180并且日期> ='2017-08-31'和date< ='2017-09-30'and hour> = 9 and hour< 22然后1 else 0 end）as ex_z1z2z3z4z5_num_less3_prev，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 60并且（zone1 + zone2 + zone3 + zone3 + cashiertime + special）<180的情况和日期> ='2017-10-01'和日期< ='2017-10-31'和小时> = 9并且小时< 22然后是zone1 + zone2 + zone3 + cashiertime + special else 0 end） ex_z1z2z3z4z5_num_1to3，
总和（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 60和（zone1 + zone2 + zone3 + cashiertime + special）< 180 and date> ; ='2017-08-31'和日期< ='2017-09-30'和小时> = 9和小时< 22然后1个其他0结束）如ex_z1z2z3z4z5_num_1to3_prev，
总和（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 600并且（zone1 + zone2 + zone3 + zone3 + cashiertime + special）的情况; 900和日期> ='2017-10-01'和日期< ='2017-10-31'和小时> = 9并且小时< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end ）作为ex_z1z2z3z4z5_num_10to15，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 600和（zone1 + zone2 + zone3 + cashiertime + special）<900 and date> ='2017-08-31'并且日期< ='2017-09-30'并且小时> = 9并且小时<22然后1否则0结束）如ex_z1z2z3z4z5_num_10to15_prev，
sum（goinside = 1和（zone1 + zone2 + zone3 + cashiertime + special）< 60和日期> ='2017-10-01'和日期< ='2017-10-31'和小时> = 9和小时< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end）作为ex_z1z2z3z4z5_num_less1，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）的情况< 60和日期& gt; ='2017-08-31'和日期< ='2017-09-30'和小时> = 9和小时< 22然后1个其他0结束）作为ex_z1z2z3z4z5_num_less1_prev，
sum（goinside = 1且（zone1 + zone2 + zone3 + cashiertime + special）> = 900且日期> ='2017-10-01'的情况date< ='2017-10-31'and hour> = 9 and hour< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end）as ex_z1z2z3z4z5_num_over15，
sum（goinside = 1的情况）并且（zone1 + zone2 + zone3 + cashiertime + special）> = 900，日期> ='2017-08-31'，日期< ='2017-09-30'和小时> = 9，小时< 22然后1个其他0结束）作为ex_z1z2z3z4z5_num_over15_prev，
sum（goinside = 1和（zone1 + zone2 + zone3 + cashiertime + special）> = 600且日期> ='2017-10-01'的情况date< ='2017-10-31'and hour> = 9 and hour< 22然后zone1 + zone2 + zone3 + cashiertime + special else 0 end）as ex_z1z2z3z4z5_num_over10，
sum（goinside = 1的情况）并且（zone1 + zone2 + zone3 + cashiertime + special）> = 600，日期> ='2017-08-31'，日期< ='2017-09-30'和小时> = 9，小时< 22然后1其他0结束） s ex_z1z2z3z4z5_num_over10_prev，
sum（goinside = 1且type = 1且date> ='2017-10-01'且date< ='2017-10-31'and hour> = 9时< 22然后1 else 0 end）作为man，
sum（goinside = 1且type = 2且date> ='2017-10-01'和date< ='2017-10-31'的情况小时> = 9，小时< 22然后1否则0结束）作为女性，
sum（goinside = 1且type = 3且date> ='2017-10-01'并且date< ='2017-10-31'并且小时> = 9并且小时<22然后1否则0结尾）作为对，
总和（当goinside = 1且类型= 4且日期> ='2017时-10-01''和日期< ='2017-10-31'和小时> = 9并且小时< 22然后1其他0结束）作为男孩，
sum（goinside = 1并键入= 5并且日期> ='2017-10-01'并且日期< ='2017-10-31'和小时> = 9并且小时< 22然后1其他0结束）作为女孩，
sum（当goinside = 1且类型= 6且date> ='2017-10-01'且date< ='2017-10-31'且hour> = 9且hour< 22则1否则为0 end）作为young_couple，
sum（goinside = 1且类型= 7并且date> ='2017-10-01'和date< ='2017-10-31'and hour> = 9的情况并且小时< 22然后1 els e 0 end）作为old_man，
sum（goinside = 1且类型= 8且date> ='2017-10-01'和date< ='2017-10-31'and hour> = 9并且小时< 22然后1 else 0 end）作为old_woman，
sum（goinside = 1且类型= 9且date> ='2017-10-01'和date< ='2017-10-31'的情况）小时> = 9，小时< 22，然后1否则0结束）作为old_couple，
总和（goinside = 1且类型= 10且日期> ='2017-10-01'和date< ='2017-10-31'并且小时> = 9并且小时<22然后1否则0结尾）作为家庭，
总和（goinside = 1且类型= 10且日期> ='2017 -10-01'和日期< ='2017-10-31'和小时> = 9并且小时< 22然后family_total否则0结束）作为family_total，
sum（goinside = 1并键入= 11并且日期> ='2017-10-01'并且日期< ='2017-10-31'和小时> = 9并且小时< 22然后1其他0结束）作为组，
sum（当goinside = 1且类型= 11且date> ='2017-10-01'且date< ='2017-10-31'且hour> = 9且hour< 22时为group_man else 0 end）as group_man，
sum（goinside = 1且type = 11并且date> ='2017-10-01'和date< ='2017-10-31'的情况d小时> = 9，小时< 22然后group_woman else 0 end）作为group_woman，
sum（goinside = 1且类型= 12且date> ='2017-10-01'和date< ='2017-10-31'的情况，小时> = 9且小时< 22然后1否则0结束）为空，
总和（goinside = 1且日期> ='2017-10-01'并且日期< ='2017- 10-31'并且小时> = 9并且小时< 22然后1其他0结束）作为total_entries，
sum（goinside = 1且type = 1并且日期> ='2017-08-31 'and date< ='2017-09-30'and hour> = 9 and hour< 22然后1 else 0 end）作为man_prev，
sum（goinside = 1且type = 2和date的情况> ='2017-08-31'和日期< ='2017-09-30'和小时> = 9和小时< 22然后1其他0结束）作为woman_prev，
sum（case when goinside = 1且类型= 3且date> ='2017-08-31'和date< ='2017-09-30'and hour> = 9且hour< 22然后1 else 0 end）as couple_prev ，
sum（goinside = 1且类型= 4且date> ='2017-08-31'和date< ='2017-09-30'and hour> = 9且小时< 22 then 1 else 0 end）as boy_prev，
sum（goinside = 1并且type = 5并且date> ='2017-08-31'and date< ='2017-09-30'和小时> = 9并且小时< 22然后1其他0结束）作为girl_prev，
sum（goinside = 1且类型= 6且日期> ='2017-08-31'并且date< ='2017-09-30'并且小时> = 9并且小时<22然后1否则0结尾）作为young_couple_prev，
sum（goinside = 1且类型= 7且日期> ='2017 -08-31'并且日期< ='2017-09-30'和小时> = 9并且小时< 22然后1否则0结尾）作为old_man_prev，
sum（goinside = 1并键入= 8并且日期> ='2017-08-31'和日期< ='2017-09-30'并且小时> = 9并且小时< 22然后1其他0结束）作为old_woman_prev，
sum（当goinside = 1且类型= 9且date> ='2017-08-31'且date< ='2017-09-30'且hour> = 9且hour< 22然后1 else 0的情况end）as old_couple_prev，
sum（goinside = 1且type = 10且date> ='2017-08-31'和date< ='2017-09-30'a的情况nd hour> = 9，hour< 22 then 1 else 0 end）as family_prev，
sum（goinside = 1且type = 10且date> ='2017-08-31'和date< ='2017-09-30'的情况小时> = 9，小时< 22，然后family_total否则0结束）作为family_total_prev，
sum（goinside = 1且type = 11且日期> ='2017-08-31'和date< ='2017-09-30'并且小时> = 9并且小时<22然后1其他0结束）作为grou_prev，
sum（goinside = 1且类型= 11且日期> ='2017 -08-31'并且日期< ='2017-09-30'和小时> = 9并且小时< 22然后是group_man else 0 end）作为group_man_prev，
sum（goinside = 1并键入= 11并且日期> ='2017-08-31'并且日期< ='2017-09-30'和小时> = 9并且小时< 22然后group_woman否则0结束）作为group_woman_prev，
sum（当goinside = 1且类型= 12且date> ='2017-08-31'且date< ='2017-09-30'且hour> = 9且hour< 22则1否则0 end）as empty_prev，
sum（goinside = 1且日期> ='2017-08-31'和dat的情况e< = 2017-09-30和小时> = 9和小时< 22然后1个其他0结束）作为来自data_1034_detail的total_entries_prev
其中id_station IN（2399,2397）AND（（date> ='2017-10-01'and date< ='2017-10-31'AND小时> = 9和小时< 22）或（日期> ='2017-08-31'和日期< ='2017-09-30'AND小时> = 9和小时< 22））组通过id_station，area_type
实际上，所有字段选择的区域都不同，因此它将使查询速度缓慢地工作如果我添加更多条件并选择字段。在这种情况下，如何设置适当的索引？
说明：
'HashAggregate（成本= 304337.96..304338.36行= 40宽度= 428）（实际时间= 7233.402..7233.449行= 37循环= 1）'
'组密钥：id_station，area_type'
'->对data_1034_detail的Seq扫描（成本= 0.00..17573.72行= 218904宽度= 64）（实际时间= 0.035..394.334行= 282899循环= 1）'
'过滤器：（（id_station = ANY（'{2399 ，2397}':: bigint []））和（小时> = 9）和（小时< 22）AND（（（（日期> ='2017-10-01':: date））AND（日期< ='2017-10-31':: date））或（（date> ='2017-08-31':: date）AND（date< ='2017-09-30':: date））） ）'
'计划时间：0.735毫秒'
'执行时间：7234.169毫秒'
是否有机会使查询在1秒钟内执行？
解决方案
这是使查询变慢的算法。 / p>
我不确定，但是如果您将 FILTER 子句与求和一起使用，也许可以赢取一些钱。
而不是
sum（以goinside = 1
为例） AND（区域1 +区域2 +区域3 +收银时间+特殊时间）> 0
AND日期> ='2017-10-01'
AND日期< ='2017-10-31'
AND hour> = 9
AND hour< 22
THEN区域1 +区域2 +区域3 +收银时间+特殊
ELSE 0
END）AS ex_z1z2z3z4z5_time
您可以这样写：
sum（zone1 + zone2 + zone3 + Cashiertime + Special）
过滤器（goinside = 1
AND（zone1 + zone2 + zone3 + Cashiertime + Special））> 0
AND日期> ='2017-10-01'
AND日期< ; ='2017-10-31'
AND hour> = 9
AND hour< 22）AS ex_z1z2z3z4z5_time
您仍然必须进行所有计算，但总和将超过少数项目。
也许 PG-Strom 可以在这里为您提供帮助–此扩展委派可以并行化到GPU的操作。
I have a table likse this:
my index is id_station_date_hour
My query:
select id_station,area_type,
sum(case when goinside = 1 and(zone1+zone2+zone3+cashiertime+special) > 0 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_time,
sum(case when goinside = 1 and(zone1+zone2+zone3+cashiertime+special) > 0 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num,
sum(case when goinside = 1 and(zone1+zone2+zone3+cashiertime+special) > 0 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_time_prev,
sum(case when goinside = 1 and(zone1+zone2+zone3+cashiertime+special) > 0 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 180 and (zone1+zone2+zone3+cashiertime+special) < 360 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_3to6,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 180 and (zone1+zone2+zone3+cashiertime+special) < 360 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_3to6_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 360 and (zone1+zone2+zone3+cashiertime+special) < 600 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_6to10,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 360 and (zone1+zone2+zone3+cashiertime+special) < 600 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_6to10_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) < 180 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_less3,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) < 180 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_less3_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 60 and (zone1+zone2+zone3+cashiertime+special) < 180 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_1to3,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 60 and (zone1+zone2+zone3+cashiertime+special) < 180 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_1to3_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 600 and (zone1+zone2+zone3+cashiertime+special) < 900 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_10to15,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 600 and (zone1+zone2+zone3+cashiertime+special) < 900 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_10to15_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) < 60 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_less1,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) < 60 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_less1_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 900 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_over15,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 900 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_over15_prev,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 600 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then zone1+zone2+zone3+cashiertime+special else 0 end) as ex_z1z2z3z4z5_num_over10,
sum(case when goinside = 1 and (zone1+zone2+zone3+cashiertime+special) >= 600 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as ex_z1z2z3z4z5_num_over10_prev,
sum(case when goinside = 1 and type = 1 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as man,
sum(case when goinside = 1 and type = 2 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as woman,
sum(case when goinside = 1 and type = 3 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as couple,
sum(case when goinside = 1 and type = 4 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as boy,
sum(case when goinside = 1 and type = 5 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as girl,
sum(case when goinside = 1 and type = 6 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as young_couple,
sum(case when goinside = 1 and type = 7 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as old_man,
sum(case when goinside = 1 and type = 8 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as old_woman,
sum(case when goinside = 1 and type = 9 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as old_couple,
sum(case when goinside = 1 and type = 10 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as family,
sum(case when goinside = 1 and type = 10 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then family_total else 0 end) as family_total,
sum(case when goinside = 1 and type = 11 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as group,
sum(case when goinside = 1 and type = 11 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then group_man else 0 end) as group_man,
sum(case when goinside = 1 and type = 11 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then group_woman else 0 end) as group_woman,
sum(case when goinside = 1 and type = 12 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as empty,
sum(case when goinside = 1 and date >= '2017-10-01' and date <= '2017-10-31' and hour >= 9 and hour < 22 then 1 else 0 end) as total_entries,
sum(case when goinside = 1 and type = 1 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as man_prev,
sum(case when goinside = 1 and type = 2 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as woman_prev,
sum(case when goinside = 1 and type = 3 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as couple_prev,
sum(case when goinside = 1 and type = 4 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as boy_prev,
sum(case when goinside = 1 and type = 5 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as girl_prev,
sum(case when goinside = 1 and type = 6 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as young_couple_prev,
sum(case when goinside = 1 and type = 7 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as old_man_prev,
sum(case when goinside = 1 and type = 8 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as old_woman_prev,
sum(case when goinside = 1 and type = 9 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as old_couple_prev,
sum(case when goinside = 1 and type = 10 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as family_prev,
sum(case when goinside = 1 and type = 10 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then family_total else 0 end) as family_total_prev,
sum(case when goinside = 1 and type = 11 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as grou_prev,
sum(case when goinside = 1 and type = 11 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then group_man else 0 end) as group_man_prev,
sum(case when goinside = 1 and type = 11 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then group_woman else 0 end) as group_woman_prev,
sum(case when goinside = 1 and type = 12 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as empty_prev,
sum(case when goinside = 1 and date >= '2017-08-31' and date <= '2017-09-30' and hour >= 9 and hour < 22 then 1 else 0 end) as total_entries_prev
from data_1034_detail where id_station IN (2399,2397) AND ((date >= '2017-10-01' and date <= '2017-10-31' AND hour >= 9 and hour < 22) OR (date >= '2017-08-31' and date <= '2017-09-30' AND hour >= 9 and hour < 22)) group by id_station, area_type
actually all fields selected area different condition, so it will make the query speed working slowly if I add more condition and selected fields. How can I set up proper index in this case?
Explain:
'HashAggregate (cost=304337.96..304338.36 rows=40 width=428) (actual time=7233.402..7233.449 rows=37 loops=1)'
' Group Key: id_station, area_type'
' -> Seq Scan on data_1034_detail (cost=0.00..17573.72 rows=218904 width=64) (actual time=0.035..394.334 rows=282899 loops=1)'
' Filter: ((id_station = ANY ('{2399,2397}'::bigint[])) AND (hour >= 9) AND (hour < 22) AND (((date >= '2017-10-01'::date) AND (date <= '2017-10-31'::date)) OR ((date >= '2017-08-31'::date) AND (date <= '2017-09-30'::date))))'
'Planning time: 0.735 ms'
'Execution time: 7234.169 ms'
And any chance to make this query execute in 1s?
解决方案
It is the arithmetic that makes the query slow.
I am not sure, but maybe you can win some if you use a FILTER clause with the summation.
Instead of
sum(CASE WHEN goinside = 1
AND (zone1 + zone2 + zone3 + cashiertime + special) > 0
AND date >= '2017-10-01'
AND date <= '2017-10-31'
AND hour >= 9
AND hour < 22
THEN zone1 + zone2 + zone3 + cashiertime + special
ELSE 0
END) AS ex_z1z2z3z4z5_time
you could write:
sum(zone1 + zone2 + zone3 + cashiertime + special)
FILTER (WHERE goinside = 1
AND (zone1 + zone2 + zone3 + cashiertime + special) > 0
AND date >= '2017-10-01'
AND date <= '2017-10-31'
AND hour >= 9
AND hour < 22) AS ex_z1z2z3z4z5_time
You will still have to do all the calculations, but the sum will be over fewer items.
Perhaps PG-Strom can help you here – this extension delegates operations that can be parallelized to the GPU.
这篇关于HashAggregate的性能缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
