PostgreSQL 如何让模糊匹配使用索引

在 PostgreSQL 中，使用 LIKE 和 ILIKE 进行模糊匹配时，如果不采取特殊的措施，它们是不会使用索引的，而是会进行全表扫描，导致查询效率较低。但是，可以通过创建索引和使用特殊的匹配方式来优化模糊匹配查询的性能。

1. 创建索引

可以创建 B-tree 索引来优化 LIKE 和 ILIKE 查询的性能。例如，对于一个名为 name 的列，可以使用如下 SQL 语句创建一个 B-tree 索引：

CREATE INDEX idx_name ON tablename USING btree (name);

2. 使用“前缀匹配”

“前缀匹配”是指在查询时只匹配目标字符串的开头部分，这样可以大大减少匹配的次数。例如，如果要查询名字以“John”开头的用户，可以将查询条件改为：

SELECT * FROM tablename WHERE name LIKE 'John%';

这样，只有以“John”开头的字符串才会被匹配，不符合条件的字符串就不会被匹配，可以大大减少匹配的次数。

3. 使用 pg_trgm 扩展

pg_trgm 是 PostgreSQL 的一个扩展，它提供了一种基于 trigram 的模糊匹配算法，可以用来优化 LIKE 和 ILIKE 查询的性能。它将字符串拆分成三字母组合，并将其存储在索引中，这样在查询时只需要比较三字母组合的匹配程度，而不是整个字符串的匹配程度，可以大大提高查询效率。使用 pg_trgm 扩展，需要先在数据库中创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

然后可以使用如下语句创建一个 trigram 索引：

CREATE INDEX idx_name_trgm ON tablename USING gin (name gin_trgm_ops);

最后，在查询时需要使用 pg_trgm 提供的函数进行模糊匹配，例如：

SELECT * FROM tablename WHERE name % 'John';

其中，“%”表示使用 trigram 算法进行模糊匹配。