PostgreSQL中pgtrgm创建的GIN索引为何未被匹配？

你的查询没用到目标索引，核心原因是索引定义的表达式和查询的过滤条件完全不匹配，具体分析如下：

1. 索引与查询条件的表达式不匹配 #

你创建的索引是基于拼接后的小写全名：

CREATE INDEX index_users_on_username_trigram
ON users
USING gin ((LOWER(firstname) || LOWER(lastname)) gin_trgm_ops);

而查询的过滤条件是针对单独的firstname字段做trigram匹配：

WHERE u.firstname % 'Name'

这两个表达式没有任何关联，PostgreSQL无法通过这个拼接索引定位符合firstname % 'Name'的行，自然不会使用它。

2. 大小写匹配的额外问题 #

pgtrgm的%操作符默认区分大小写，你查询里用的是'Name'，而索引里是转成小写的拼接字段——即便你想基于全名查询，也需要把查询条件改成和索引一致的小写拼接形式，才有可能命中索引。

3. 执行计划的逻辑说明 #

当前执行计划是先扫描cars_damage表，再通过users的主键索引关联用户数据，最后过滤firstname的条件。这种方式在数据量极小（执行计划预估rows=1）时，PostgreSQL会认为比使用trigram索引更高效，但核心问题还是表达式不匹配导致索引无法被选用。

解决办法 #

根据你的实际需求选择对应的方案：

方案一：针对firstname单独建trigram索引 #

如果你的查询就是要匹配firstname的模糊内容，创建专门的索引：

CREATE INDEX index_users_on_firstname_trigram
ON users
USING gin (LOWER(firstname) gin_trgm_ops);

同时修改查询条件为小写匹配（保证和索引表达式一致）：

EXPLAIN 
SELECT * FROM 
cars_damage
LEFT JOIN users u
ON u.id = cars_damage.user_id
WHERE LOWER(u.firstname) % LOWER('Name');

方案二：修改查询条件匹配现有索引 #

如果你的实际需求是匹配全名（firstname+lastname）的模糊内容，调整查询条件和索引表达式对齐：

EXPLAIN 
SELECT * FROM 
cars_damage
LEFT JOIN users u
ON u.id = cars_damage.user_id
WHERE (LOWER(u.firstname) || LOWER(u.lastname)) % LOWER('Name');

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