PostgreSQL分区级连接在范围过滤下失效的问题排查

问题场景 #

我们有两张结构一致的双层分区表：

• items表：按group_id（text类型）列表分区，子分区按created_at（timestamptz类型）按年份范围分区
• things表：与items分区规则完全一致，且通过外键关联items的group_id, created_at, item_id

开启enable_partitionwise_join = on后，单独查询items时，指定group_id和created_at范围能正确修剪到对应子分区；但与things连接查询时，things仅按group_id修剪到things_a，却未按年份修剪，同时扫描things_a2022和things_a2023。仅当把created_at的范围过滤改为等值过滤时，things的子分区修剪才正常生效。

表结构与测试数据 #

create table if not exists items (
    group_id text,
    item_id integer,
    created_at timestamp with time zone,
    primary key (group_id, created_at, item_id)
) partition by list (group_id);

-- Partition by group ID.
create table items_a partition of items for values in ('a') partition by range (created_at);
create table items_b partition of items for values in ('b') partition by range (created_at);

-- Partition by year.
create table items_a2022 partition of items_a for values from ('2022-01-01') to ('2023-01-01');
create table items_a2023 partition of items_a for values from ('2023-01-01') to ('2024-01-01');
create table items_b2022 partition of items_b for values from ('2022-01-01') to ('2023-01-01');
create table items_b2023 partition of items_b for values from ('2023-01-01') to ('2024-01-01');

create table if not exists things (
    group_id text,
    item_id integer,
    item_created_at timestamp with time zone,
    FOREIGN KEY (group_id, item_created_at, item_id) REFERENCES items (group_id, created_at, item_id)
) partition by list(group_id);

-- Partition by group ID.
create table things_a partition of things for values in ('a') partition by range (item_created_at);
create table things_b partition of things for values in ('b') partition by range (item_created_at);

-- Partition by year.
create table things_a2022 partition of things_a for values from ('2022-01-01') to ('2023-01-01');
create table things_a2023 partition of things_a for values from ('2023-01-01') to ('2024-01-01');
create table things_b2022 partition of things_b for values from ('2022-01-01') to ('2023-01-01');
create table things_b2023 partition of things_b for values from ('2023-01-01') to ('2024-01-01');

-- 测试数据
insert into items (group_id, item_id, created_at) values ('a', 1, '2022-01-01');
insert into items (group_id, item_id, created_at) values ('b', 2, '2023-06-10');
insert into things (group_id, item_id, item_created_at) values ('a', 1, '2022-01-01');

-- 开启分区级连接
set enable_partitionwise_join = on;

异常执行计划（范围过滤） #

explain
select count(*)
from items
join things on things.item_id = items.item_id
    and things.item_created_at = items.created_at
    and things.group_id = items.group_id
where items.created_at >= '2022-05-01'::timestamptz
    and items.created_at <= '2022-06-01'::timestamptz
    and items.group_id = 'a';

执行计划显示things扫描了things_a2022和things_a2023：

Aggregate  (cost=56.67..56.68 rows=1 width=8)
  ->  Nested Loop  (cost=0.15..56.67 rows=1 width=0)
        Join Filter: ((items.item_id = things.item_id) AND (items.created_at = things.item_created_at))
        ->  Index Only Scan using items_a2022_pkey on items_a2022 items  (cost=0.15..8.17 rows=1 width=44)
              Index Cond: ((group_id = 'a'::text) AND (created_at >= '2022-05-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2022-06-01 00:00:00+00'::timestamp with time zone))
        ->  Append  (cost=0.00..48.31 rows=12 width=44)
              ->  Seq Scan on things_a2022 things_1  (cost=0.00..24.12 rows=6 width=44)
                    Filter: (group_id = 'a'::text)
              ->  Seq Scan on things_a2023 things_2  (cost=0.00..24.12 rows=6 width=44)
                    Filter: (group_id = 'a'::text)

正常执行计划（等值过滤） #

当把created_at改为等值过滤时，things仅扫描things_a2022：

explain
select count(*)
from items
join things on things.item_id = items.item_id
    and things.item_created_at = items.created_at
    and things.group_id = items.group_id
where items.created_at = '2022-05-01'::timestamptz
    and items.group_id = 'a';

执行计划：

Aggregate  (cost=35.14..35.15 rows=1 width=8)
  ->  Nested Loop  (cost=0.15..35.14 rows=1 width=0)
        Join Filter: (items.item_id = things.item_id)
        ->  Index Only Scan using items_a2022_pkey on items_a2022 items  (cost=0.15..8.17 rows=1 width=44)
              Index Cond: ((group_id = 'a'::text) AND (created_at = '2022-05-01 00:00:00+00'::timestamp with time zone))
        ->  Seq Scan on things_a2022 things  (cost=0.00..26.95 rows=1 width=44)
              Filter: ((item_created_at = '2022-05-01 00:00:00+00'::timestamp with time zone) AND (group_id = 'a'::text))

原因分析 #

1. 分区级连接的条件推导限制：PostgreSQL的分区级连接在处理多层分区时，对于范围类型的子分区键，仅当连接条件为等值匹配且过滤条件为等值条件时，才能将过滤条件完整传递到子分区进行修剪。当items的created_at是范围过滤时，优化器无法推导得出things的item_created_at必然落在同一范围，因此无法修剪子分区。
2. 顶层列表分区的修剪逻辑：group_id的修剪生效是因为连接条件是明确的等值匹配（things.group_id = items.group_id），且items的group_id是固定值（'a'），优化器能直接确定things只需扫描group_id='a'的顶层分区。
3. 版本一致性：该行为在PostgreSQL 13到15版本中一致，因为这一阶段的优化器尚未支持跨表的范围条件推导来完成子分区修剪。

解决方案 #

方案1：显式添加things的时间范围过滤 #

直接在WHERE子句中对things.item_created_at添加与items.created_at相同的范围条件，让优化器能直接修剪things的子分区：

explain
select count(*)
from items
join things on things.item_id = items.item_id
    and things.item_created_at = items.created_at
    and things.group_id = items.group_id
where items.created_at >= '2022-05-01'::timestamptz
    and items.created_at <= '2022-06-01'::timestamptz
    and items.group_id = 'a'
    and things.item_created_at >= '2022-05-01'::timestamptz
    and things.item_created_at <= '2022-06-01'::timestamptz;

方案2：使用子查询提前筛选items #

通过子查询或CTE提前筛选出符合条件的items数据，再与things连接，帮助优化器识别things的时间范围：

explain
select count(*)
from (
    select group_id, item_id, created_at
    from items
    where created_at >= '2022-05-01'::timestamptz
        and created_at <= '2022-06-01'::timestamptz
        and group_id = 'a'
) as filtered_items
join things on things.item_id = filtered_items.item_id
    and things.item_created_at = filtered_items.created_at
    and things.group_id = filtered_items.group_id;

方案3：升级到PostgreSQL 16+ #

PostgreSQL 16版本对分区修剪的推导逻辑进行了增强，可能能自动处理这种跨表的范围条件传递（需实际测试验证）。

内容的提问来源于stack exchange，提问作者Ciarán Tobin