我有一个包含许多测试字段的查询,如下所示:
SELECT * FROM some-table WHERE field1 ILIKE "%thing%" OR field2 ILIKE "%thing" OR field3 ILIKE "%thing";
列几乎都是varchar(50)或左右 . 现在我理解提高性能我应该索引搜索操作的字段 . 我应该考虑完全用TSEARCH替换ILIKE吗?
全文搜索设置与“包含”类似查询不同 . 它源于单词等,所以你可以匹配“汽车”与“汽车” .
如果你真的想要一个快速的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/
添加一些其他人说的话 .
首先,您无法真正使用基于字符串中间值的索引 . 索引通常是树搜索,你无法知道你的搜索是否比扫描表更快,因此PostgreSQL将默认为seq扫描 . 仅当索引与字符串的第一部分匹配时才使用索引 . 所以:
SELECT * FROM invoice WHERE invoice_number like 'INV-2012-435%'
可能会使用索引,但 like '%44354456%' 不能 .
like '%44354456%'
一般来说,在LedgerSMB中我们使用两者,具体取决于我们正在进行的搜索类型 . 您可能会看到如下搜索:
select * from parts WHERE partnumber ilike ? || '%' and plainto_tsquery(get_default_language(), ?) @@ description;
所以这些是非常不同的 . 在最有意义的地方使用每一个 .
有一点非常重要: NO B-TREE INDEX 将改进这种搜索:
where field ilike '%SOMETHING%'
我要说的是,如果你这样做:
create index idx_name on some_table(field);
您将改进的唯一访问权限是 where field like 'something%' . (当您搜索以某些文字开头的值时) . 因此,在这种情况下,通过向 field 列添加常规索引,您将无法获益 .
where field like 'something%'
field
如果您需要改善搜索响应时间,请务必考虑使用 FULL TEXT SEARCH .
3 回答
全文搜索设置与“包含”类似查询不同 . 它源于单词等,所以你可以匹配“汽车”与“汽车” .
如果你真的想要一个快速的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/
添加一些其他人说的话 .
首先,您无法真正使用基于字符串中间值的索引 . 索引通常是树搜索,你无法知道你的搜索是否比扫描表更快,因此PostgreSQL将默认为seq扫描 . 仅当索引与字符串的第一部分匹配时才使用索引 . 所以:
可能会使用索引,但
like '%44354456%'
不能 .一般来说,在LedgerSMB中我们使用两者,具体取决于我们正在进行的搜索类型 . 您可能会看到如下搜索:
所以这些是非常不同的 . 在最有意义的地方使用每一个 .
有一点非常重要: NO B-TREE INDEX 将改进这种搜索:
我要说的是,如果你这样做:
您将改进的唯一访问权限是
where field like 'something%'
. (当您搜索以某些文字开头的值时) . 因此,在这种情况下,通过向field
列添加常规索引,您将无法获益 .如果您需要改善搜索响应时间,请务必考虑使用 FULL TEXT SEARCH .