2023-02-08
postgres查分区表最大月份数据很慢
5M一条查按月分区的最大月份数据的查询，单独查最大月份以及指定月份去查询都很快，但组合成查最大月份的数据就很慢。CreateDay有索引
--单独查最大月份很快
select max(CreateDay) mx_dy from t;
Time: 10.108 ms
--查指定月的数据也很快
select branch,count(*) from t where CreateDay=date'2023-01-01' group by branch order by 2 desc ;
Time: 868.186 ms
--上面两条sql组合在一起就非常慢，1分钟都出不来
select branch,count(*) from t where CreateDay=(select max(CreateDay) from t) group by branch order by 2 desc ; 我来答
添加附件
收藏
分享
问题补充
3条回答
默认
最新
执行计划如下
explain select max(CreateDay) from t;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=33.80..33.81 rows=1 width=8)
InitPlan 1 (returns $0)
-> Limit (cost=33.73..33.80 rows=1 width=8)
-> Append (cost=33.73..22359565.62 rows=310667995 width=8)
-> Index Only Scan Backward using t_p_202305_createday_custnum_idx on t_p_202305 (cost=0.14..80.49 rows=20 width=8)
Index Cond: (createday IS NOT NULL)
-> Index Only Scan Backward using t_p_202304_createday_custnum_idx on t_p_202304 (cost=0.14..80.49 rows=20 width=8)
Index Cond: (createday IS NOT NULL)
.......(省略100多行类似上面行的分区信息)explain select branch,count(*) from t where CreateDay=date'2023-01-01' group by branch order by 2 desc ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Sort (cost=193983.88..193984.38 rows=200 width=21)
Sort Key: (count(*)) DESC
-> Finalize GroupAggregate (cost=193925.56..193976.23 rows=200 width=21)
Group Key: t_p_202301.branch
-> Gather Merge (cost=193925.56..193972.23 rows=400 width=21)
Workers Planned: 2
-> Sort (cost=192925.54..192926.04 rows=200 width=21)
Sort Key: t_p_202301.branch
-> Partial HashAggregate (cost=192915.90..192917.90 rows=200 width=21)
Group Key: t_p_202301.branch
-> Parallel Seq Scan on t_p_202301 (cost=0.00..184513.64 rows=1680451 width=13)
Filter: (createday = '2023-01-01'::date)
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true
explain select branch,count(*) from t where CreateDay=(select max(CreateDay) from t) group by branch order by 2 desc ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
Sort (cost=14995430.85..14995431.35 rows=200 width=21)
Sort Key: (count(*)) DESC
InitPlan 2 (returns $1)
-> Result (cost=33.80..33.81 rows=1 width=8)
InitPlan 1 (returns $0)
-> Limit (cost=33.73..33.80 rows=1 width=8)
-> Append (cost=33.73..22359565.62 rows=310667995 width=8)
-> Index Only Scan Backward using t_p_202305_createday_custnum_idx on t_p_202305 t_p_202305_1 (cost=0.14..80.49 rows=20 width=8)
Index Cond: (createday IS NOT NULL)
-> Index Only Scan Backward using t_p_202304_createday_custnum_idx on t_p_202304 t_p_202304_1 (cost=0.14..80.49 rows=20 width=8)
Index Cond: (createday IS NOT NULL)
.......(省略100多行类似上面行的分区信息)
-> Finalize GroupAggregate (cost=14995338.72..14995389.39 rows=200 width=21)
Group Key: t_p_201601.branch
-> Gather Merge (cost=14995338.72..14995385.39 rows=400 width=21)
Workers Planned: 2
Params Evaluated: $1
-> Sort (cost=14994338.70..14994339.20 rows=200 width=21)
Sort Key: t_p_201601.branch
-> Partial HashAggregate (cost=14994329.05..14994331.05 rows=200 width=21)
Group Key: t_p_201601.branch
-> Parallel Append (cost=0.14..14347104.64 rows=129444883 width=13)
-> Parallel Index Scan using t_p_201601_createday_custnum_idx on t_p_201601 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201602_createday_custnum_idx on t_p_201602 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201603_createday_custnum_idx on t_p_201603 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201604_createday_custnum_idx on t_p_201604 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201605_createday_custnum_idx on t_p_201605 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201606_createday_custnum_idx on t_p_201606 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201607_createday_custnum_idx on t_p_201607 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201608_createday_custnum_idx on t_p_201608 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201609_createday_custnum_idx on t_p_201609 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201610_createday_custnum_idx on t_p_201610 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201611_createday_custnum_idx on t_p_201611 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_201612_createday_custnum_idx on t_p_201612 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_202302_createday_custnum_idx on t_p_202302 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_202303_createday_custnum_idx on t_p_202303 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_202304_createday_custnum_idx on t_p_202304 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Index Scan using t_p_202305_createday_custnum_idx on t_p_202305 (cost=0.14..8.15 rows=1 width=98)
Index Cond: (createday = $1)
-> Parallel Seq Scan on t_p_202202 (cost=0.00..362170.91 rows=1801592 width=13)
Filter: (createday = $1)
-> Parallel Seq Scan on t_p_201812 (cost=0.00..203420.34 rows=1877308 width=13)
Filter: (createday = $1)
.......(省略100多行类似上面行的分区信息)
JIT:
Functions: 472
Options: Inlining true, Optimization true, Expressions true, Deforming true
评论
有用 0试下这个语句，发plan出来
select branch,count(*) from t where CreateDay=(select b.CreateDay from t b order by b.CreateDay desc limit 1) group by branch order by 2 desc ;
或把 select max(CreateDay) from t; 放自定义函数中，
select branch,count(*) from t where CreateDay=f_get_max_CreateDay() group by branch order by 2 desc ;
评论 有用 1回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
墨值悬赏