postgres查分区表最大月份数据很慢

我来答

2023-02-08

一条查按月分区的最大月份数据的查询，单独查最大月份以及指定月份去查询都很快，但组合成查最大月份的数据就很慢。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条回答

默认

最新

张sir

发下执行计划看下？

有用 1

打赏 0

风云飘

题主

2023-02-09

执行计划发在下面

风云飘

执行计划如下

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

打赏 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

打赏 0

回答交流

Markdown

请输入正文

导入word

提交