postgresql 文本检索和索引

 

文本检索方法：

  - 完全一致检索

  - 前方一致检索

  - 后方一致检索

  - 中间一致检索

  - 正则表达式检索

  - 单词单位检索

 

我们先创建一张表

CREATE TABLE test(

id serial PRIMARY KEY, body text);

完全一致检索

WHERE body = 'foo'
完全一致检索一般是用 = 等号来作的。如果我们创建一个btree索引的话，上面的查询是能够用到索引的。
=# CREATE INDEX idx ON documents (body)
但是如果body的文本很长的时候，上面的索引就是比较浪费了。为了减少系统的负担可以创建一个哈希索引。

前方一致检索

WHERE body LIKE 'foo%'


一般的btree索引的时候，如果是C local的场合是能够利用索引的。

后方一致检索

WHERE body LIKE '%foo'
一般的btree索引对这种的检索是没有效果的。但是可以写一个函数，帮数据反转。反转后的数据就可前方一致检索一样可以利用索引了。例如我们创建一个反转函数re
=# CREATE INDEX idx_reverse ON documents (re(body));

中间一致检索

WHERE body LIKE '%foo%'
一般索引是没有效果的。gin索引虽然可以利用但是效率不高。

 

正则表达式检索

WHERE body SIMILAR TO '%[F|f]oo%
SIMILAR TO 是SQL标识利用正则表达式的关键字。正则表达式目前是没有相应的索引能够用上的。

 

单词单位检索

例：WHERE to_tsvector(body) ～ to_tsquery('foo')

tsvector类型的时候，gin，gist索引都是可以利用的。