Postgresql 9.4:在模式搜索中不工作的索引

Question

我有一个名为“医生”的表和一个名为“全名”的字段，该字段将存储带有口音的名称。我需要做的是进行“口音不敏感+不区分大小写”的搜索，类似于：

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;

(我已经安装了“取消口音”分机)。

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

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控制模块提供的操作符类来加速通用正则表达式搜索。

你可以在PostgreSQL手册http://www.postgresql.org/docs/9.4/static/pgtrgm.html#AEN163078上读到更多关于它的内容。