如何确认trigram索引是否被实际用于带双通配符的ILIKE查询？

嘿，很高兴看到你已经推进到trigram索引这一步啦！要确认它有没有真的被用上，其实有几个非常直接的方法，我给你一步步拆解清楚：

1. 用EXPLAIN查看预估查询计划 #

这是最基础也最常用的方式——直接在你的搜索查询前加上EXPLAIN关键字就行。比如你的查询是：

SELECT * FROM your_table WHERE concatenated_column ILIKE '%search_term%';

那你就执行：

EXPLAIN SELECT * FROM your_table WHERE concatenated_column ILIKE '%search_term%';

重点看输出结果里的关键词：

• 如果出现Index Scan using your_trigram_index on your_table或者Bitmap Index Scan on your_trigram_index，说明索引已经被用上了；
• 如果看到Seq Scan（顺序扫描），那就是PostgreSQL没选择用这个索引。

2. 用EXPLAIN ANALYZE验证真实执行情况 #

EXPLAIN只是给出预估的执行计划，而EXPLAIN ANALYZE会实际跑一遍你的查询，返回真实的执行路径和性能数据。执行命令：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE concatenated_column ILIKE '%search_term%';

除了看有没有索引扫描的条目，你还可以对比：

• 实际扫描的行数（是不是比全表扫描少很多）；
• 执行时间（和临时删除索引后跑的结果对比，看有没有明显提升）。
  这样能更直观地确认索引是否在发挥作用。

3. 先检查索引本身是否正确创建 #

有时候索引没被用上，根本原因是你建索引的方式不对！要确保你是基于pg_trgm扩展的正确算子创建的索引，比如：

-- GIN索引（适合高基数数据，查询更快）
CREATE INDEX idx_your_table_trgm ON your_table USING gin (concatenated_column gin_trgm_ops);

-- 或者GIST索引（占用空间更小，写入更快）
CREATE INDEX idx_your_table_trgm ON your_table USING gist (concatenated_column gist_trgm_ops);

如果你的索引没有指定gin_trgm_ops或者gist_trgm_ops，那这个trigram索引根本不会生效，自然不会被查询用到。

4. 其他常见排查点 #

• 数据量太小：如果你的表只有几百行，PostgreSQL会觉得顺序扫描比走索引更快，这是正常的，试试用更大的数据集测试；
• 字段不匹配：确保查询条件里的字段和索引的字段完全一致（包括数据类型）；
• 统计信息过时：PostgreSQL依赖表的统计信息来选择执行计划，如果统计信息太久没更新，可能会做出错误判断。这时候跑一下ANALYZE your_table;更新统计信息，再重新查看执行计划。

举个实际的例子 #

假设我有个users表，建了名为idx_users_full_name_trgm的trigram索引，执行EXPLAIN后得到这样的输出，就明确说明索引在工作：

Bitmap Heap Scan on users (cost=4.21..12.45 rows=1 width=123)
Recheck Cond: (full_name ~~* '%john%'::text)
-> Bitmap Index Scan on idx_users_full_name_trgm (cost=0.00..4.21 rows=1 width=0)
Index Cond: (full_name ~~* '%john%'::text)

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