数据库 Postgres Fulltext Search (一)

hooopo · 2019年02月26日 · 最后由 hooopo 回复于 2019年02月27日 · 1106 次阅读
本帖已被设为精华帖!

Postgres提供了LIKE、ILIKE、正则匹配等字符串匹配函数,对于一般的字符串匹配需求已经够用。然而,传统LIKE匹配存在几个问题:

  • 性能低效,LIKE匹配可以通过pg_trgm extension建立gin或gist索引缓解
  • 没有相关度支持,不能解决匹配程度问题,其实可以使用fuzzywuzzy来解决,但是麻烦了一些
  • 没有stemming等
  • 多条件JOIN效率低,查询复杂

安装 zhparser

安装 SCWS

wget -q -O - https://github.com/hightman/scws/archive/1.2.2.tar.gz | tar xzf -
cd scws-1.2.2 ; ./configure ; make install

下载zhparser源码

git clone https://github.com/amutu/zhparser.git

编译和安装zhparser

SCWS_HOME=/usr/local make && make install

如果你使用的是阿里云postgres rds,已经有预装好的zhparser插件,可以使用下面语句验证:

 select * from pg_available_extensions where name = 'zhparser';
   name   | default_version | installed_version |                 comment
----------+-----------------+-------------------+------------------------------------------
 zhparser | 1.0             | 1.0               | a parser for full-text search of Chinese

另外,阿里云rds是支持自定义词典的。

准备数据

从kaggle下载测试数据,也可以通过链接直接下载,需要注册账号:

kaggle datasets download -d noxmoon/chinese-official-daily-news-since-2016
unzip chinese-official-daily-news-since-2016.zip

创建新闻表,字段有date、tag、headline、content

create table news (date date, tag varchar, headline varchar, content text);

使用COPY命令导入新闻数据

COPY news FROM '/Users/hooopo/w/learn_pg/chinese_news.csv' CSV HEADER;

tsvector 和 tsquery

postgres的全文检索功能,核心就是tsvector和tsquery这两个pg内置类型,

select headline::tsvector from news where length(headline) < 8 limit 10;
     headline
------------------
 '太行山上新农人'
 '【数据春运】'
 '春运暖心归乡路'
 '爱心温暖返程路'
 '黄静汶同志逝世'
 '北戴河机场启用'
 '关注叙利亚局势'
 '【凡人善举】'
 '【凡人善举】'
 '童心共筑中国梦'

由于默认的default_text_search_config是english,中文分词并没有生效:

select name, setting from pg_settings where name ~ 'text_search';
            name            |      setting
----------------------------+--------------------
 default_text_search_config | pg_catalog.english

创建一个中文分词的search config:

CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zhcfg 
ADD MAPPING FOR a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z 
WITH simple;

使用zhcfg之后的tsvector:

select headline,to_tsvector('zhcfg', headline) as tsv from news where length(headline) < 8 limit 10;
    headline    |                  tsv
----------------+----------------------------------------
 太行山上新农人 | '人':4 '太行':1 '山上':2 '新农':3
 【数据春运】   | '【':1 '】':4 '数据':2 '春运':3
 春运暖心归乡路 | '归乡':4 '心':3 '春运':1 '暖':2 '路':5
 爱心温暖返程路 | '温暖':2 '爱心':1 '路':4 '返程':3
 黄静汶同志逝世 | '同志':2 '逝世':3 '黄静汶':1
 北戴河机场启用 | '北戴河':1 '启用':3 '机场':2
 关注叙利亚局势 | '关注':1 '叙利亚':2 '局势':3
 【凡人善举】   | '【':1 '】':4 '凡人':2 '善举':3
 【凡人善举】   | '【':1 '】':4 '凡人':2 '善举':3
 童心共筑中国梦 | '中国':4 '共':2 '梦':5 '童心':1 '筑':3

其中,标点符号也被当成了lexemes,设置忽略标点符号:

set zhparser.punctuation_ignore TO t; /*本session生效,全局生效需要修改config文件*/
select headline,to_tsvector('zhcfg', headline) as tsv from news where length(headline) < 8 limit 10;
    headline    |                  tsv
----------------+----------------------------------------
 太行山上新农人 | '人':4 '太行':1 '山上':2 '新农':3
 【数据春运】   | '数据':1 '春运':2
 春运暖心归乡路 | '归乡':4 '心':3 '春运':1 '暖':2 '路':5
 爱心温暖返程路 | '温暖':2 '爱心':1 '路':4 '返程':3
 黄静汶同志逝世 | '同志':2 '逝世':3 '黄静汶':1
 北戴河机场启用 | '北戴河':1 '启用':3 '机场':2
 关注叙利亚局势 | '关注':1 '叙利亚':2 '局势':3
 【凡人善举】   | '凡人':1 '善举':2
 【凡人善举】   | '凡人':1 '善举':2
 童心共筑中国梦 | '中国':4 '共':2 '梦':5 '童心':1 '筑':3

使用tsvector进行一次搜索:

