Postgres相似性查询未高效利用GIN索引，求优化方案

背景与问题 #

我的temp.entities表有约1200万行数据，包含text类型字段full_name和UUIDV7类型的id字段，已创建以下索引：

create index entities_full_name_gin_trgm_ops_index
  on temp.entities
  using gin(full_name gin_trgm_ops);

create index entities_id_index ON temp.entities (id);

我需要执行以下查询，为每条记录找到ID更小且full_name相似的另一条记录：

select lhs.id, lhs.full_name, rhs.id, rhs.full_name
from temp.entities as lhs
left join lateral (
  select id, full_name
  from temp.entities
  where full_name % lhs.full_name and id < lhs.id
  limit 1
) as rhs on true
order by lhs.id desc
limit 10;

遇到的性能问题 #

1. 原始查询未使用GIN索引，而是走entities_id_index的B树索引，执行时间超289秒，执行计划显示每次子查询都要扫描大量行后过滤相似性条件。
2. 按照建议在子查询中添加order by full_name后，查询开始使用GIN索引，但limit 10仍需约6秒，扩大到limit 1000或limit 10000时性能会进一步下降。

两次查询执行计划 #

第一次查询执行计划（未添加order by） #

Limit  (cost=1.12..66.24 rows=10 width=68) (actual time=22616.601..289628.893 rows=10 loops=1)
   Output: lhs.id, lhs.full_name, entities.id, entities.full_name
   Buffers: shared hit=64213445 read=2646221 written=1696
   ->  Nested Loop Left Join  (cost=1.12..86936338.39 rows=13350926 width=68) (actual time=22616.600..289628.886 rows=10 loops=1)
         Output: lhs.id, lhs.full_name, entities.id, entities.full_name
         Buffers: shared hit=64213445 read=2646221 written=1696
         ->  Index Scan Backward using entities_id_index on temp.entities lhs  (cost=0.56..719298.01 rows=13350926 width=34) (actual time=0.035..0.055 rows=10 loops=1)
               Output: lhs.id, lhs.first_name, lhs.middle_name, lhs.last_name, lhs.name_suffix, lhs.full_name, lhs.address_house_number, lhs.address_street_direction, lhs.address_street_name, lhs.address_street_suffix, lhs.address_street_post_direction, lhs.address_unit_prefix, lhs.address_unit_value, lhs.address_city, lhs.address_state, lhs.address_zip, lhs.address_zip_4, lhs.address_legacy, lhs.address_normalized, lhs.address, lhs.status, lhs.total_records, lhs.total_buy_records, lhs.total_sell_records, lhs."fix_and_flip?", lhs."buy_and_hold?", lhs."wholesaler?", lhs.last_year, lhs.last_year_buy_records, lhs.last_year_buy_transfer_amount, lhs.last_year_sell_records, lhs.last_year_sell_transfer_amount, lhs.current_year, lhs.current_year_buy_records, lhs.current_year_buy_transfer_amount, lhs.current_year_sell_records, lhs.current_year_sell_transfer_amount, lhs.score, lhs.score_version, lhs.inserted_at, lhs.updated_at
               Buffers: shared hit=2 read=8
         ->  Limit  (cost=0.56..6.45 rows=1 width=34) (actual time=28962.880..28962.880 rows=1 loops=10)
               Output: entities.id, entities.full_name
               Buffers: shared hit=64213443 read=2646213 written=1696
               ->  Index Scan using entities_id_index on temp.entities  (cost=0.56..262023.42 rows=44503 width=34) (actual time=28962.878..28962.878 rows=1 loops=10)
                     Output: entities.id, entities.full_name
                     Index Cond: (entities.id < lhs.id)
                     Filter: ((entities.full_name)::text % (lhs.full_name)::text)
                     Rows Removed by Filter: 8771697
                     Buffers: shared hit=64213443 read=2646213 written=1696
 Settings: temp_buffers = '64MB', work_mem = '128MB', max_parallel_workers_per_gather = '8', enable_seqscan = 'off'
 Planning:
   Buffers: shared hit=16 read=2
 Planning Time: 0.364 ms
 Execution Time: 289628.935 ms
(23 rows)

第二次查询执行计划（添加order by full_name后） #

