【金仓数据库产品体验官】让慢 SQL 现出原形：Memoize、Incremental Sort 与连接消除实测

#数据库平替用金仓# #金仓产品体验官#

金仓这套 12.1 内核里，计划器这边有几个在 PostgreSQL 系里相对新的动作：Memoize 缓存嵌套循环的参数、Incremental Sort 借用索引已有的排序、以及把冗余的连接直接删掉。我拿一台麒麟 V10 测试机造了 300 万行订单数据，把这几件事一件件拆开对照：同一句 SQL，只切一个开关，看执行次数、缓冲区访问、内存和磁盘到底差在哪里。结论里有几条和直觉相反，尤其是 Memoize，它并不是白给的。

阅读约定：代码块中 # 开头的是 root 命令，$ 开头的是 kingbase 用户的命令，其余都是终端里的实际输出。

一、机器、版本与测试数据

测试数据是我自己建的三张表，规模刻意做成「维度小、事实大、并且连接键高度重复」的形状，这样 Memoize 才有戏：

$ ksql -U system -d test -p 54321 -Atc "select 'city' as t, count(*) from t_sq_city union all select 'users', count(*) from t_sq_users union all select 'orders', count(*) from t_sq_orders;"
city|200
users|50000
orders|3000000
$ ksql -U system -d test -p 54321 -Atc "select count(*) as distinct_users_in_orders, count(*)/count(distinct user_id) as orders_per_user from t_sq_orders;"
3000000|60
$ ksql -U system -d test -p 54321 -Atc "select relname, pg_size_pretty(pg_total_relation_size(oid)) as size from pg_class where relname in ('t_sq_city','t_sq_users','t_sq_orders') order by 1;"
t_sq_city|64 kB
t_sq_orders|374 MB
t_sq_users|3720 kB

表结构就三张：

create table t_sq_city(city_id int primary key, city_name text);
create table t_sq_users(user_id int primary key, city_id int, level int, nick text);
create table t_sq_orders(order_id int, user_id int, city_id int, amount numeric(10,2), created_at timestamp, pad text);
create index t_sq_orders_city_idx on t_sq_orders(city_id);
create index t_sq_orders_user_idx on t_sq_orders(user_id);

订单表的 user_id 取值是 (order_id % 50000) + 1，所以 300 万订单散在 5 万个用户上，平均每人 60 单，这就是「重复外部参数」的天然来源。city_id 只在 1 到 200 之间，相当于一张 200 行的维表被反复引用。

有一点要提前说清楚：为了让对照干净，下面大部分实验我都把并行关掉（max_parallel_workers_per_gather = 0）。开着并行虽然更快，但计划树里会多出 Gather 和多份 worker 计数，讨论「这个节点有没有用」时反而看不清。真实生产里不必照抄这个设置。

二、这套计划器有哪些开关

把 enable_ 开头的参数全捞出来，能看到金仓在标准 PostgreSQL 之外自己加了一批：

$ ksql -U system -d test -p 54321 -Atc "select name, setting, short_desc from pg_settings where name ~ 'elimin|unique|subquery|unnest' order by 1;"
enable_semi_to_unique_inner|on|Enables the planner's transform semi join to unique inner join.
enable_unnest_expr_sublink|off|Expr Sublink unnesting.
kdb_rbo.enable_scalar_subquery_removal|off|Enable optimizer to remove scalar subquery in target.
kdb_rbo.enable_subquery_qual_push|on|subquery qual push down or up .

enable_semi_to_unique_inner 这个开关很重要，后面第六节会用它来解释一个容易被误读的现象：为什么 exists 子查询的执行计划里根本看不到 Semi 字样。

另外几个跟本次实验直接相关的开关，默认值如下：

$ ksql -U system -d test -p 54321 -Atc "select name, setting, source from pg_settings where name in ('enable_memoize','enable_incremental_sort','enable_semi_to_unique_inner','max_parallel_workers_per_gather','work_mem','kdb_rbo.enable_scalar_subquery_removal') order by 1;"
enable_incremental_sort|on|default
enable_memoize|on|default
enable_semi_to_unique_inner|on|default
kdb_rbo.enable_scalar_subquery_removal|off|default
max_parallel_workers_per_gather|2|default
work_mem|4096|default