select headline,
       to_tsvector('zhcfg', headline) as tsv 
  from news 
 where length(headline) < 8 
       and to_tsvector('zhcfg', headline) @@ to_tsquery('zhcfg', '中 国') 
 limit 10;

    headline    |                  tsv
----------------+----------------------------------------
 童心共筑中国梦 | '中国':4 '共':2 '梦':5 '童心':1 '筑':3
 【中国有我】   | '中国':1 '我':3 '有':2
 四海共庆中国年 | '中国':3 '共庆':2 '四海':1 '年':4
 【点赞中国】   | '中国':3 '点':1 '赞':2
 【点赞中国】   | '中国':3 '点':1 '赞':2
 【点赞中国】   | '中国':3 '点':1 '赞':2
 【中国有我】   | '中国':1 '我':3 '有':2
 【大美中国】   | '中国':3 '大':1 '美':2
select COALESCE(headline, '') || ' '  || COALESCE(content, '') as doc 
  from news 
 where length(headline) < 8 
       and to_tsvector('zhcfg', COALESCE(headline, '') || ' ' || COALESCE(content, '')) @@ to_tsquery('zhcfg', '中国') 
 limit 1;                                                                   

反向索引

使用反向索引来提高查询速度:

CREATE INDEX fulltext_idx 
ON news 
USING GIN (to_tsvector('zhcfg', COALESCE(headline, '') || ' '  || COALESCE(content, '')));

看一下查询计划,上面语句在加了GIN索引之后,使用了Bitmap Index:

Limit  (cost=16.79..27.29 rows=1 width=32) (actual time=3.174..3.175 rows=1 loops=1)
  ->  Bitmap Heap Scan on news  (cost=16.79..384.33 rows=35 width=32) (actual time=3.173..3.173 rows=1 loops=1)
        Recheck Cond: (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery)
        Filter: (length((headline)::text) < 8)
        Rows Removed by Filter: 751
        Heap Blocks: exact=241
        ->  Bitmap Index Scan on fulltext_idx  (cost=0.00..16.78 rows=104 width=0) (actual time=2.027..2.027 rows=6272 loops=1)
              Index Cond: (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery)
Planning time: 0.168 ms
Execution time: 3.231 ms

btree_gin

对于等值过滤和全文检索复合查询的情况,还可以使用pg内置的btree_gin提高查询效率,例如:where tag = '详细全文' AND to_tsvector(xx) @@ to_tsquery(xx)。

explain analyze select count(*) from news where tag = '详细全文' and to_tsvector('zhcfg', COALESCE(headline, '') || ' ' || COALESCE(content, '')) @@ to_tsquery('zhcfg', '中国');
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=384.05..384.06 rows=1 width=8) (actual time=18.457..18.457 rows=1 loops=1)
   ->  Bitmap Heap Scan on news  (cost=16.79..383.91 rows=58 width=0) (actual time=1.571..17.917 rows=4822 loops=1)
         Recheck Cond: (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery)
         Filter: ((tag)::text = '详细全文'::text)
         Rows Removed by Filter: 1450
         Heap Blocks: exact=1915
         ->  Bitmap Index Scan on fulltext_idx  (cost=0.00..16.78 rows=104 width=0) (actual time=1.300..1.300 rows=6272 loops=1)
               Index Cond: (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery)

上面的查询,先通过fulltext_idx这条索引扫描了6272条记录,然后再通过filter(tag='详细全文')过滤掉了1450条记录。也就是说,我们可以通过btree_gin,让gin索引和btree索引组合起来,发挥最大威力。

create index fulltext_idx_1 on news using gin (tag, to_tsvector('zhcfg', COALESCE(headline, '') || ' '  || COALESCE(content, '')));

再看一下查询计划:

explain analyze select count(*) from news where tag = '详细全文' and to_tsvector('zhcfg', COALESCE(headline, '') || ' ' || COALESCE(content, '')) @@ to_tsquery('zhcfg', '中国');
                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=246.82..246.83 rows=1 width=8) (actual time=7.540..7.540 rows=1 loops=1)
   ->  Bitmap Heap Scan on news  (cost=28.59..246.68 rows=58 width=0) (actual time=4.363..6.899 rows=4822 loops=1)
         Recheck Cond: (((tag)::text = '详细全文'::text) AND (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery))
         Heap Blocks: exact=1718
         ->  Bitmap Index Scan on fulltext_idx_1  (cost=0.00..28.58 rows=58 width=0) (actual time=3.870..3.870 rows=4822 loops=1)
               Index Cond: (((tag)::text = '详细全文'::text) AND (to_tsvector('zhcfg'::regconfig, (((COALESCE(headline, ''::character varying))::text || ' '::text) || COALESCE(content, ''::text))) @@ '''中国'''::tsquery))

直接通过fulltext_idx_1这条索引扫描了4822条记录,没有多余的filter过程,对于区分性高的过滤条件来说,会极大提升查询效率。

共收到 2 条回复
jasl 将本帖设为了精华贴 02月26日 21:42

使用反向索引来提纲查询速度 应为 使用反向索引来 提高 查询速度

rocLv 回复

fixed.

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册