PostgreSQL中的ILIKE与TSEARCH对比

Question

我有一个查询，其中包含多个测试字段，例如：

SELECT * FROM some-table
  WHERE field1 ILIKE "%thing%"
     OR field2 ILIKE "%thing"
     OR field3 ILIKE "%thing";

这些列几乎都是varchar(50)或类似的类型。现在我理解为了提高性能，应该为搜索操作建立索引。我是否应该考虑完全替换ILIKE为TSEARCH？

- singingfish

Answer 1

全文搜索设置与“包含”查询不完全相同。它处理单词等，使您可以将 "cars" 与 "car" 匹配。

如果您确实想要一个快速的 ILIKE 查询，则没有标准数据库索引或 FTS 可以帮助您。幸运的是，pg_trgm 模块可以做到这一点。

• http://www.postgresql.org/docs/9.1/static/pgtrgm.html
• http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

- Richard Huxton

---

pg_trgm非常有用。对于Django用户：在your_text_field上建立索引时使用upper(your_text_field)，因为Django会发出upper(x) like upper(y)查询而不是ilike。如果字段没有使用upper进行索引，则该索引将不会在这些查询中使用。 - Risadinha

Answer 2

有一件非常重要的事情：无论如何，不会有B-Tree索引可以改善这种搜索：

where field ilike '%SOMETHING%'

我想说的是，如果你执行：

create index idx_name on some_table(field);

您唯一能够改善的访问方式是where field like 'something%'（当您搜索以某个字面量开头的值时）。因此，在这种情况下，向field列添加常规索引不会带来任何好处。

如果您需要提高搜索响应时间，一定要考虑使用全文本搜索。

- Pablo Santa Cruz

---

你说得没错，没有B-Tree可以改善这个搜索，但是PostgreSQL可以使用其他索引类型来支持%foobar%条件（请参见Richard的回答）。 - user330315

Answer 3

补充其他人的观点。

首先，你不能基于字符串中间的值使用索引。索引通常是树搜索，而您无法知道搜索是否比仅扫描表更快，因此PostgreSQL将默认执行顺序扫描。只有匹配字符串的第一个部分的索引才会被使用。所以：

SELECT * FROM invoice
  WHERE invoice_number like 'INV-2012-435%'

可以使用索引，但like '%44354456%'不能。

一般情况下，在LedgerSMB中我们会根据不同的搜索需求来选择使用哪种方式。您可能会看到以下搜索方式：

select * from parts
  WHERE partnumber ilike ?  || '%'
    and plainto_tsquery(get_default_language(), ?) @@ description;

因此，它们非常不同。在最合适的地方使用每个选项。

- Chris Travers

---

我同意目前信息不足以确定是使用trigram还是tsearch更为合适，但是其中之一（或者可能是两者的结合）似乎是必要的。 - kgrittn