为何基于generate_series()的查询无法触发PostgreSQL分区裁剪？

我在PostgreSQL 12中使用声明式分区创建了表foo：

create table foo (
    id  integer not null,
    date timestamp not null,
    count integer default 0,
    primary key (id, date)
)
    partition by RANGE (date);

静态时间范围查询时，PostgreSQL能正确执行分区裁剪，只扫描对应分区，效果理想：

select sum(foo.count) as total,
       date_trunc('day', foo.date) as day_date
from foo
where foo.date between '2023-01-01' and '2023-01-02'
group by day_date

但使用generate_series()生成动态时间范围做关联查询时，PostgreSQL会扫描所有分区：

with times as (
    select generate_series('2023-01-01 12:00', '2023-01-02 16:00', '1 hour') as date1,
           generate_series('2023-01-01 16:00', '2023-01-02 20:00', '1 hour') as date2
)
select sum(foo.count) as total,
       times.date1
from times join foo on foo.date between times.date1 and times.date2
group by times.date1;

问题根源在于PostgreSQL 12的查询优化器仅能在规划阶段基于静态常量条件判断是否跳过分区，而CTE中动态生成的时间范围在规划阶段无法确定具体值，因此无法触发分区裁剪。以下是几种可行的解决方法：

• 使用LATERAL子查询实现逐行分区裁剪
  把关联逻辑改成LATERAL JOIN，让优化器能针对times中的每一行时间范围单独做分区判断：

  with times as (
      select generate_series('2023-01-01 12:00', '2023-01-02 16:00', '1 hour') as date1,
             generate_series('2023-01-01 16:00', '2023-01-02 20:00', '1 hour') as date2
  )
  select sum(f.count) as total,
         t.date1
  from times t
  left join lateral (
      select count
      from foo
      where foo.date between t.date1 and t.date2
  ) f on true
  group by t.date1;
  

  这种方式下，优化器会为times的每一行单独生成foo的查询计划，从而触发分区裁剪。

• 预计算时间范围的全局边界，缩小扫描范围
  先算出times中所有时间范围的最小起始时间和最大结束时间，先过滤foo的分区，再做关联：

  with times as (
      select generate_series('2023-01-01 12:00', '2023-01-02 16:00', '1 hour') as date1,
             generate_series('2023-01-01 16:00', '2023-01-02 20:00', '1 hour') as date2
  ),
  time_bounds as (
      select min(date1) as min_date, max(date2) as max_date from times
  )
  select sum(f.count) as total,
         t.date1
  from times t
  join (
      select * from foo, time_bounds b
      where foo.date between b.min_date and b.max_date
  ) f on f.date between t.date1 and t.date2
  group by t.date1;
  

  这会先把foo的扫描范围限制在所有动态时间范围的覆盖边界内，大幅减少需要扫描的分区数量。

• 升级到PostgreSQL 13及以上版本
  PostgreSQL 13引入了运行时分区裁剪功能，支持在执行阶段针对动态生成的条件做分区裁剪。对于这类CTE关联的场景，无需修改查询语句，优化器就能自动识别并跳过无关分区。

• 使用自定义函数封装查询
  把单条时间范围的统计逻辑封装成SQL函数，函数内部的查询会针对每次传入的具体参数触发分区裁剪：

  create or replace function get_count_for_range(start_date timestamp, end_date timestamp)
  returns integer as $$
  select sum(count) from foo where date between start_date and end_date;
  $$ language sql stable;
  
  with times as (
      select generate_series('2023-01-01 12:00', '2023-01-02 16:00', '1 hour') as date1,
             generate_series('2023-01-01 16:00', '2023-01-02 20:00', '1 hour') as date2
  )
  select get_count_for_range(t.date1, t.date2) as total,
         t.date1
  from times t;
  

内容的提问来源于stack exchange，提问作者peter ignatiev