我有一个查询,其中包含多个测试字段,例如:
SELECT * FROM some-table
WHERE field1 ILIKE "%thing%"
OR field2 ILIKE "%thing"
OR field3 ILIKE "%thing";
这些列几乎都是varchar(50)或类似的类型。现在我理解为了提高性能,应该为搜索操作建立索引。我是否应该考虑完全替换ILIKE为TSEARCH?
我有一个查询,其中包含多个测试字段,例如:
SELECT * FROM some-table
WHERE field1 ILIKE "%thing%"
OR field2 ILIKE "%thing"
OR field3 ILIKE "%thing";
这些列几乎都是varchar(50)或类似的类型。现在我理解为了提高性能,应该为搜索操作建立索引。我是否应该考虑完全替换ILIKE为TSEARCH?
全文搜索设置与“包含”查询不完全相同。它处理单词等,使您可以将 "cars" 与 "car" 匹配。
如果您确实想要一个快速的 ILIKE 查询,则没有标准数据库索引或 FTS 可以帮助您。幸运的是,pg_trgm 模块可以做到这一点。
有一件非常重要的事情:无论如何,不会有B-Tree索引可以改善这种搜索:
where field ilike '%SOMETHING%'
我想说的是,如果你执行:
create index idx_name on some_table(field);
您唯一能够改善的访问方式是where field like 'something%'
(当您搜索以某个字面量开头的值时)。因此,在这种情况下,向field
列添加常规索引不会带来任何好处。
如果您需要提高搜索响应时间,一定要考虑使用全文本搜索。
%foobar%
条件(请参见Richard的回答)。 - user330315补充其他人的观点。
首先,你不能基于字符串中间的值使用索引。索引通常是树搜索,而您无法知道搜索是否比仅扫描表更快,因此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;
因此,它们非常不同。在最合适的地方使用每个选项。
your_text_field
上建立索引时使用upper(your_text_field)
,因为Django会发出upper(x) like upper(y)
查询而不是ilike
。如果字段没有使用upper
进行索引,则该索引将不会在这些查询中使用。 - Risadinha