PostgreSQL 行级全文检索-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL 行级全文检索

简介: 在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索。这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子: postgres=# create table t(phonenum text, info text, c1 in

在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索。
这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子:

postgres=# create table t(phonenum text, info text, c1 int, c2 text, c3 text, c4 timestamp);
CREATE TABLE
postgres=# insert into t values ('13888888888','i am digoal, a postgresqler',123,'china','中华人民共和国,阿里巴巴,阿',now());
INSERT 0 1
postgres=# select * from t;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

例如查询t表,条件是,任意字段匹配digoal就返回该记录。

select * from t where phonenum='digoal' or info ~ 'digoal' or c1='digoal' or ......;

每个字段都要写一个条件,有精准匹配,有全文检索。

使用行级全文检索,可以大大简化这个查询。
以结巴分词为例:
源码如下,
https://github.com/jaiminpan/pg_jieba
还有一个基于scws的pg_scws,
https://github.com/jaiminpan/pg_scws
以上都支持自定义词典。
安装略,下面看看用法:

postgres=# select t::text from t;
                                                        t                                                        
-----------------------------------------------------------------------------------------------------------------
 (13888888888,"i am digoal, a postgresqler",123,china,中华人民共和国,阿里巴巴,阿,"2016-04-19 11:15:55.208658")
(1 row)

postgres=# select to_tsvector('jiebacfg',t::text) from t;
                                                                                 to_tsvector                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ' ':6,8,11,13,33 '04':30 '11':34 '123':17 '13888888888':2 '15':36 '19':32 '2016':28 '55.208658':38 'china':19 'digoal':9 'postgresqler':14 '中华人民共和国':21 '阿里巴巴':23
(1 row)

使用t::text可以将行转成一个大文本。

postgres=# select to_tsvector('jiebacfg',t::text) @@ to_tsquery('digoal & china') from t;
 ?column? 
----------
 t
(1 row)

postgres=# select to_tsvector('jiebacfg',t::text) @@ to_tsquery('digoal & post') from t;
 ?column? 
----------
 f
(1 row)

创建行级文本索引,需要用到immutable函数索引

postgres=# create or replace function f1(regconfig,text) returns tsvector as 
$$

 select to_tsvector($1,$2);
 
$$
 language sql immutable strict;
CREATE FUNCTION

postgres=# create or replace function f1(text) returns tsvector as 
$$
          
select to_tsvector($1);   

$$
 language sql immutable strict;
CREATE FUNCTION

postgres=# alter function record_out(record) immutable;
ALTER FUNCTION
postgres=# alter function textin(cstring) immutable;
ALTER FUNCTION
postgres=# create index idx_t_1 on t using gin (f1('jiebacfg'::regconfig,t::text)) ;
CREATE INDEX

验证:

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & post') ;
 phonenum | info | c1 | c2 | c3 | c4 
----------+------+----+----+----+----
(0 rows)
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & china') ;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1.52 rows=1 width=140)
   Filter: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
(2 rows)

如果记录数很多,就会用到索引,记录数很少的时候,我们可以用hint或者开关来强制索引:

postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=12.25..16.77 rows=1 width=140)
   Recheck Cond: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..12.25 rows=1 width=0)
         Index Cond: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
(4 rows)

happy it.

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接