三、场景一：重复外部参数，Memoize 到底值不值

3.1 先把嵌套循环逼出来

这条 SQL 的连接键重复度很高，优化器默认会选哈希连接，那样就看不到 Memoize 了：

$ ksql -U system -d test -p 54321 -Atc "explain (analyze, buffers, costs off) select u.level, count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id group by u.level;"
Finalize GroupAggregate (actual time=598.747..610.800 rows=5 loops=1)
  Group Key: u.level
  Buffers: shared hit=16348 read=15971
  ->  Gather Merge (actual time=598.739..610.789 rows=15 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort (actual time=590.640..590.648 rows=5 loops=3)
              ->  Partial HashAggregate (actual time=590.529..590.537 rows=5 loops=3)
                    ->  Hash Join (actual time=18.513..420.843 rows=1000000 loops=3)
                          Hash Cond: (o.user_id = u.user_id)
                          ->  Parallel Seq Scan on t_sq_orders o (actual time=0.034..143.903 rows=1000000 loops=3)
                          ->  Hash (actual time=18.041..18.042 rows=50000 loops=3)
Planning Time: 0.736 ms
Execution Time: 610.895 ms

Memoize 是嵌套循环内表的一个缓存节点，哈希连接里不出现。所以我用 enable_hashjoin = off 加 enable_mergejoin = off 把执行路径固定成嵌套循环，再做对照。这是一个受控实验，不是推荐在生产上关哈希连接。

3.2 关掉 Memoize：内表被扫 20 万次

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = off; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000;"
Aggregate (actual time=720.067..720.069 rows=1 loops=1)
  Buffers: shared hit=615524 read=15726
  ->  Nested Loop (actual time=0.069..703.192 rows=200000 loops=1)
        ->  Seq Scan on t_sq_orders o (actual time=0.039..371.777 rows=200000 loops=1)
              Filter: (order_id <= 200000)
              Rows Removed by Filter: 2800000
        ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=200000)
              Index Cond: (user_id = o.user_id)
              Heap Fetches: 200000
              Buffers: shared hit=600000
Planning Time: 0.453 ms
Execution Time: 720.116 ms

内表 Index Only Scan 的 loops 是 200000，也就是外表的每一行都去查了一次索引，总缓冲区访问 60 万块。连跑三次，执行时间分别是 720.116 ms、719.611 ms、721.473 ms，非常稳定。

3.3 打开 Memoize：扫描降到 17 万次，但总耗时反而涨了

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = on; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000;"
Aggregate (actual time=768.549..768.551 rows=1 loops=1)
  Buffers: shared hit=525591 read=15668
  ->  Nested Loop (actual time=1.601..747.816 rows=200000 loops=1)
        ->  Seq Scan on t_sq_orders o (actual time=0.229..376.363 rows=200000 loops=1)
              Filter: (order_id <= 200000)
              Rows Removed by Filter: 2800000
        ->  Memoize (actual time=0.002..0.002 rows=1 loops=200000)
              Cache Key: o.user_id
              Cache Mode: logical
              Hits: 29997  Misses: 170003  Evictions: 0  Overflows: 0  Skips: 160004  Memory Usage: 3672kB
              Buffers: shared hit=510009
              ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=170003)
                    Index Cond: (user_id = o.user_id)
                    Heap Fetches: 170003
                    Buffers: shared hit=510009
Planning Time: 0.563 ms
Execution Time: 770.193 ms

三个数字要连起来看。内表扫描从 200000 次降到 170003 次，只降了 15%；缓冲区访问从 60 万块降到 51 万块；但执行时间从约 720 ms 涨到约 770~783 ms（三次分别 770.193、782.632、770.926），比不开还慢 7% 左右。

原因就在 Hits: 29997 上。20 万次调用里只有 3 万次命中缓存，剩下 17 万次还是老老实实去查索引，而这 17 万次每条都要先做一次哈希查找，省下的索引扫描，抵不上多出来的缓存维护开销。

3.4 关键变量是「外部键的工作集装不装得下」

命中率低，第一反应是缓存不够大。Memoize 的缓存理论上受 work_mem 约束，我把 work_mem 从 4MB 一路调到 256MB：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = on; set work_mem = '4MB'; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000;"
        ->  Memoize (actual time=0.002..0.002 rows=1 loops=200000)
              Hits: 29997  Misses: 170003  Evictions: 0  Overflows: 0  Skips: 160004  Memory Usage: 3672kB
              ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=170003)
