Postgres 14.6含LIKE的关联排序查询性能优化求助

场景与原始查询 #

两张表各约1亿条记录，其中tvxa.ttvx63_ult_ric的c_tar字段（varchar(30)）存储车牌信息，需通过该字段的LIKE条件关联tvxa.ttvx51_msg_81，并按关联表的d_trn字段降序取前10条数据，原始查询如下：

select *
from tvxa.ttvx51_msg_81 as ttvx51
left join tvxa.ttvx63_ult_ric as fnl on ttvx51.c_id_msg = fnl.c_id_msg and fnl.c_tip_ric = ('FNL')
where fnl.c_tar ilike any ('{"GD47%", "CD529%", "CX7530_", "GL573S_"}')
order by ttvx51.d_trn desc
limit 10 offset 0;

现有索引配置 #

• ttvx51.d_trn：标准B-tree索引
• fnl.c_tar：GIST和B-tree索引
• c_id_msg：两张表的主键

性能问题现象 #

• 部分LIKE条件查询耗时极长：比如前缀为5个字符的AB142%，查询耗时超20分钟；部分条件仅需毫秒级完成。
• 移除ORDER BY子句后，查询仅需数秒即可完成。

不同场景的执行计划 #

1. 带ORDER BY、5字符前缀LIKE的查询计划 #

Limit  (cost=1001.16..59950.24 rows=10 width=274)
  ->  Gather Merge  (cost=1001.16..48020923.65 rows=8146 width=274)
        Workers Planned: 2
        ->  Nested Loop  (cost=1.14..48018983.37 rows=3394 width=274)
              ->  Parallel Index Scan Backward using ittvx51_msg_81_migrazione2 on ttvx51_msg_81 ttvx51  (cost=0.57..22944880.81 rows=37045867 width=204)
              ->  Index Scan using ittvx63_ult_ric_migrazione on ttvx63_ult_ric fnl  (cost=0.57..0.68 rows=1 width=70)
                    Index Cond: (c_id_msg = ttvx51.c_id_msg)
                    Filter: (((c_tar)::text ~~* ANY ('{AB142%}'::text[])) AND ((c_tip_ric)::text = 'FNL'::text))

带explain (buffers, analyze)的结果：

