如何优化基于pg_trgm.similarity的姓名相似性SQL查询？

耗时是否正常？ #

2万条记录耗时15分钟完全不正常。当前查询是全表自连接，对每一对记录计算相似度，属于O(n²)复杂度的操作，且普通B-tree索引无法支持similarity函数的查询逻辑，导致执行效率极低。

优化方法 #

1. 创建pg_trgm专用索引 #

similarity函数依赖PostgreSQL的pg_trgm扩展，普通索引无法适配，需创建GIN或GiST类型的 trigram 索引：

-- 先确保扩展已安装
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 创建GIN索引（适合高基数数据集，相似度查询效率更优）
CREATE INDEX idx_debtors_full_name_trgm ON debtors USING GIN (full_name gin_trgm_ops);
-- 若表更新频繁，可改用GiST索引（写入性能更好）
-- CREATE INDEX idx_debtors_full_name_trgm ON debtors USING GiST (full_name gist_trgm_ops);

2. 避免重复匹配 #

将WHERE a.id != b.id改为WHERE a.id < b.id，每一对相似客户只会被匹配一次，直接减少一半的计算量和结果集大小：

SELECT      a.full_name, a.id, b.id
FROM        debtors as a
INNER JOIN  debtors as b on similarity(a.full_name, b.full_name) >= 0.6
WHERE       a.id < b.id
ORDER BY    a.full_name, a.id

3. 标准化姓名字段 #

提前对full_name做标准化处理（如去除空格、特殊符号，统一大小写），减少无效相似度计算，可新增标准化字段后再建索引：

ALTER TABLE debtors ADD COLUMN full_name_normalized TEXT;
UPDATE debtors SET full_name_normalized = lower(regexp_replace(full_name, '[^a-zA-Z0-9\u4e00-\u9fa5]', '', 'g'));
CREATE INDEX idx_debtors_normalized_trgm ON debtors USING GIN (full_name_normalized gin_trgm_ops);

后续基于标准化字段做相似度匹配，能同时提升计算准确性和效率。

4. 调整相似度阈值 #

若业务允许，适当提高阈值（如从0.6调至0.7），可显著减少需匹配的记录数量，加快查询速度。

优化结果反馈 #

已通过上述优化将查询耗时缩短至1分钟。

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