本文介绍了Postgresql ILIKE与TSEARCH的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

列几乎都是varchar(50)或其附近。现在我明白为了提高性能,我应该为搜索所在的领域编制索引。我应该考虑用TSEARCH完全取代ILIKE吗?

解决方案全文搜索设置与包含 。它阻止了单词等,所以你可以匹配汽车与汽车。

如果你真的想要一个快速ILIKE,那么没有标准数据库索引或FTS会有帮助。幸运的是,pg_trgm模块可以做到这一点。






I have a query with a number of test fields something like this:

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

The columns are pretty much all varchar(50) or thereabouts. Now I understand to improve performance I should index the fields upon which the search operates. Should I be considering replacing ILIKE with TSEARCH completely?

解决方案

A full text search setup is not identical to a "contains" like query. It stems words etc so you can match "cars" against "car".

If you really want a fast ILIKE then no standard database index or FTS will help. Fortunately, the pg_trgm module can do that.

这篇关于Postgresql ILIKE与TSEARCH的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-16 07:02