云数据库 PostgreSQL 启发式优化器规则

一、功能概述

·enable_nl2hj：当外层小、内层大且外层无 Param 关联时，惩罚 NestLoop、让 HashJoin 胜出。

·enable_index：在满足 SAME / UNIQUE / INCLUDE 等子条件时偏置索引路径（可与 enable_seqscan 一同调节）。

·enable_danger_joinkeys：对配置为“danger”的连接列，对 MergeJoin/HashJoin 施加可调惩罚，倾向于回到 NestLoop 或换列。

二、前提条件

PostgreSQL 18，版本 ≥ v18.4_r1.10。

三、使用限制

·该插件需要通过 shared_preload_libraries 或 LOAD 加载生效。

·所有规则均为成本调整 / 候选路径过滤，不改变查询结果集，仅改变 EXPLAIN 形状与执行时间。

·主开关 tencentdb_heuristic.enable 关闭时，所有子规则一律失效。

四、创建插件

检查 shared_preload_libraries 已包含 tencentdb_heuristic：

postgres=> SHOW shared_preload_libraries;

创建扩展：

CREATE EXTENSION tencentdb_heuristic;SELECT * FROM pg_extension WHERE extname = 'tencentdb_heuristic';

会话级临时加载（仅当前 backend 生效）：

LOAD 'tencentdb_heuristic';

五、主开关与子开关

六、数值调参

七、字符串子选项

八、danger_joinkeys：让 MergeJoin/HashJoin 退回 NestLoop

当业务判定某些连接列（例如低基数、倾斜严重的 uid）不宜走哈希 / 排序，可将其加入 danger 列表。

先准备两张演示表（orders.uid 故意做成低基数、重度倾斜）：

CREATE TABLE public.users (id int PRIMARY KEY, name text);CREATE TABLE public.orders (id int PRIMARY KEY, uid int NOT NULL, amount numeric);INSERT INTO public.usersSELECT i, 'u' || i FROM generate_series(1, 100) i;– 100 万行订单，但 uid 只有 10 个不同值，且 90% 集中在 uid=1INSERT INTO public.ordersSELECT i, CASE WHEN random() < 0.9 THEN 1 ELSE (random() * 9)::int + 2 END, (random() * 1000)::numeric(10,2)FROM generate_series(1, 1000000) i;ANALYZE public.users, public.orders;

然后打开 danger：

LOAD 'tencentdb_heuristic';SET tencentdb_heuristic.enable = on;SET tencentdb_heuristic.enable_danger_joinkeys = on;SET tencentdb_heuristic.danger_joinkeys = 'public.orders(uid)';SET tencentdb_heuristic.joinkeys_magnify = 1000; — 强惩罚EXPLAIN (COSTS OFF)SELECT * FROM public.orders o JOIN public.users u ON o.uid = u.id; QUERY PLAN ——————————— Hash Join Hash Cond: (o.uid = u.id) -> Seq Scan on orders o -> Hash -> Seq Scan on users u(5 rows)

原本的 MergeJoin/HashJoin 会因 danger 列的额外成本被打压，从而落到 NestLoop。

关闭调试打印可用 SET tencentdb_heuristic.enable_danger_joinkeys_debug = on。

九、nl2hj：把 NestLoop 改写为 HashJoin

外层行少、内层行多且无参数化时，NestLoop 反而更慢。

先准备两张体积悬殊的演示表（外层5行、内层100万行，等值 join 列均匀分布）：

CREATE TABLE small_outer (k int PRIMARY KEY, tag text);CREATE TABLE big_inner (k int NOT NULL, payload text);INSERT INTO small_outerSELECT i, 't' || i FROM generate_series(1, 5) i;INSERT INTO big_innerSELECT (random() * 5)::int + 1, md5(i::text)FROM generate_series(1, 1000000) i;CREATE INDEX ON big_inner(k); — 有索引才能让 mainline 先选出 NL，随后被规则改写ANALYZE small_outer, big_inner;

然后打开 nl2hj：

SET tencentdb_heuristic.enable = on;SET tencentdb_heuristic.enable_nl2hj = on;SET tencentdb_heuristic.nl2hj_startup = 10; — 外层 < 10 行才保留 NLSET tencentdb_heuristic.nl2hj_magnify = 50;SET tencentdb_heuristic.nl2hj_options = 'cost,violence,precheck';EXPLAIN (COSTS OFF)SELECT * FROM small_outer o, big_inner b WHERE o.k = b.k; QUERY PLAN ————————————— Hash Join Hash Cond: (b.k = o.k) -> Seq Scan on big_inner b -> Hash -> Seq Scan on small_outer o(5 rows)

NL 路径的成本被放大50倍，HashJoin 胜出。

内层无索引可用的 NL 路径成本乘100，倾向切换到 HashJoin / MergeJoin。

十、enable_index：偏置索引路径选择

SET tencentdb_heuristic.enable = on;SET tencentdb_heuristic.enable_index = on;SET tencentdb_heuristic.enable_seqscan = on;SET tencentdb_heuristic.index_options = 'same,unique,include,cost';

index_options 中的每个子选项分别控制：

·same：命中相同列表达式时优先索引。

·unique：命中唯一约束/唯一索引时优先。

·include：INCLUDE 列可覆盖查询时优先。

·cost：打开索引-顺扫成本对比调整。

·debug：打印诊断信息。