PostgreSQL pg_trgm批量相似查询未使用索引问题求助

问题背景 #

拥有一张51,436,173行的people表，结构如下：

CREATE TABLE people(
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

需要对一批名称执行模糊相似匹配，已为name字段创建GiST索引：

CREATE INDEX trgm_idx ON public.people USING GIST (name public.gist_trgm_ops);

初始查询使用similarity()函数做JOIN条件，触发全表顺序扫描，速度极慢：

EXPLAIN (ANALYZE, VERBOSE)
SELECT id, people.name, keys.name, similarity(people.name, keys.name)
FROM people JOIN (VALUES ('alice'), ('bob'), ('charlie')) keys(name)
ON similarity(people.name, keys.name) >= 0.75;

索引未生效的核心原因 #

1. PostgreSQL优化器无法将similarity(a, b) >= 阈值的函数调用，自动映射为pg_trgm提供的%相似性操作符，而只有%操作符能触发trgm索引的使用。
2. 即使使用%操作符，若未设置对应的pg_trgm.similarity_threshold参数，操作符会使用默认阈值（0.3），与业务需求的0.75不匹配，导致索引无法按预期过滤数据。

优化后的查询实现 #

通过以下调整，成功让查询利用GiST索引：

BEGIN;
-- 临时禁用顺序扫描（仅用于验证，生产环境可根据优化器判断调整）
SET LOCAL enable_seqscan = off;
-- 设置会话级相似性阈值，与业务需求一致
SET LOCAL pg_trgm.similarity_threshold = 0.75;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, people.name, keys.name, similarity(keys.name, people.name)
FROM people
    JOIN (VALUES ('alice'), ('bob'), ('charlie'), ('dani')) keys(name)
      ON people.name % keys.name; -- 使用%操作符替代函数比较
COMMIT;

执行计划解析（中文翻译） #

+--------------------------------------------------------------------------------------------------------------------------------------+
|查询计划                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------+
|嵌套循环  (预估成本=4758.47..3668792.68 行数=2058049 宽度=67) (实际时间=2419.249..32730.893 行数=75 循环次数=1)                       |
|  缓冲区: 共享命中=13120 读取=144894                                                                                                   |
|  ->  值扫描 on "*VALUES*"  (预估成本=0.00..0.05 行数=4 宽度=32) (实际时间=0.461..0.745 行数=4 循环次数=1)                              |
|  ->  位图堆扫描 on people  (预估成本=4758.47..910766.76 行数=514512 宽度=31) (实际时间=5615.752..8182.340 行数=19 循环次数=4)         |
|        重检查条件: (name % "*VALUES*".column1)                                                                                       |
|        索引重检查过滤行数: 1122368                                                                                                  |
|        堆块: 精确匹配=102969 模糊匹配=33951                                                                                           |
|        缓冲区: 共享命中=13120 读取=144894                                                                                           |
|        ->  位图索引扫描 on trgm_idx2  (预估成本=0.00..4629.84 行数=514512 宽度=0) (实际时间=710.005..710.007 行数=37618 循环次数=4)|
|              索引条件: (name % "*VALUES*".column1)                                                                                   |
|              缓冲区: 共享命中=12793 读取=8286                                                                                       |
|规划时间:                                                                                                                             |
|  缓冲区: 共享读取=1                                                                                                                  |
|规划耗时: 17.039 毫秒                                                                                                                |
|JIT编译:                                                                                                                              |
|  函数数: 5                                                                                                                          |
|  选项: 内联=true, 优化=true, 表达式=true, 变形=true                                                                                 |
|  耗时: 生成38.873毫秒, 内联75.855毫秒, 优化163.283毫秒, 生成代码156.729毫秒, 总计434.740毫秒                                        |
|执行耗时: 32787.630 毫秒                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------+

关键要点：

• 已触发位图索引扫描 on trgm_idx2，说明索引成功被利用
• 针对每个关键词（4次循环），索引扫描返回约3.7万条候选数据，最终过滤出19条匹配结果
• 存在模糊匹配=33951堆块，说明当前work_mem不足，导致位图无法存储精确行号，可适当调大work_mem减少重检查开销

额外优化建议 #

• GIN vs GiST索引选择：GIN索引在高选择性场景下（如相似阈值高，匹配结果少）查询速度更快，但占用存储空间更大；GiST索引占用空间小，写入性能更好。可根据业务场景测试后选择。
• 调整work_mem参数：临时或全局调大work_mem（如SET work_mem = '64MB'），减少位图堆扫描的lossy块数量，降低索引重检查的开销。
• 避免强制禁用顺序扫描：生产环境建议移除enable_seqscan = off，让优化器根据数据分布和统计信息自动选择最优路径。若优化器仍选择全表扫描，可更新统计信息（ANALYZE people;）或调整random_page_cost参数引导优化器选择索引扫描。
• 批量关键词优化：若待匹配关键词数量极大，可将关键词存入临时表并创建trgm索引，使用JOIN ... ON people.name % temp.name的方式，进一步提升匹配效率。

内容的提问来源于stack exchange，提问作者Carl Mastrangelo