云数据库 PostgreSQL OR 谓词展开为 UNION A

功能概述
针对以事实表为中心、以 OR 连接多个维度的星型 OR 谓词：
SELECT ...
FROM fact f, dim_1 d1, ..., dim_n dn
WHERE (f.k1 = d1.k AND p1(d1))
   OR (f.k2 = d2.k AND p2(d2))
   OR ...
   OR (f.kn = dn.k AND pn(dn));
主干优化器只会退化到事实表上的 BitmapOr，一旦命中 heap 就丢失了每个维度的选择率信息，导致大量无效读放大。
or_expansion 是云数据库 PostgreSQL 内置的规划期重写 pass，会将上述 N 路 OR 展开成 N 个独立的 JOIN 分支，然后由 Append 联合；从第2个分支起，每个分支都会追加一组累积的反重复过滤（anti-dup filter），保证与原始 OR 语义严格一致。展开后每条分支：
各自决定 join order、join 方法、以及每个维度可用的索引。
可以独立走并行 / 索引扫 / 分区裁剪。
结果集通过 Append 顺序输出，行数总和等于原始 OR 的结果基数（严格通过 EXCEPT ALL 双向核对）。
前提条件
PostgreSQL 18，版本 ≥ v18.4_r1.10。
使用限制
默认关闭。启用需要 两个 GUC 都 > 0：tencentdb_or_expansion_max_stmt_number 与 tencentdb_or_expansion_max_times。任一为 0 都视为关闭。
仅针对跨表的 OR 谓词生效：所有分支都指向同一个 relid（例如 WHERE d1_id = 10 OR d2_id = 20 OR d3_id = 30）时不改写，保留原始 BitmapOr。
展开会在语义层生成累积 anti-dup 过滤条件。
对 OUTER JOIN 的星型 OR，展开会尊重 outer-side NULL 扩展语义。
参数说明
参数
类型
默认
上下文
说明
tencentdb_or_expansion_max_stmt_number
int
0
USERSET
允许考虑展开的 OR 分支数量上限。0关闭优化。
tencentdb_or_expansion_max_times
int
0
USERSET
单条查询内递归展开的最大轮数（每个已展开分支还可以再被展开一次算一轮）。0关闭优化。
tencentdb_or_expansion_force
bool
off
USERSET
调试/回归用：强制选择展开后的 Append 路径，忽略 cost。生产环境不建议开启。
启用方法
在会话中启用（典型3 - 5分支）：
SET tencentdb_or_expansion_max_stmt_number = 5;
SET tencentdb_or_expansion_max_times       = 2;
或写入 postgresql.conf 全局启用：
tencentdb_or_expansion_max_stmt_number = 5
tencentdb_or_expansion_max_times = 2
关闭：把任一 GUC 设置回0即可。
使用示例
准备一个事实 + 4维度的最小演示：
CREATE TABLE or_exp_dim1 (d_id int PRIMARY KEY, d_tag text);
CREATE TABLE or_exp_dim2 (d_id int PRIMARY KEY, d_tag text);
CREATE TABLE or_exp_dim3 (d_id int PRIMARY KEY, d_tag text);
CREATE TABLE or_exp_dim4 (d_id int PRIMARY KEY, d_tag text);
CREATE TABLE or_exp_fact (
    id int PRIMARY KEY, d1_id int, d2_id int, d3_id int, d4_id int, val int
);
﻿
INSERT INTO or_exp_dim1 SELECT g, CASE WHEN g%5=0 THEN 'gold' ELSE 'silver' END FROM generate_series(1,50) g;
INSERT INTO or_exp_dim2 SELECT g, CASE WHEN g%5=0 THEN 'gold' ELSE 'silver' END FROM generate_series(1,50) g;
INSERT INTO or_exp_dim3 SELECT g, CASE WHEN g%5=0 THEN 'gold' ELSE 'silver' END FROM generate_series(1,50) g;
INSERT INTO or_exp_dim4 SELECT g, CASE WHEN g%5=0 THEN 'gold' ELSE 'silver' END FROM generate_series(1,50) g;
INSERT INTO or_exp_fact
SELECT g, 1+(g%50), 1+((g/7)%50), 1+((g/11)%50), 1+((g/13)%50), g%1000
FROM generate_series(1,5000) g;
﻿
CREATE INDEX or_exp_fact_d1 ON or_exp_fact(d1_id);
CREATE INDEX or_exp_fact_d2 ON or_exp_fact(d2_id);
CREATE INDEX or_exp_fact_d3 ON or_exp_fact(d3_id);
CREATE INDEX or_exp_fact_d4 ON or_exp_fact(d4_id);
ANALYZE or_exp_dim1; ANALYZE or_exp_dim2; ANALYZE or_exp_dim3;
ANALYZE or_exp_dim4; ANALYZE or_exp_fact;
基线：BitmapOr 计划（默认关闭）
RESET tencentdb_or_expansion_max_stmt_number;
RESET tencentdb_or_expansion_max_times;
RESET tencentdb_or_expansion_force;
﻿
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM or_exp_fact f, or_exp_dim1 d1, or_exp_dim2 d2, or_exp_dim3 d3
WHERE (f.d1_id = d1.d_id AND d1.d_tag = 'gold')
   OR (f.d2_id = d2.d_id AND d2.d_tag = 'gold')
   OR (f.d3_id = d3.d_id AND d3.d_tag = 'gold');
                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Nested Loop
         ->  Nested Loop
               ->  Nested Loop
                     ->  Seq Scan on or_exp_dim1 d1
                     ->  Materialize
                           ->  Seq Scan on or_exp_dim2 d2
               ->  Materialize
                     ->  Seq Scan on or_exp_dim3 d3
         ->  Bitmap Heap Scan on or_exp_fact f
               Recheck Cond: ((d1_id = d1.d_id) OR (d2_id = d2.d_id) OR (d3_id = d3.d_id))
               Filter: (((d1_id = d1.d_id) AND (d1.d_tag = 'gold'::text)) OR ((d2_id = d2.d_id) AND (d2.d_tag = 'gold'::text)) OR ((d3_id = d3.d_id) AND (d3.d_tag = 'gold'::text)))
               ->  BitmapOr
                     ->  Bitmap Index Scan on or_exp_fact_d1
                           Index Cond: (d1_id = d1.d_id)
                     ->  Bitmap Index Scan on or_exp_fact_d2
                           Index Cond: (d2_id = d2.d_id)
                     ->  Bitmap Index Scan on or_exp_fact_d3
                           Index Cond: (d3_id = d3.d_id)
