PostgreSQL关联分区键仅左表分区裁剪问题咨询

问题背景 #

创建两个以create_time为分区键的关联分区表，通过复合键(main_id, create_time)建立外键关联。测试发现：

• 使用简洁过滤条件 WHERE create_time >= '2023-04-01' 关联两表时，仅左表part_main实现分区裁剪，右表part_other会扫描全部3个分区；
• 显式指定两表过滤条件 WHERE part_main.create_time >= '2023-04-01' AND part_other.create_time >= '2023-04-01' 时，两张表均能实现分区裁剪。

现提出两个问题：

1. 这两种查询写法是否存在语义差异？是否可能返回不同结果？
2. 若两种写法语义一致，如何让查询优化器使用简洁写法生成理想的执行计划？

建表与测试数据SQL #

创建分区表 #

CREATE TABLE part_main (
   main_id serial,
   create_time timestamptz,
   main_val int,
   primary key (main_id, create_time)
 )
 PARTITION BY RANGE (create_time);

CREATE TABLE part_other (
   other_id serial,
   create_time timestamptz,
   main_id int,
   other_val text,
   primary key (other_id, create_time),
   foreign key (main_id, create_time) references part_main(main_id, create_time)
 )
 PARTITION BY RANGE (create_time);

CREATE TABLE part_main_y2023m02 PARTITION OF part_main
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE part_main_y2023m03 PARTITION OF part_main
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
CREATE TABLE part_main_y2023m04 PARTITION OF part_main
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

CREATE TABLE part_other_y2023m02 PARTITION OF part_other
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE part_other_y2023m03 PARTITION OF part_other
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
CREATE TABLE part_other_y2023m04 PARTITION OF part_other
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

插入测试数据 #

insert into part_main (create_time, main_val) values ('2023-04-02', 10);
insert into part_other (main_id, create_time, other_val) select main_id, create_time, 'foo' from part_main;

问题解答 #

1. 两种写法的语义是否存在差异？ #

不存在语义差异，不会返回不同结果。

由于part_other的(main_id, create_time)是关联part_main主键的外键，part_other中每条记录的(main_id, create_time)组合必须在part_main中存在对应记录。当使用WHERE create_time >= '2023-04-01'时，实际会匹配关联条件对应的part_main.create_time，只有part_main中符合条件的记录会被选中，而关联的part_other记录的create_time必然等于对应part_main记录的create_time，因此这些part_other记录也必然满足create_time >= '2023-04-01'。两种写法最终筛选的数据集完全一致。

2. 如何让优化器使用简洁写法实现分区裁剪？ #

在PostgreSQL 13-15版本中，优化器默认不会自动利用复合外键的关联关系推导part_other的分区裁剪条件，可通过以下方式解决：

• 升级到PostgreSQL 16+版本：16版本显著增强了分区裁剪逻辑，能自动利用外键约束传递谓词，无需修改查询写法即可实现双表分区裁剪。
• 确认分区裁剪参数状态：确保以下参数处于开启状态（默认已开启，可手动验证）：

  SET enable_partition_pruning = on;
  SET optimizer_prune_partitions = on;
  SET constraint_exclusion = partition;
  

• 更新表统计信息：执行ANALYZE part_main; ANALYZE part_other;，让优化器获取更准确的数据分布信息，辅助谓词推导。
• 使用查询提示（可选）：若上述方法无效，可借助pg_hint_plan插件添加提示，强制优化器利用外键关联传递谓词，示例：

  SELECT /*+ JOIN_FILTER(part_other part_main.create_time = part_other.create_time) */ *
  FROM part_main
  JOIN part_other ON part_main.main_id = part_other.main_id AND part_main.create_time = part_other.create_time
  WHERE create_time >= '2023-04-01';
  

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