Planning Time: 0.484 ms
Execution Time: 774.640 ms

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = on; set work_mem = '256MB'; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000;"
        ->  Memoize (actual time=0.002..0.002 rows=1 loops=200000)
              Hits: 29997  Misses: 170003  Evictions: 0  Overflows: 0  Skips: 160004  Memory Usage: 3672kB
              ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=170003)
Planning Time: 0.496 ms
Execution Time: 773.678 ms

4MB 和 256MB 的结果一模一样：命中数、内存占用、扫描次数一个字都没变，缓存占用稳稳停在 3672kB。也就是说，在这套 V009R002C016 上，Memoize 的缓存规模不随 work_mem 变，它自己有个上限。

换个角度验证这个上限：把外表的过滤条件收紧，让不同的 user_id 个数从 5 万降到 3 万、1 万，再看命中情况。

$ ksql -U system -d test -p 54321 -Atc "select 10000 as key_limit, count(*) as outer_rows, count(distinct user_id) as distinct_keys from t_sq_orders where order_id <= 200000 and user_id <= 10000;"
10000|40000|10000
$ ksql -U system -d test -p 54321 -Atc "select 30000 as key_limit, count(*) as outer_rows, count(distinct user_id) as distinct_keys from t_sq_orders where order_id <= 200000 and user_id <= 30000;"
30000|120000|30000
$ ksql -U system -d test -p 54321 -Atc "select 50000 as key_limit, count(*) as outer_rows, count(distinct user_id) as distinct_keys from t_sq_orders where order_id <= 200000 and user_id <= 50000;"
50000|200000|50000

三种规模下的 Memoize 表现：

看第一行：外部键只有 1 万个，缓存装得下，命中 29997 次，内表只扫了 10003 次，比不开 Memoize 的 40000 次少了 75%，缓冲区访问从 135636 块降到 45631 块，执行时间也真的快了（418.9 ms 对 450.3 ms）。

再看后两行：不同键涨到 3 万、5 万，缓存装不下，Hits 就死死卡在 29997 不动了，Misses 跟着涨；这时候 Memoize 从「帮手」变成了「负担」，反而比不开慢 2% 到 7%。

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = on; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000 and o.user_id <= 10000;"
        ->  Memoize (actual time=0.001..0.001 rows=1 loops=40000)
              Cache Key: o.user_id
              Cache Mode: logical
              Hits: 29997  Misses: 10003  Evictions: 0  Overflows: 0  Skips: 4  Memory Usage: 1016kB
              ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=10003)
Execution Time: 418.881 ms
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_hashjoin = off; set enable_mergejoin = off; set enable_memoize = off; explain (analyze, buffers, costs off) select count(*) from t_sq_orders o join t_sq_users u on u.user_id = o.user_id where o.order_id <= 200000 and o.user_id <= 10000;"
        ->  Index Only Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=40000)
              Buffers: shared hit=120000
Execution Time: 450.288 ms

判定：Memoize 的收益不是一个「开了就好」的开关，而是一道算术题：外部键的工作集要装得进它那约 3 万条的缓存。装得下，内表扫描和缓冲区访问都大幅下降；装不下，命中数在 3 万处封顶，多出来的哈希查找反而拖慢整体。看计划时别只看到 Memoize 三个字就安心，要读它下面那几行 Hits 和 Misses。

四、场景二：排序键带索引前缀，Incremental Sort

第二类慢 SQL 是排序。订单表上有一个 city_id 的单列索引，而业务要的是「按城市、再按下单时间」排序：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_incremental_sort = on; explain (analyze, buffers, costs off) select order_id, city_id, created_at from t_sq_orders order by city_id, created_at limit 1000;"
Limit (actual time=136.209..136.339 rows=1000 loops=1)
  Buffers: shared hit=1749 read=13305 written=3726
  ->  Incremental Sort (actual time=136.208..136.267 rows=1000 loops=1)
        Sort Key: city_id, created_at
        Presorted Key: city_id
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
        Pre-sorted Groups: 1  Sort Method: top-N heapsort  Average Memory: 103kB  Peak Memory: 103kB
        ->  Index Scan using t_sq_orders_city_idx on t_sq_orders (actual time=0.045..131.763 rows=15001 loops=1)
