一、功能概述
主干优化器只会退化到事实表上的 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 = 5tencentdb_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_factSELECT g, 1+(g%50), 1+((g/7)%50), 1+((g/11)%50), 1+((g/13)%50), g%1000FROM 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 d3WHERE (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 d3WHERE (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) …