PostgreSQL 9.4 : index not working in a pattern search

Question

我有一个名为“doctors”的表和一个名为“fullname”的字段，用于存储带有重音符号的姓名。我需要做的是“不区分重音 + 不区分大小写”搜索，例如:

SELECT * 
FROM doctors
WHERE unaccent_t(fullname) ~* 'unaccented_and_lowercase_string';

要搜索的值将以 unaccented+lowercase 形式出现，unaccent_t 是一个函数，定义为:

CREATE FUNCTION unaccent_t(text, lowercase boolean DEFAULT false)
RETURNS text AS
$BODY$
SELECT CASE
  WHEN $2 THEN unaccent('unaccent', lower(trim($1)))
  ELSE unaccent('unaccent', trim($1))
END;
$BODY$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

(我已经安装了“unaccent”扩展)。

因此，我继续为“全名”字段创建索引:

CREATE INDEX doctors_fullname ON doctors (unaccent_t(fullname) text_pattern_ops);

(我也尝试过使用 varchar_pattern_ops 并且根本没有指定操作)

在医生表中，我有大约 15K 行。

查询有效，我得到了预期的结果，但是当我将 explain analyze 添加到查询中时，我没有看到使用了索引:

Seq Scan on doctors  (cost=0.00..4201.76 rows=5 width=395) (actual time=0.282..182.025 rows=15000 loops=1)
  Filter: (unaccent_t((fullname)::text, false) ~* 'garcia'::text)
  Rows Removed by Filter: 1
Planning time: 0.207 ms
Execution time: 183.387 ms

我也尝试从 unaccent_t 中删除可选参数，但我得到了相同的结果。

在这种情况下，我应该如何定义索引以便在上述查询中使用它？

Answer 1

Btree 索引仅在模式保持锚定时才可用于加速操作。

从 PostgreSQL 9.3 开始，您可以使用 GIN 或 GiST 索引以及 pg_trgm contrib 模块提供的运算符类来加速通用正则表达式搜索。

您可以在 http://www.postgresql.org/docs/9.4/static/pgtrgm.html#AEN163078 的 PostgreSQL 手册中阅读更多相关信息