Planning Time: 0.360 ms
Execution Time: 136.409 ms

Incremental Sort 这一行里有三个字段是判断依据：Presorted Key: city_id 说明它借用了索引已经排好的前缀，Pre-sorted Groups: 1 说明只处理了一个组，内存峰值 103kB。整条 SQL 只从索引里读了 15001 行就凑够了前 1000 条，136 毫秒结束。

关掉这个特性，优化器只能老老实实全表扫再整体排序：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_incremental_sort = off; explain (analyze, buffers, costs off) select order_id, city_id, created_at from t_sq_orders order by city_id, created_at limit 1000;"
Limit (actual time=1066.456..1066.579 rows=1000 loops=1)
  ->  Sort (actual time=1066.455..1066.507 rows=1000 loops=1)
        Sort Key: city_id, created_at
        Sort Method: top-N heapsort  Memory: 139kB
        ->  Seq Scan on t_sq_orders (actual time=0.022..586.044 rows=3000000 loops=1)
Planning Time: 0.293 ms
Execution Time: 1066.646 ms

同样是取 1000 行，1066.6 ms 对 136.4 ms，差了 7.8 倍。差别全在「要不要先把 300 万行读出来」：关掉增量排序后，计划里变成了 Seq Scan on t_sq_orders ... rows=3000000。

把数据量放大到全量 300 万行，差距换了个形态，从时间差变成了磁盘差：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_incremental_sort = on; explain (analyze, buffers, costs off) select order_id, city_id, created_at from t_sq_orders order by city_id, created_at;"
Incremental Sort (actual time=25.554..3042.779 rows=3000000 loops=1)
  Sort Key: city_id, created_at
  Presorted Key: city_id
  Full-sort Groups: 200  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
  Pre-sorted Groups: 200  Sort Method: quicksort  Average Memory: 1205kB  Peak Memory: 1205kB
  Buffers: shared hit=2967542 read=40711 written=2
  ->  Index Scan using t_sq_orders_city_idx on t_sq_orders (actual time=0.020..2438.832 rows=3000000 loops=1)
Planning Time: 0.290 ms
Execution Time: 3168.642 ms

200 个城市被拆成 200 个排序组，每组单独快排，峰值内存 1205kB，全程没有落盘。

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_incremental_sort = off; explain (analyze, buffers, costs off) select order_id, city_id, created_at from t_sq_orders order by city_id, created_at;"
Sort (actual time=3549.697..4132.882 rows=3000000 loops=1)
  Sort Key: city_id, created_at
  Sort Method: external merge  Disk: 92248kB
  Buffers: shared hit=16042 read=15214, temp read=22608 written=25458
  ->  Seq Scan on t_sq_orders (actual time=0.023..596.228 rows=3000000 loops=1)
Planning Time: 0.348 ms
Execution Time: 4282.341 ms

关掉之后是 external merge，临时文件写了 92248kB、也就是 90MB。执行时间 4282.3 ms 对 3168.6 ms，增量排序快 1.35 倍。但真正值钱的是那 90MB 临时写。排序是并发场景下最容易放大 IO 的地方，省掉它比省一秒更实在。

前提也要说清楚：这一切成立的前提是排序键有一个「索引能给的前缀」。换成 order by created_at, city_id，没有任何索引能提供 created_at 的前缀，增量排序就不会出现：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_incremental_sort = on; explain (analyze, buffers, costs off) select order_id, city_id, created_at from t_sq_orders order by created_at, city_id limit 1000;"
Limit (actual time=1073.928..1074.053 rows=1000 loops=1)
  ->  Sort (actual time=1073.927..1073.981 rows=1000 loops=1)
        Sort Key: created_at, city_id
        Sort Method: top-N heapsort  Memory: 103kB
        ->  Seq Scan on t_sq_orders (actual time=0.067..593.002 rows=3000000 loops=1)
Execution Time: 1074.125 ms

判定：看到 Incremental Sort 和 Presorted Key，说明优化器找到了排序键的前缀索引；要想让它出现，索引列的顺序必须和 order by 的前缀对齐，差一个列序都不行。

