如何在PostgreSQL中创建大小写不敏感索引以优化EF查询性能？

问题背景 #

现有EF Core查询代码如下，需要改为大小写不敏感：

var list = _context.MyObjects.Where(x => !x.IsDeleted && (
                                    x.ABC.Contains(searchFor)
                                 || x.DEF.Contains(searchFor)
                                 || x.GHI.Contains(searchFor)))
                 .Select(x => new MyObject { ... });

尝试过StringComparison.CurrentCultureIgnoreCase、客户端ToLower()、citext、自定义排序规则，目前用EF.Functions.ILike实现但担心性能，已创建基于lower(列)的索引但未被使用，针对三个疑问解答如下：

1. 创建lower(列)索引后，查询时需要对列用lower()吗？ #

必须要。PostgreSQL的函数索引是基于lower(列)的计算结果创建的，只有当查询语句中同样对列调用lower()函数时，查询优化器才能识别到可以匹配该索引。举个例子：

• 正确的查询写法（EF Core）：

  var lowerSearch = searchFor.ToLower();
  var list = _context.MyObjects.Where(x => !x.IsDeleted && (
      EF.Functions.Lower(x.ABC).Contains(lowerSearch)
   || EF.Functions.Lower(x.DEF).Contains(lowerSearch)
   || EF.Functions.Lower(x.GHI).Contains(lowerSearch)))
  .Select(x => new MyObject { ... });
  

  这样生成的SQL会是lower("ABC") LIKE '%xxx%'，才能触发对应的函数索引。

2. 如何正确配置索引让EF查询高效执行？ #

注意：普通B树索引只支持前缀匹配（如'xxx%'），如果是包含式模糊查询（如'%xxx%'），需要用pg_trgm扩展的GIN/GIST索引，步骤如下：

步骤1：安装pg_trgm扩展（支持子串匹配索引）

在pgAdmin或psql中执行：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

步骤2：为查询列创建trigram函数索引

针对每个需要大小写不敏感模糊查询的列，创建基于lower(列)的GIN索引（GIN比GIST查询速度更快，适合读多写少的场景）：

-- 为ABC列创建索引
CREATE INDEX idx_myobjects_lower_abc_trgm ON "Manufacturing"."MyObjects" 
USING GIN (lower("ABC") gin_trgm_ops);

-- 为DEF列创建索引
CREATE INDEX idx_myobjects_lower_def_trgm ON "Manufacturing"."MyObjects" 
USING GIN (lower("DEF") gin_trgm_ops);

-- 为GHI列创建索引
CREATE INDEX idx_myobjects_lower_ghi_trgm ON "Manufacturing"."MyObjects" 
USING GIN (lower("GHI") gin_trgm_ops);

步骤3：调整EF Core查询语句

确保查询中同时对列和搜索词做小写转换，并且使用EF的内置函数让转换逻辑在数据库端执行（避免客户端转换后传输大量数据）：

var lowerSearchTerm = searchFor.ToLowerInvariant(); // 用ToLowerInvariant避免文化差异问题
var result = _context.MyObjects
    .Where(x => !x.IsDeleted && (
        EF.Functions.Lower(x.ABC).Contains(lowerSearchTerm)
     || EF.Functions.Lower(x.DEF).Contains(lowerSearchTerm)
     || EF.Functions.Lower(x.GHI).Contains(lowerSearchTerm)
    ))
    .Select(x => new MyObject { ... })
    .ToList();

步骤4：验证索引是否生效

用EXPLAIN ANALYZE执行EF生成的SQL语句，查看输出中是否出现Index Scan using idx_myobjects_lower_def_trgm on "MyObjects"类似的内容，确认索引被使用。如果未生效，可能是数据量过小（PostgreSQL会优先全表扫描）或搜索词过短（如1-2个字符），可以增加数据量测试。

3. 该索引方案对比EF.Functions.ILike的性能差异？ #

• 无索引场景：两者性能几乎一致，都是全表扫描，因为ILike本质上是PostgreSQL内置的大小写不敏感匹配，和lower(列) LIKE lower(搜索词)的逻辑等价。
• 有trigram索引场景：两者性能差距极小，因为ILike '%xxx%'和lower(列) LIKE '%xxx%'在有trigram索引时，都会触发索引扫描。区别仅在于写法：ILike不需要手动转换搜索词的大小写，写法更简洁；而lower()+Contains需要显式处理大小写，但逻辑更直观。
• 注意：如果用的是普通B树索引，那么只有前缀匹配（如'xxx%'）能用到索引，包含式匹配（'%xxx%'）两者都无法触发索引，性能一样差。所以如果是做包含式模糊查询，必须用trigram索引。

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