如何为含unaccent()与ILIKE的PostgreSQL查询创建优化索引？

可以创建索引来优化这类查询，核心是把unaccent(name)的预处理结果和trigram索引结合，具体操作如下：

1. 先安装必要扩展 #

确保unaccent和pg_trgm扩展已安装：

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

2. 创建复合函数索引 #

因为查询里用到了unaccent(name)，直接对原字段建索引无法匹配，需要基于unaccent(name)的结果，结合company_id创建trigram索引：

选项一：GIN索引（适合数据量大、查询频繁的场景） #

CREATE INDEX idx_company_unaccent_name_trgm ON your_table_name
USING GIN (company_id, unaccent(name) gin_trgm_ops);

选项二：GIST索引（写入性能更优，适合写多读少的场景） #

CREATE INDEX idx_company_unaccent_name_trgm ON your_table_name
USING GIST (company_id, unaccent(name) gist_trgm_ops);

3. 查询语句的适配建议 #

为了确保索引命中，要保证查询里的表达式和索引定义完全一致。另外，unaccent已经处理了变音字符，若无需额外区分大小写，可把ILIKE换成LIKE，效果一致：

...
WHERE company_id = 2
AND unaccent(name) ILIKE unaccent('%value%'); -- 注意模糊匹配的通配符位置

关键说明 #

• 复合索引同时包含company_id和unaccent(name)，会先按company_id过滤数据，再做trigram匹配，效率更高。
• 如果name字段短字符串或重复值较多，trigram索引的优化效果会受限，可根据实际数据情况调整方案。

内容的提问来源于stack exchange，提问作者Max Bündchen