五、场景三：多表冗余连接，什么时候敢删

第三类慢 SQL 常见于报表：为了取字段，把一堆维表都连上，最后 SELECT 列表里其实一个字段都没用到。

维表 t_sq_city 的 city_id 是主键，订单表里只取自己的列：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id, o.amount from t_sq_orders o left join t_sq_city c on c.city_id = o.city_id;"
Seq Scan on t_sq_orders o

整个连接不见了。计划里只剩订单表的一次顺序扫描。

一旦 SELECT 列表里用到右表的列，连接就必须留下：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id, c.city_name from t_sq_orders o left join t_sq_city c on c.city_id = o.city_id;"
Hash Left Join
  Hash Cond: (o.city_id = c.city_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  Seq Scan on t_sq_city c

真正要命的是第三种情况：右表的连接键不唯一。我专门造了一张 400 行、但 city_id 只有 200 个不同值的表：

$ ksql -U system -d test -p 54321 -Atc "select count(*) as rows_all, count(distinct city_id) as distinct_city from t_sq_city_dup;"
400|200
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id from t_sq_orders o left join t_sq_city_dup d on d.city_id = o.city_id;"
Hash Left Join
  Hash Cond: (o.city_id = d.city_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  Seq Scan on t_sq_city_dup d

连接被保留了。原因用行数一量就明白：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select count(*) as left_join_rows from (select o.order_id from t_sq_orders o left join t_sq_city_dup d on d.city_id = o.city_id) t;"
6000000
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select count(*) as left_table_rows from t_sq_orders o;"
3000000

左连接把 300 万行放大成了 600 万行。要是优化器这时候把连接删掉，结果就错了。所以唯一性是这个优化的安全前提，不是可选项。

内连接的情况又不一样。同样连主键维表，只是不改写成左连接：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id from t_sq_orders o join t_sq_city c on c.city_id = o.city_id;"
Hash Join
  Hash Cond: (o.city_id = c.city_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  Seq Scan on t_sq_city c

这次连接没被删。原因是光有唯一性还不够：内连接还承担着「过滤掉匹配不上的行」的职责，而优化器没法证明每一行都能匹配上。给它一个真外键，它才敢：

$ ksql -U system -d test -p 54321 -Atc "alter table t_sq_orders add constraint t_sq_orders_city_fk foreign key (city_id) references t_sq_city(city_id);"
ALTER TABLE
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id from t_sq_orders o join t_sq_city c on c.city_id = o.city_id;"
Seq Scan on t_sq_orders o
  Filter: (city_id IS NOT NULL)

连接消失了，但留下了一句 Filter: (city_id IS NOT NULL)。这个细节很能说明问题：外键保证了非空的 city_id 一定能匹配上维表，可 city_id 本身还允许为 NULL，而 NULL 是匹配不上任何行的，所以那句过滤必须留着。优化器删掉的是连接，不是语义。

日常写法上其实有个更省事的办法：既然左连接在「维表唯一 + 不取右表列」时会被删掉，报表 SQL 里把维表写成 LEFT JOIN 反而比写成 INNER JOIN 更容易被优化掉。当然前提是维表的那个键上真的建了主键或唯一约束。

六、场景四：EXISTS 和相关子查询，计划该怎么读

6.1 为什么 EXISTS 的计划里看不到 Semi

客户表 t_sq_users 的主键是 user_id，用 exists 去过滤订单：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; show enable_semi_to_unique_inner; explain (costs off, summary off) select o.order_id from t_sq_orders o where exists (select 1 from t_sq_users u where u.user_id = o.user_id);"
on
Hash Join
  Hash Cond: (o.user_id = u.user_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  Seq Scan on t_sq_users u

计划里是干干净净的 Hash Join，一个 Semi 字样都没有。第一次看到会以为优化器把 EXISTS 当成普通连接处理了，那语义不就错了？其实不是：enable_semi_to_unique_inner 这个开关的说明写得很直白，Enables the planner's transform semi join to unique inner join，也就是当子查询的连接键在内表上唯一时，半连接可以安全地改写成一比一的普通连接，因为一个外行最多匹配一行，去重这件事本身就多余了。

内表的键不唯一时，去重步骤就出来了：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id from t_sq_orders o where exists (select 1 from t_sq_orders x where x.user_id = o.user_id and x.order_id <= 10000);"
Hash Join
  Hash Cond: (o.user_id = x.user_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  HashAggregate
              Group Key: x.user_id
              ->  Seq Scan on t_sq_orders x
                    Filter: (order_id <= 10000)

内表先被 HashAggregate 按 user_id 去重，再拿去做哈希连接。这正是半连接的语义：一个外行只保留一份。

那 Semi 节点到底存不存在？答案在非唯一键这一侧更容易看到。把半连接改写开关关掉，节点名就变了：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set enable_semi_to_unique_inner = off; explain (costs off, summary off) select o.order_id from t_sq_orders o where exists (select 1 from t_sq_orders x where x.user_id = o.user_id and x.order_id <= 10000);"
Merge Semi Join
  Merge Cond: (o.user_id = x.user_id)
  ->  Index Scan using t_sq_orders_user_idx on t_sq_orders o
  ->  Sort
        Sort Key: x.user_id
        ->  Seq Scan on t_sq_orders x
              Filter: (order_id <= 10000)

Merge Semi Join。所以读计划时不能只搜「Semi」这个词：哈希路径下，半连接会被重写成「去重 + 内连接」，节点名里一个 Semi 都不留；归并路径下才会直接叫 Semi Join。

顺带记一笔：在内表键唯一的那种写法上，把 enable_semi_to_unique_inner 关掉之后，计划仍然是 Hash Join，并没有变回 Semi。这个开关的实际触发条件比名字暗示的要宽，判断时还是以计划里的 HashAggregate 去重节点为准。

反连接就不一样，它的名字一直很老实：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (costs off, summary off) select o.order_id from t_sq_orders o where not exists (select 1 from t_sq_users u where u.user_id = o.user_id);"
Hash Anti Join
  Hash Cond: (o.user_id = u.user_id)
  ->  Seq Scan on t_sq_orders o
  ->  Hash
        ->  Seq Scan on t_sq_users u

6.2 把 EXISTS 手写成 JOIN，行数会炸

有些人的习惯是「EXISTS 一律改成 JOIN，看着直观」。同一个子表自己连自己，两种写法的行数差得不是一点：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select count(*) as exists_rows from t_sq_orders o where o.order_id <= 5000 and exists (select 1 from t_sq_orders x where x.user_id = o.user_id);"
5000
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select count(*) as inner_join_rows from t_sq_orders o join t_sq_orders x on x.user_id = o.user_id where o.order_id <= 5000;"
300000

EXISTS 给 5000 行，JOIN 给 30 万行。每个用户平均 60 单，全被乘进去了。业务上多出来的这 29.5 万行会一路传到报表和下游，这种错最难查，因为 SQL 不报错。

6.3 SELECT 列表里的标量子查询：逐行执行，开关救不了

最后一种写法是把维表字段写成括号里的子查询，这种 SQL 在 Oracle 迁移场景里特别多：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (analyze, buffers, costs off) select o.order_id, (select u.nick from t_sq_users u where u.user_id = o.user_id) as nick from t_sq_orders o where o.order_id <= 100000;"
Seq Scan on t_sq_orders o (actual time=0.085..565.020 rows=100000 loops=1)
  Filter: (order_id <= 100000)
  Rows Removed by Filter: 2900000
  Buffers: shared hit=314919 read=16331
  SubPlan 1
    ->  Index Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=100000)
          Index Cond: (user_id = o.user_id)
          Buffers: shared hit=300000
Planning Time: 0.323 ms
Execution Time: 571.429 ms

关键还是 loops：SubPlan 1 里的索引扫描执行了 100000 次，外表有多少行就执行多少次。我试过把计划器里两个看起来对口的开关打开：

$ ksql -U system -d test -p 54321 -Atc "select name, setting, short_desc from pg_settings where name ~ 'scalar_subquery|unnest|sublink' order by 1;"
change_any_sublink_to_join|not_relevant_sublink|convert sublinks of anyexpr to semijoin or antijoin.
enable_unnest_expr_sublink|off|Expr Sublink unnesting.
kdb_rbo.enable_coalesce_exists_sublink|off|Expand the plan if there is EXISTING sublink.
kdb_rbo.enable_scalar_subquery_removal|off|Enable optimizer to remove scalar subquery in target.
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; set kdb_rbo.enable_scalar_subquery_removal = on; explain (analyze, buffers, costs off) select o.order_id, (select u.nick from t_sq_users u where u.user_id = o.user_id) as nick from t_sq_orders o where o.order_id <= 100000;"
  SubPlan 1
    ->  Index Scan using t_sq_users_pkey on t_sq_users u (actual time=0.001..0.001 rows=1 loops=100000)
          Index Cond: (user_id = o.user_id)
Execution Time: 577.942 ms

Plan 树纹丝不动，还是 10 万次。enable_unnest_expr_sublink 单独打开、和 kdb_rbo.enable_scalar_subquery_removal 两个一起打开，结果都一样。这条 SQL 想快，只能自己改写：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; explain (analyze, buffers, costs off) select o.order_id, u.nick from t_sq_orders o left join t_sq_users u on u.user_id = o.user_id where o.order_id <= 100000;"
Hash Left Join (actual time=18.805..417.575 rows=100000 loops=1)
  Hash Cond: (o.user_id = u.user_id)
  Buffers: shared hit=15272 read=16299
  ->  Seq Scan on t_sq_orders o (actual time=0.039..372.955 rows=100000 loops=1)
        Filter: (order_id <= 100000)
        Rows Removed by Filter: 2900000
  ->  Hash (actual time=18.476..18.477 rows=50000 loops=1)
        Buckets: 65536  Batches: 1  Memory Usage: 2661kB
        ->  Seq Scan on t_sq_users u (actual time=0.010..8.481 rows=50000 loops=1)
Planning Time: 0.694 ms
Execution Time: 421.845 ms

改成左连接之后，从 571.4 ms 降到 421.8 ms，快了 26%；内表索引扫描的 10 万次变成了 1 次哈希表构建，缓冲区访问从 31 万块降到 1.5 万块。抽样核对结果一致：

$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select o.order_id, (select u.nick from t_sq_users u where u.user_id = o.user_id) as nick_scalar from t_sq_orders o where o.order_id <= 5 order by 1;"
1|u2
2|u3
3|u4
4|u5
5|u6
$ ksql -U system -d test -p 54321 -Atc "set max_parallel_workers_per_gather = 0; select o.order_id, u.nick as nick_join from t_sq_orders o left join t_sq_users u on u.user_id = o.user_id where o.order_id <= 5 order by 1;"
1|u2
2|u3
3|u4
4|u5
5|u6

注意这里必须用 left join 而不是 join：标量子查询取不到值的时候返回 NULL，主表一行都不会少，写成内连接会把匹配不上的行直接丢掉。

七、四个场景的判定合到一张表

八、这一趟踩到的坑

坑一：pg_indexes 的列名记错了。

我想看订单表上的索引定义，凭印象写了 indexrelname，那是 pg_stat_user_indexes 的列名：

$ ksql -U system -d test -p 54321 -Atc "select indexrelname, indexdef from pg_indexes where tablename='t_sq_orders';"
ERROR:  column "indexrelname" does not exist
第1行select indexrelname, indexdef from pg_indexes where tablenam...
            ^
提示:  Perhaps you meant to reference the column "pg_indexes.indexname".

报错里的提示直接把正确列名给了出来：indexname。这两个视图的列名接近但不通用，写巡检脚本时值得注意。

坑二：以为 EXISTS 的计划里一定有 Semi。

第一次看 exists 的执行计划，满屏找不到 Semi，我一度怀疑是不是被当成了普通内连接、语义会出错。翻到 enable_semi_to_unique_inner 的说明才明白这是有意为之的改写，又用非唯一键的表和关闭开关两种情况交叉验证，才把它定性清楚。教训是：参数说明里的 short_desc 往往比文档更快，遇到看不懂的节点先去 pg_settings 里搜一圈。

坑三：以为调大 work_mem 能救 Memoize 的命中率。

Memoize 命中率低，我的第一反应是缓存太小，把 work_mem 从 4MB 一路加到 256MB，指望缓存能装下 5 万个键。结果是三个数字一动不动。这也算个收获：这个版本的 Memoize 缓存不跟 work_mem 走，想靠调大内存提升命中率这条路是走不通的，只能从 SQL 形态上下手。