Postgresql 9.4：在模式搜索中索引无效

Question

我有一个名为"doctors"的表，里面有一个名为"fullname"的字段，用于存储带有重音符号的姓名。

我需要进行“忽略重音符号+大小写不敏感”的搜索，例如：

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

需要搜索的值将以非重音+小写形式出现，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”扩展。

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

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中删除可选参数，但结果相同。

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

- Roland Pish

---

这种技术可以使用 = 或左锚定的 LIKE，但 ~* 是用于匹配正则表达式的运算符。 - Daniel Vérité

所以只需尝试 SELECT * WHERE name like '%garcia'，这应该使用索引。但是这个不会：SELECT * WHERE name like 'garcia%'。 - Juan Carlos Oropeza

Answer 1

Btree索引只有在模式左对齐时才能用于加速操作。

自PostgreSQL 9.3以来，您可以使用pg_trgm contrib模块提供的操作符类，通过GIN或GiST索引加速通用正则表达式搜索。

您可以在PostgreSQL手册上阅读更多信息：http://www.postgresql.org/docs/9.4/static/pgtrgm.html#AEN163078。

- mnencia

---

谢谢，听起来不错。我将为这种类型的搜索创建一个gin / gist索引。干杯。 - Roland Pish

尝试使用 _text_ops 创建 gin 索引时，提示该运算符不接受文本数据。如果不指定任何选项，则会告诉我没有默认的运算符类。指定 gin_trgm_ops 可以创建索引并且可以正常工作，但是我不会在此字段中执行“三元组”全文搜索。是否有其他运算符类可用于此 gin 索引？或者即使我不执行任何全文搜索，使用 gin_trgm_ops 也可以吗？ - Roland Pish

此外，现在的解释分析告诉我索引正在被使用，但是如果不使用索引，执行时间基本相同。就像索引没有改善查询的执行时间一样。可能出了什么问题？ - Roland Pish

GIN或GIST_trgm_ops索引可用于加速任何regexp或LIKE搜索。如果执行时间与没有索引的时间相似，则可能是因为您的数据集很小或者您选择了大部分数据。 - mnencia

mnencia，这非常有道理。我的测试数据符合你所提到的情况。好消息是现在我知道索引正在被使用。因此，在我的查询场景中，这个gin索引将会起作用。再次感谢。 - Roland Pish