Limit  (cost=224541.60..2469952.55 rows=10 width=68) (actual time=687.770..6063.586 rows=10 loops=1)
   Output: lhs.id, lhs.full_name, entities.id, entities.full_name
   Buffers: shared hit=479653 read=64097 written=295
   ->  Nested Loop Left Join  (cost=224541.60..2997831757508.70 rows=13350926 width=68) (actual time=687.769..6063.580 rows=10 loops=1)
         Output: lhs.id, lhs.full_name, entities.id, entities.full_name
         Buffers: shared hit=479653 read=64097 written=295
         ->  Index Scan Backward using entities_id_index on temp.entities lhs  (cost=0.56..719298.01 rows=13350926 width=34) (actual time=0.026..0.068 rows=10 loops=1)
               Output: lhs.id, lhs.first_name, lhs.middle_name, lhs.last_name, lhs.name_suffix, lhs.full_name, lhs.address_house_number, lhs.address_street_direction, lhs.address_street_name, lhs.address_street_suffix, lhs.address_street_post_direction, lhs.address_unit_prefix, lhs.address_unit_value, lhs.address_city, lhs.address_state, lhs.address_zip, lhs.address_zip_4, lhs.address_legacy, lhs.address_normalized, lhs.address, lhs.status, lhs.total_records, lhs.total_buy_records, lhs.total_sell_records, lhs."fix_and_flip?", lhs."buy_and_hold?", lhs."wholesaler?", lhs.last_year, lhs.last_year_buy_records, lhs.last_year_buy_transfer_amount, lhs.last_year_sell_records, lhs.last_year_sell_transfer_amount, lhs.current_year, lhs.current_year_buy_records, lhs.current_year_buy_transfer_amount, lhs.current_year_sell_records, lhs.current_year_sell_transfer_amount, lhs.score, lhs.score_version, lhs.inserted_at, lhs.updated_at
               Buffers: shared hit=4 read=6
         ->  Limit  (cost=224541.04..224541.05 rows=1 width=34) (actual time=606.348..606.348 rows=1 loops=10)
               Output: entities.id, entities.full_name
               Buffers: shared hit=479649 read=64091 written=295
               ->  Sort  (cost=224541.04..224652.30 rows=44503 width=34) (actual time=606.346..606.346 rows=1 loops=10)
                     Output: entities.id, entities.full_name
                     Sort Key: entities.full_name
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=479649 read=64091 written=295
                     ->  Bitmap Heap Scan on temp.entities  (cost=102831.00..224318.53 rows=44503 width=34) (actual time=606.318..606.336 rows=2 loops=10)
                           Output: entities.id, entities.full_name
                           Recheck Cond: (((entities.full_name)::text % (lhs.full_name)::text) AND (entities.id < lhs.id))
                           Rows Removed by Index Recheck: 1
                           Heap Blocks: exact=30
                           Buffers: shared hit=479649 read=64091 written=295
                           ->  BitmapAnd  (cost=102831.00..102831.00 rows=44503 width=0) (actual time=606.297..606.297 rows=0 loops=10)
                                 Buffers: shared hit=479644 read=64066 written=295
                                 ->  Bitmap Index Scan on entities_full_name_gin_trgm_ops_index  (cost=0.00..882.62 rows=133509 width=0) (actual time=83.707..83.707 rows=5 loops=10)
                                       Index Cond: ((entities.full_name)::text % (lhs.full_name)::text)
                                       Buffers: shared hit=20153 read=11997 written=40
                                 ->  Bitmap Index Scan on entities_id_index  (cost=0.00..101925.88 rows=4450309 width=0) (actual time=522.579..522.579 rows=13350920 loops=10)
                                       Index Cond: (entities.id < lhs.id)
                                       Buffers: shared hit=459491 read=52069 written=255
 Settings: temp_buffers = '64MB', work_mem = '128MB', max_parallel_workers_per_gather = '8', enable_seqscan = 'off'
 Planning:
   Buffers: shared read=1
 Planning Time: 0.140 ms
 Execution Time: 6063.627 ms
(36 rows)

优化方案 #

1. 调整子查询排序逻辑，优先定位目标ID #

修改子查询，按id desc排序（直接找最大的小于当前ID的相似记录），避免不必要的排序开销：

select lhs.id, lhs.full_name, rhs.id, rhs.full_name
from temp.entities as lhs
left join lateral (
  select id, full_name
  from temp.entities
  where full_name % lhs.full_name 
    and id < lhs.id
  order by id desc  -- 优先返回符合条件的最大ID记录，找到即停止
  limit 1
) as rhs on true
order by lhs.id desc
limit 10;

此调整让PostgreSQL在找到相似记录后，直接筛选ID小于当前值的最大项，无需对所有相似记录排序。

2. 创建复合GIN索引（结合trigram与ID） #

利用btree_gin扩展，创建同时包含full_name trigram和id的复合GIN索引，避免BitmapAnd的高额开销：

-- 先安装扩展（若未安装）
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- 创建复合索引
CREATE INDEX entities_full_name_id_gin_idx 
ON temp.entities 
USING GIN (full_name gin_trgm_ops, id);

该索引可直接同时匹配相似性条件和ID范围，减少索引扫描次数。

3. 调高work_mem优化Bitmap操作 #

从执行计划看，ID的Bitmap扫描返回了1300多万行，当前work_mem=128MB可能不足以在内存中处理Bitmap，导致磁盘IO开销大。临时调高参数：

SET work_mem = '512MB';  -- 根据服务器内存调整，建议不超过总内存的1/8

此设置可让PostgreSQL在内存中完成Bitmap合并，大幅减少磁盘读写。

4. 预计算相似分组（非实时场景） #

若不需要实时查询，可预计算相似full_name的分组：

• 用pg_trgm.similarity函数计算相似度，将相似度高于阈值的记录归为一组
• 每组内按id排序，存储到临时表或新增分组字段
  查询时直接从分组中取ID更小的记录，避免重复计算相似性。

5. 强制查询计划走GIN索引引导 #

临时关闭Bitmap扫描，让PostgreSQL优先使用GIN索引结合ID过滤：

SET enable_bitmapscan = off;

执行查询后再恢复参数：

SET enable_bitmapscan = on;

此方法需测试验证是否能提升性能，不同数据分布效果可能不同。

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