12M+客户表：用unaccent与pg_trgm多列索引实现脏数据模糊查询

老哥，针对你这1200万+条客户数据的非精确匹配需求，用unaccent+pg_trgm的方案绝对是PostgreSQL里处理脏数据查询的最优解之一，尤其是用f_unaccent代替原生unaccent来实现索引层面的去重音处理，能把查询性能拉满，我给你把完整的实现步骤和注意事项理清楚：

原生的unaccent()函数因为依赖词典配置，被PostgreSQL标记为stable（结果可能受外部环境影响），无法直接用于GiST/GIN索引。而我们包装出来的f_unaccent()会被标记为immutable（结果仅由输入参数决定），这样就能在索引中直接应用去重音逻辑，让模糊查询能命中索引，避免全表扫描。

1. 先安装必备扩展 #

首先确保unaccent和pg_trgm这两个扩展已经安装：

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建f_unaccent包装函数 #

用这个函数来包装原生的unaccent，让它满足索引的immutable要求：

CREATE OR REPLACE FUNCTION f_unaccent(text)
RETURNS text AS
$func$
SELECT unaccent('unaccent', $1); -- 这里用默认的unaccent词典，你也可以替换成自定义词典
$func$ LANGUAGE sql IMMUTABLE;

3. 创建针对性的索引 #

针对你需要查询的first_name、last_name、birth_place字段，分别创建支持trgm模糊匹配的GiST索引（也可以用GIN，后面会说区别）：

单字段索引 #

-- first_name的去重音+模糊匹配索引
CREATE INDEX customer_first_name_trgm_idx ON customer 
USING gist (f_unaccent(coalesce(first_name, '')) gist_trgm_ops);

-- last_name的去重音+模糊匹配索引
CREATE INDEX customer_last_name_trgm_idx ON customer 
USING gist (f_unaccent(coalesce(last_name, '')) gist_trgm_ops);

-- birth_place的去重音+模糊匹配索引
CREATE INDEX customer_birth_place_trgm_idx ON customer 
USING gist (f_unaccent(coalesce(birth_place, '')) gist_trgm_ops);

这里用coalesce是为了把NULL值转成空字符串，避免索引里的NULL条目干扰匹配逻辑。

组合查询索引（如果需要同时查多个字段） #

如果你的业务经常需要同时匹配姓名和出生地，可以创建组合索引，进一步提升多字段查询的效率：

CREATE INDEX customer_name_birthplace_trgm_idx ON customer 
USING gist (
  f_unaccent(coalesce(first_name, '')),
  f_unaccent(coalesce(last_name, '')),
  f_unaccent(coalesce(birth_place, '')) gist_trgm_ops
);

4. 实际查询示例 #

查询的时候要和索引逻辑对齐，用f_unaccent包裹字段和输入值，这样才能命中索引：

SELECT * FROM customer
WHERE f_unaccent(coalesce(first_name, '')) % f_unaccent('João') -- %是pg_trgm的模糊匹配运算符
AND f_unaccent(coalesce(last_name, '')) % f_unaccent('Silva')
AND f_unaccent(coalesce(birth_place, '')) % f_unaccent('São Paulo');

• 针对12M+的大表，创建索引的时候建议用CONCURRENTLY，避免长时间锁表影响业务：

  CREATE INDEX CONCURRENTLY customer_first_name_trgm_idx ON customer 
  USING gist (f_unaccent(coalesce(first_name, '')) gist_trgm_ops);
  

• 如果对匹配精度有要求，可以调整pg_trgm.similarity_threshold参数，或者直接在查询里用similarity()函数指定阈值：

  SELECT * FROM customer
  WHERE similarity(f_unaccent(coalesce(first_name, '')), f_unaccent('João')) > 0.7;
  

• GiST和GIN索引的选择：GiST索引构建更快、占用空间更小，适合写多读少的场景；GIN索引查询速度更快，适合读多写少的场景，你可以根据业务情况切换。
• 定期维护索引：如果表数据更新频繁，建议定期用REINDEX INDEX ...来优化索引性能，避免索引碎片化。

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