PostgreSQL 15.3关联查询分区裁剪未触发的原因排查求助

我有两张按day列做范围分区的表：events（约10万条数据）和weather（约20亿条数据），两表的day字段含义不同。执行关联查询时，PostgreSQL查询计划未对weather表触发分区裁剪，而是扫描所有分区；但相同逻辑在Oracle 19c中可正常触发分区裁剪。以下是建表语句、查询语句及执行计划，分析未触发分区裁剪的原因如下：

建表语句 #

CREATE TABLE IF NOT EXISTS events
(
   id  bigint NOT NULL,
   day date NOT NULL,
   CONSTRAINT events_pkey PRIMARY KEY (day, id)
       USING INDEX TABLESPACE tabpart,
) PARTITION BY RANGE (day)

TABLESPACE tabpart;

CREATE TABLE events_2024 PARTITION OF events    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') TABLESPACE tabpart;

CREATE TABLE IF NOT EXISTS weather
(
   id  bigint NOT NULL,
   day date NOT NULL,
   tmax numeric(3,1) NOT NULL,
   CONSTRAINT weather_pkey PRIMARY KEY (day, id)
       USING INDEX TABLESPACE tabpart,
) PARTITION BY RANGE (day)

TABLESPACE tabpart;

CREATE TABLE weather_1991 PARTITION OF weather    FOR VALUES FROM ('1991-01-01') TO ('1992-01-01') TABLESPACE tabpart;
CREATE TABLE weather_1992 PARTITION OF weather    FOR VALUES FROM ('1992-01-01') TO ('1993-01-01') TABLESPACE tabpart;
...（其余weather分区语句省略）
CREATE TABLE weather_2024 PARTITION OF weather    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') TABLESPACE tabpart;

查询语句 #

select avg(value) 
from   events  e
join   weather w on (e.id = w.id)
where  w.day between e.day - 10 and e.day + 21;  

执行计划 #

HashAggregate  (cost=90136968.42..90138196.60 rows=98254 width=40) (actual time=482709.895..482747.827 rows=66552 loops=1)
   Output: w.idgrid, avg(w.temperature_max)
   Group Key: w.idgrid
   Batches: 1  Memory Usage: 31761kB
   Buffers: shared hit=35478237
   ->  Hash Join  (cost=2190.20..89507469.47 rows=125899790 width=14) (actual time=474894.521..482209.735 rows=1833282 loops=1)
         Output: w.idgrid, w.temperature_max
         Hash Cond: (w.idgrid = c.idgrid)
         Join Filter: ((w.day >= (c.day - 10)) AND (w.day <= (c.day + 21)))
         Rows Removed by Join Filter: 811759233
         Buffers: shared hit=35478237
               Buffers: shared hit=35477877
               ->  Seq Scan on weather_1979 w_1  (cost=0.00..0.00 rows=1 width=24) (actual time=0.009..0.010 rows=0 loops=1)
                     Output: w_1.idgrid, w_1.temperature_max, w_1.day
               ...（其余weather分区扫描语句省略）
         ->  Hash  (cost=1358.28..1358.28 rows=66553 width=12) (actual time=19.735..19.738 rows=66552 loops=1)
               Output: c.day, c.idgrid
               Buckets: 131072  Batches: 1  Memory Usage: 4144kB
               Buffers: shared hit=360
               ->  Append  (cost=0.00..1358.28 rows=66553 width=12) (actual time=0.033..10.108 rows=66552 loops=1)
                     Buffers: shared hit=360
                     ->  Seq Scan on events_2023 c_1  (cost=0.00..0.00 rows=1 width=12) (actual time=0.013..0.013 rows=0 loops=1)
                           Output: c_1.day, c_1.idgrid
                     ->  Seq Scan on events_2024 c_2  (cost=0.00..1025.52 rows=66552 width=12) (actual time=0.018..5.889 rows=66552 loops=1)
                           Output: c_2.day, c_2.idgrid
                           Buffers: shared hit=360
 Query Identifier: 8183848394416207525
 Planning:
   Buffers: shared hit=451 read=36
   I/O Timings: shared/local read=11.284
 Planning Time: 21.057 ms
 JIT:
   Functions: 114
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 11.569 ms, Inlining 10.237 ms, Optimization 204.663 ms, Emission 129.446 ms, Total 355.914 ms
 Execution Time: 482763.833 ms

核心原因分析 #

PostgreSQL的分区裁剪逻辑无法在关联查询的连接过滤条件中，基于另一张表的行级列值动态推导分区范围，而Oracle 19c支持这种动态分区裁剪能力。

具体到本次查询：

• 过滤条件w.day between e.day -10 and e.day +21中，e.day是来自events表的行级变量，并非常量或可提前计算的固定表达式。
• PostgreSQL查询优化器在生成执行计划阶段，无法预判e.day的具体取值范围，也就无法确定weather表需要扫描的分区集合，因此只能选择扫描所有分区，之后再通过Join Filter过滤不符合条件的行。
• 执行计划中的Hash Join也佐证了这一点：优化器先将小表events加载到哈希表，再全量扫描weather的所有分区，逐行匹配连接与过滤条件，最终导致8亿多行被过滤，执行效率极低。

优化方向建议 #

• 改用嵌套循环+索引扫描：由于weather的主键已包含(day, id)，可以强制使用嵌套循环连接。这样每读取一条events数据，就能用e.id和e.day的范围去weather的索引中精准定位对应分区和数据，间接实现分区裁剪。示例写法：

  select avg(w.tmax) 
  from   events e
  join   weather w on (e.id = w.id)
  where  w.day between e.day - 10 and e.day + 21
  order by 1 limit 1; -- 通过排序+limit引导优化器选择嵌套循环，或临时关闭哈希连接参数
  

• 预计算日期范围缩小扫描范围：如果events的day集中在某个区间，先计算其最小/最大day，给weather添加静态范围过滤，减少扫描的分区数量：

  with event_days as (
      select min(day) as min_day, max(day) as max_day from events
  )
  select avg(w.tmax)
  from events e
  join weather w on e.id = w.id
  cross join event_days ed
  where w.day between ed.min_day -10 and ed.max_day +21
    and w.day between e.day -10 and e.day +21;
  

内容的提问来源于stack exchange，提问作者Tony Zucchini