如何让PostgreSQL表达式索引支持前置通配符的LIKE查询？

我使用PostgreSQL、EntityFramework与C#，创建了如下唯一索引：

CREATE UNIQUE INDEX "IX_MyObjects_MyColumn"
ON "Manufacturing"."MyObjects" USING btree
(LOWER("MyColumn") COLLATE pg_catalog."default" varchar_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;

该索引对后缀通配符的LIKE查询（如lower("MyColumn") LIKE lower('tj%')）有效，执行计划显示使用索引扫描：

EXPLAIN SELECT * FROM "Manufacturing"."MyObjects" WHERE lower("MyColumn") LIKE lower('tj%');

Bitmap Heap Scan on "MyObjects"  (cost=8.24..10.39 rows=10 width=1755)
  Filter: (lower(("MyColumn")::text) ~~ 'tj%'::text)
  ->  Bitmap Index Scan on "IX_MyObjects_MyColumn"  (cost=0.00..8.23 rows=10 width=0)
        Index Cond: ((lower(("MyColumn")::text) ~>=~ 'tj'::text) AND (lower(("MyColumn")::text) ~<=~ 'tk'::text))

但含前置通配符的LIKE查询（如lower("MyColumn") LIKE lower('%tj%')）会导致索引失效，执行计划为全表扫描：

EXPLAIN SELECT * FROM "Manufacturing"."MyObjects" WHERE lower("MyColumn") LIKE lower('%tj%');

Seq Scan on "MyObjects"  (cost=10000000000.00..10000000002.15 rows=3 width=1755)
  Filter: (lower(("MyColumn")::text) ~~ '%tj%'::text)

需求是让索引支持含前置通配符的查询，且不能限制仅前缀匹配。

1. 使用pg_trgm扩展创建GIN/GIST索引 #

PostgreSQL的pg_trgm扩展基于三元组（trigram）实现文本匹配，能为任意位置通配符的LIKE查询提供高效索引支持，这是处理%xxx%这类查询最直接的方案。

操作步骤：

• 先启用pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

• 创建GIN或GIST索引（GIN查询速度更快，GIST占用空间更小，根据业务场景选择）：

-- GIN索引（推荐查询频繁的场景）
CREATE INDEX "IX_MyObjects_MyColumn_Trgm" ON "Manufacturing"."MyObjects"
USING GIN (LOWER("MyColumn") gin_trgm_ops);

-- 或GIST索引（适合存储资源有限的场景）
CREATE INDEX "IX_MyObjects_MyColumn_Trgm" ON "Manufacturing"."MyObjects"
USING GIST (LOWER("MyColumn") gist_trgm_ops);

创建完成后，执行lower("MyColumn") LIKE lower('%tj%')查询，执行计划会自动使用该索引扫描，避免全表扫描。

2. 使用全文检索（Full-Text Search） #

如果查询需求是关键词匹配而非任意子串，全文检索是更高效的替代方案，适合自然语言类的搜索场景。

操作步骤：

• 创建全文检索索引：

CREATE INDEX "IX_MyObjects_MyColumn_FTS" ON "Manufacturing"."MyObjects"
USING GIN (to_tsvector('english', LOWER("MyColumn")));

• 使用全文检索语法查询（替代LIKE）：

SELECT * FROM "Manufacturing"."MyObjects"
WHERE to_tsvector('english', LOWER("MyColumn")) @@ to_tsquery('english', 'tj');

注意：全文检索基于词单元匹配，不适合精确的任意子串查找，仅推荐用于关键词搜索场景。

3. EntityFramework适配（C#） #

• 对于pg_trgm的LIKE查询，直接使用LINQ即可，EF会自动转换为对应SQL：

var keyword = "tj";
var results = dbContext.MyObjects
    .Where(o => EF.Functions.Like(o.MyColumn.ToLower(), $"%{keyword.ToLower()}%"))
    .ToList();

• 对于全文检索，可使用FromSqlRaw执行原生SQL：

var keyword = "tj";
var results = dbContext.MyObjects
    .FromSqlRaw(@"SELECT * FROM ""Manufacturing"".""MyObjects""
                  WHERE to_tsvector('english', LOWER(""MyColumn"")) @@ to_tsquery('english', {0})", keyword)
    .ToList();

B-tree索引的结构特性决定了它仅支持前缀匹配（xxx%），无法处理中间或后缀通配符的查询，因此无法通过修改原有B-tree索引实现需求。pg_trgm索引会增加写入操作的开销（插入/更新数据时需维护索引），需根据业务读写比例权衡选择。

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