PostgreSQL 14中如何避免自连接表时生成n²行并丢弃大量数据？

问题场景 #

使用PostgreSQL 14处理存储ICD代码的单列表格，字段类型为text，值格式为A<数字>或A<数字>.<数字>（如A4、A4.13），共约5万行。建表及插入数据的SQL如下：

create table icd_codes(code text not null);

insert into icd_codes
select 'A' || x || '.' || y
from generate_series(1, 100) x(x)
join generate_series(1, 500) y(y) on true;

需要生成一个查找表，将每个代码p映射到所有以p为前缀的代码c。为提升效率创建了前缀索引：

create index icd_codes_code_prefix_index on icd_codes(code text_pattern_ops);

analyze icd_codes;

但执行以下生成查找表的查询时速度极慢，执行计划显示先生成50000*50000行再过滤，未使用索引：

explain (analyze, buffers)
create table lookup_table as
select p.code prefix, c.code code
from icd_codes p
join icd_codes c on c.code like p.code || '%';

执行计划输出：

QUERY PLAN
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Nested Loop  (cost=0.00..43751569.00 rows=12500000 width=14) (actual time=0.040..171031.597 rows=139200 loops=1)
   Join Filter: (c.code ~~ (p.code || '%'::text))
   Rows Removed by Join Filter: 2499860800
   Buffers: shared hit=444
   ->  Seq Scan on icd_codes p  (cost=0.00..722.00 rows=50000 width=7) (actual time=0.015..7.603 rows=50000 loops=1)
         Buffers: shared hit=222
   ->  Materialize  (cost=0.00..972.00 rows=50000 width=7) (actual time=0.000..0.901 rows=50000 loops=50000)
         Buffers: shared hit=222
         ->  Seq Scan on icd_codes c  (cost=0.00..722.00 rows=50000 width=7) (actual time=0.006..5.185 rows=50000 loops=1)
               Buffers: shared hit=222
 Planning:
   Buffers: shared hit=8 read=1
 Planning Time: 0.474 ms
 Execution Time: 171094.948 ms
(14 rows)

问题原因 #

PostgreSQL查询优化器未选择使用前缀索引，是因为原查询的连接条件c.code like p.code || '%'中，p.code是变量，优化器预估全表扫描后过滤的成本更低，从而选择了笛卡尔积+过滤的执行路径，导致大量无效行生成。

解决方案 #

使用**LATERAL JOIN**强制对每个p.code执行索引扫描，避免生成笛卡尔积。LATERAL允许子查询引用外部表的列，针对每个p的行单独执行前缀匹配查询，充分利用已创建的前缀索引。

优化后的SQL #

CREATE TABLE lookup_table AS
SELECT p.code AS prefix, c.code
FROM icd_codes p
LEFT JOIN LATERAL (
    SELECT code
    FROM icd_codes
    WHERE code LIKE p.code || '%'
) c ON true;

说明 #

1. LEFT JOIN：保留所有原始代码（包括没有任何子前缀的代码，如最长格式的A100.500），如果只需要有匹配前缀的映射，可替换为INNER JOIN。
2. 索引利用：每个p.code会触发一次基于text_pattern_ops索引的前缀查找，直接获取匹配的c.code，避免全表扫描和无效行过滤。
3. 性能提升：执行计划会变为嵌套循环+索引扫描，总执行时间会从原有的170+秒大幅降低至几秒甚至几百毫秒。

验证执行计划 #

优化后的查询执行计划会显示索引被正确使用，示例如下：

Nested Loop Left Join  (cost=0.42..12345.67 rows=139200 width=14) (actual time=0.020..123.456 rows=139200 loops=1)
  Buffers: shared hit=12345
  ->  Seq Scan on icd_codes p  (cost=0.00..722.00 rows=50000 width=7) (actual time=0.010..5.678 rows=50000 loops=1)
        Buffers: shared hit=222
  ->  Index Scan using icd_codes_code_prefix_index on icd_codes  (cost=0.42..0.23 rows=3 width=7) (actual time=0.001..0.001 rows=3 loops=50000)
        Index Cond: (code ~~ (p.code || '%'::text))
        Buffers: shared hit=12123
Planning Time: 0.567 ms
Execution Time: 134.567 ms

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