(19 rows)
主干产出的是 Nested Loop + 事实表上的 BitmapOr / Seq Scan Filter 组合，维度端选择率无法透传。
启用后：Append(3分支) + 累积去重过滤
SET tencentdb_or_expansion_max_stmt_number = 5;
SET tencentdb_or_expansion_max_times       = 2;
﻿
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM or_exp_fact f, or_exp_dim1 d1, or_exp_dim2 d2, or_exp_dim3 d3
WHERE (f.d1_id = d1.d_id AND d1.d_tag = 'gold')
   OR (f.d2_id = d2.d_id AND d2.d_tag = 'gold')
   OR (f.d3_id = d3.d_id AND d3.d_tag = 'gold');
                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Hash Join
               Hash Cond: (d1.d_id = f.d1_id)
               ->  Nested Loop
                     ->  Nested Loop
                           ->  Seq Scan on or_exp_dim2 d2
                           ->  Materialize
                                 ->  Seq Scan on or_exp_dim3 d3
                     ->  Materialize
                           ->  Seq Scan on or_exp_dim1 d1
                                 Filter: (d_tag = 'gold'::text)
               ->  Hash
                     ->  Seq Scan on or_exp_fact f
         ->  Nested Loop
               ->  Nested Loop
                     Join Filter: ((NOT ((f.d1_id = d1.d_id) AND (d1.d_tag = 'gold'::text))) OR (((f.d1_id = d1.d_id) AND (d1.d_tag = 'gold'::text)) IS NULL))
                     ->  Nested Loop
                           ->  Seq Scan on or_exp_fact f
                           ->  Memoize
                                 Cache Key: f.d2_id
                                 Cache Mode: logical
                                 ->  Index Scan using or_exp_dim2_pkey on or_exp_dim2 d2
                                       Index Cond: (d_id = f.d2_id)
                                       Filter: (d_tag = 'gold'::text)
                     ->  Materialize
                           ->  Seq Scan on or_exp_dim1 d1
               ->  Materialize
                     ->  Seq Scan on or_exp_dim3 d3
         ->  Nested Loop
               Join Filter: ((NOT ((f.d2_id = d2.d_id) AND (d2.d_tag = 'gold'::text))) OR (((f.d2_id = d2.d_id) AND (d2.d_tag = 'gold'::text)) IS NULL))
               ->  Nested Loop
                     Join Filter: ((NOT ((f.d1_id = d1.d_id) AND (d1.d_tag = 'gold'::text))) OR (((f.d1_id = d1.d_id) AND (d1.d_tag = 'gold'::text)) IS NULL))
                     ->  Nested Loop
                           ->  Seq Scan on or_exp_fact f
                           ->  Memoize
                                 Cache Key: f.d3_id
                                 Cache Mode: logical
                                 ->  Index Scan using or_exp_dim3_pkey on or_exp_dim3 d3
                                       Index Cond: (d_id = f.d3_id)
                                       Filter: (d_tag = 'gold'::text)
                     ->  Materialize
                           ->  Seq Scan on or_exp_dim1 d1
               ->  Materialize
                     ->  Seq Scan on or_exp_dim2 d2
(45 rows)
计划形态：
Aggregate
  ->  Append
        ->  Nested Loop      -- arm 1: f×d1
              ...
        ->  Nested Loop      -- arm 2: f×d2, 附加 NOT(arm1 谓词)
              ...
        ->  Nested Loop      -- arm 3: f×d3, 附加 NOT(arm1) AND NOT(arm2)
              ...
OR 谓词展开为 UNION ALL

本页目录：

功能概述

前提条件

使用限制

参数说明

启用方法

使用示例

基线：BitmapOr 计划（默认关闭）

启用后：Append(3分支) + 累积去重过滤

参数	类型	默认	上下文	说明
tencentdb_or_expansion_max_stmt_number	int	0	USERSET	允许考虑展开的 OR 分支数量上限。0关闭优化。
tencentdb_or_expansion_max_times	int	0	USERSET	单条查询内递归展开的最大轮数（每个已展开分支还可以再被展开一次算一轮）。0关闭优化。
tencentdb_or_expansion_force	bool	off	USERSET	调试/回归用：强制选择展开后的 Append 路径，忽略 cost。生产环境不建议开启。