PostgreSQL如何对某行记录进行模糊查询

       例如BI报表的下拉框，用户可能会勾选多个条件进行查询，那么我们查询会很麻烦。

　　例如：

　　bill@bill=>create table test1(c1 int,c2 text,c3 text,c4 text);

　　CREATE TABLE

　　bill@bill=>insert into test1 values(1,'post','china','bill');

　　INSERT 0 1

　　如果我们要查询该表某行包含china的记录，我们可能需要这么去写：

　　select * from test1 where c1 ~ 'china' or c2 ~ 'china' or ...

　　可以看到这样写会相当麻烦，而且性能也不尽人意。

　　那么我们有什么办法能解决这类问题呢?

　　全文检索，我们可以通过行级别的全文检索来处理这类问题。

　　例子：

　　这里我们以pg_scws分词插件来演示。

　　下载地址：https://github.com/jaiminpan/pg_scws

　　安装：

　　git clone https://github.com/jaiminpan/pg_scws

　　cd pg_scws

　　USE_PGXS=1 make && make install

　　以上面的表为例，我们可以将改行记录转成一个全文检索的文本：

　　bill@bill=>select to_tsvector('scwscfg',test1::text) from test1;

　　to_tsvector

　　---------------------------------------

　　'1':1 'bill':4 'china':3 'post':2

　　(1 row)

　　查询：

　　bill@bill=> select to_tsvector('scwscfg',test1::text) @@ to_tsquery('post & china') from test1;

　　?column?

　　----------

　　t

　　(1 row)

　　至此，我们实现了对行级记录的全文检索查询，接着让我们再创建行级全文检索的索引来加速查询。

　　首先我们需要创建immutable类型的函数。

　　bill@bill=> create or replace function f1(regconfig,text) returns tsvector as $$

　　bill$# select to_tsvector($1,$2);

　　bill$# $$ language sql immutable strict;

　　CREATE FUNCTION

　　需要将record_out和textin函数转为immutable类型：

　　bill@bill=>alter function record_out(record) immutable;

　　ALTER FUNCTION

　　bill@bill=>alter function textin(cstring) immutable;

　　ALTER FUNCTION

　　创建索引：

　　bill@bill=> create index idx_test1 on test1 using gin (f1('scwscfg'::regconfig,test1::text)) ;

　　CREATE INDEX

　　查询测试：

　　bill@bill=>select * from test1 where f1('scwscfg'::regconfig,test1::text) @@ to_tsquery('china') ;

　　c1 | c2 | c3 | c4

　　----+------+-------+------

　　1 | post | china | bill

　　(1 row)

　　验证是否可以使用索引：

　　bill@bill=>set enable_seqscan = off;

　　SET

　　bill@bill=> explain select * from test1 where f1('scwscfg'::regconfig,test1::text) @@ to_tsquery('china') ;

　　QUERY PLAN

　　-------------------------------------------------------------------------------------------------------

　　Bitmap Heap Scan on test1 (cost=2.85..4.67 rows=1 width=100)

　　Recheck Cond: (to_tsvector('scwscfg'::regconfig, (test1.*)::text) @@ to_tsquery('china'::text))

　　-> Bitmap Index Scan on idx_test1 (cost=0.00..2.85 rows=1 width=0)

　　Index Cond: (to_tsvector('scwscfg'::regconfig, (test1.*)::text) @@ to_tsquery('china'::text))

　　(4 rows)

　　参考链接：

　　https://github.com/jaiminpan/pg_scws

　　http://www.postgres.cn/docs/13/textsearch.html