Limit  (cost=1001.16..59951.63 rows=10 width=274) (actual time=453151.960..612564.881 rows=10 loops=1)
  Buffers: shared hit=291645856 read=1955824
  I/O Timings: read=1106382.770
  ->  Gather Merge  (cost=1001.16..48022052.31 rows=8146 width=274) (actual time=453151.958..612564.875 rows=10 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=291645856 read=1955824
        I/O Timings: read=1106382.770
        ->  Nested Loop  (cost=1.14..48020112.04 rows=3394 width=274) (actual time=388428.681..554079.550 rows=5 loops=3)
              Buffers: shared hit=291645856 read=1955824
              I/O Timings: read=1106382.770
              ->  Parallel Index Scan Backward using ittvx51_msg_81_migrazione2 on ttvx51_msg_81 ttvx51  (cost=0.57..22945552.81 rows=37045867 width=204) (actual time=0.057..49202.373 rows=16243236 loops=3)
                    Buffers: shared hit=48645632 read=645078
                    I/O Timings: read=91099.170
              ->  Index Scan using ittvx63_ult_ric_migrazione on ttvx63_ult_ric fnl  (cost=0.57..0.68 rows=1 width=70) (actual time=0.030..0.030 rows=0 loops=48729709)
                    Index Cond: (c_id_msg = ttvx51.c_id_msg)
                    Filter: (((c_tar)::text ~~* ANY ('{AB142%}'::text[])) AND ((c_tip_ric)::text = 'FNL'::text))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=243000224 read=1310746
                    I/O Timings: read=1015283.600
Planning:
  Buffers: shared hit=490
Planning Time: 0.595 ms
Execution Time: 612564.974 ms

2. 无ORDER BY、5字符前缀LIKE的查询计划 #

Limit  (cost=431.81..565.35 rows=10 width=274)
  ->  Nested Loop  (cost=431.81..109212.18 rows=8146 width=274)
        ->  Bitmap Heap Scan on ttvx63_ult_ric fnl  (cost=431.24..34898.28 rows=8687 width=70)
              Recheck Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
              Filter: ((c_tip_ric)::text = 'FNL'::text)
              ->  Bitmap Index Scan on ittvx63_ult_ric_3  (cost=0.00..429.07 rows=9153 width=0)
                    Index Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
        ->  Index Scan using ttvx51_msg_81_migrazione_pkey_migrazione on ttvx51_msg_81 ttvx51  (cost=0.57..8.55 rows=1 width=204)
              Index Cond: (c_id_msg = fnl.c_id_msg)

带explain (buffers, analyze)的结果：

Limit  (cost=435.81..569.35 rows=10 width=274) (actual time=253353.646..253361.341 rows=10 loops=1)
  Buffers: shared hit=830 read=300091
  I/O Timings: read=249287.405
  ->  Nested Loop  (cost=435.81..109216.18 rows=8146 width=274) (actual time=253353.644..253361.334 rows=10 loops=1)
        Buffers: shared hit=830 read=300091
        I/O Timings: read=249287.405
        ->  Bitmap Heap Scan on ttvx63_ult_ric fnl  (cost=435.24..34902.28 rows=8687 width=70) (actual time=253352.990..253354.078 rows=10 loops=1)
              Recheck Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
              Filter: ((c_tip_ric)::text = 'FNL'::text)
              Heap Blocks: exact=10
              Buffers: shared hit=789 read=300082
              I/O Timings: read=249280.389
              ->  Bitmap Index Scan on ittvx63_ult_ric_3  (cost=0.00..433.07 rows=9153 width=0) (actual time=253351.558..253351.558 rows=49 loops=1)
                    Index Cond: ((c_tar)::text ~~* ANY ('{AB142%}'::text[]))
                    Buffers: shared hit=786 read=300066
                    I/O Timings: read=249278.022
        ->  Index Scan using ttvx51_msg_81_migrazione_pkey_migrazione on ttvx51_msg_81 ttvx51  (cost=0.57..8.55 rows=1 width=204) (actual time=0.720..0.720 rows=1 loops=10)
              Index Cond: (c_id_msg = fnl.c_id_msg)
              Buffers: shared hit=41 read=9
              I/O Timings: read=7.016
Planning:
  Buffers: shared hit=1157 read=3
  I/O Timings: read=3.113
Planning Time: 5.534 ms
Execution Time: 253361.759 ms

3. 带ORDER BY、3字符前缀LIKE的查询计划 #

Limit  (cost=1001.16..59950.24 rows=10 width=274)
  ->  Gather Merge  (cost=1001.16..48020923.65 rows=8146 width=274)
        Workers Planned: 2
        ->  Nested Loop  (cost=1.14..48018983.37 rows=3394 width=274)
              ->  Parallel Index Scan Backward using ittvx51_msg_81_migrazione2 on ttvx51_msg_81 ttvx51  (cost=0.57..22944880.81 rows=37045867 width=204)
              ->  Index Scan using ittvx63_ult_ric_migrazione on ttvx63_ult_ric fnl  (cost=0.57..0.68 rows=1 width=70)
                    Index Cond: (c_id_msg = ttvx51.c_id_msg)
                    Filter: (((c_tar)::text ~~* ANY ('{AB142%}'::text[])) AND ((c_tip_ric)::text = 'FNL'::text))

优化方案与问题排查 #

核心问题分析 #

带ORDER BY时，PostgreSQL选择从ttvx51表按d_trn倒序扫描，再逐个关联fnl表并过滤c_tar条件。这种方式在匹配结果极少时，会扫描大量无关数据（实际扫描近5000万条ttvx51记录才找到10条符合条件的数据），导致I/O开销极大。

无ORDER BY时，计划先从fnl表通过c_tar索引找到匹配数据，再关联ttvx51，效率更高，但无法满足排序需求。

具体优化措施 #

1. 改写查询，强制先过滤后关联排序
   用子查询先获取符合条件的fnl记录ID，再关联ttvx51并排序，明确执行顺序：

   select ttvx51.*, fnl.*
   from (
       select c_id_msg, c_tar, c_tip_ric
       from tvxa.ttvx63_ult_ric
       where c_tip_ric = 'FNL' and c_tar ilike any ('{"AB142%"}')
   ) as fnl
   join tvxa.ttvx51_msg_81 as ttvx51 on ttvx51.c_id_msg = fnl.c_id_msg
   order by ttvx51.d_trn desc
   limit 10 offset 0;
   

   注：原查询的left join因where条件退化为inner join，直接改为inner join减少歧义。

2. 创建复合索引优化过滤+关联
   在fnl表创建(c_tip_ric, c_tar, c_id_msg)的复合B-tree索引，可直接通过索引获取符合条件的c_id_msg，无需回表：

   create index idx_fnl_tip_tar_id on tvxa.ttvx63_ult_ric (c_tip_ric, c_tar, c_id_msg);
   

   该索引对前缀LIKE场景（如ilike 'AB142%'）有高效匹配能力。

3. 更新统计信息
   执行analyze tvxa.ttvx63_ult_ric;更新表统计信息，让PostgreSQL更准确评估c_tar条件的匹配行数，避免选择低效执行计划。

4. 临时应急：禁用并行扫描
   若上述方案暂时无法生效，可执行set max_parallel_workers_per_gather = 0;禁用并行扫描，强制优化器选择更优路径，但此为临时方案，优先推荐前三种方法。

操作失误排查 #

• 检查fnl.c_tar的B-tree索引类型：确保基于text类型创建，且数据库enable_seqscan、enable_indexscan等参数未被错误关闭。
• 确认ilike必要性：若车牌为固定大小写，改用like可避免大小写转换开销，提升索引效率。
• 检查left join合理性：原查询中where fnl.c_tar ilike ...会过滤掉fnl为NULL的记录，等同于inner join，无需使用left join，避免优化器产生错误计划。

内容的提问来源于stack exchange，提问作者Mario Agati