作者
digoal
日期
2016-04-19
标签
PostgreSQL , 分词 , 全文检索 , 全字段检索 , 任意字段检索 , 下拉框选择
背景
在一些应用程序中，可能需要对表的所有字段进行检索，有些字段可能需要精准查询，有些字段可能需要模糊查询或全文检索。
比如一些前端页面下拉框的勾选和选择。
这种需求对于应用开发人员来说，会很蛋疼，因为写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.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
