PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1

5 minute read

背景

在有些应用场景中,可能会涉及多个字段的匹配。

例如这样的场景,一张表包含了几个字段,分别为歌手,曲目,专辑名称,作曲,歌词, 。。。

用户可能要在所有的字段中以分词的方式匹配刘德华,任意字段匹配即返回TRUE。

传统的做法是每个字段建立分词索引,然后挨个匹配。

这样就导致SQL写法很冗长,而且要使用大量的OR操作。 有没有更好的方法呢?

当然有,可以将整条记录输出为一个字符串,然后对这个字符串建立分词索引。

但是问题来了,整条记录输出的格式是怎么样的,会不会影响分词结果。

PostgreSQL 行记录的输出格式

create table t1(id int, c1 text, c2 text, c3 text);    
insert into t1 values (1 , '子远e5a1cbb8' , '子远e5a1cbb8' , 'abc');    
  
postgres=# select t1::text from t1;  
                t1                   
-----------------------------------  
 (1,子远e5a1cbb8,子远e5a1cbb8,abc)  
(1 row)  
  
postgres=# \df+ record_out  
                                                                  List of functions  
   Schema   |    Name    | Result data type | Argument data types |  Type  | Security | Volatility |  Owner   | Language | Source code | Description   
------------+------------+------------------+---------------------+--------+----------+------------+----------+----------+-------------+-------------  
 pg_catalog | record_out | cstring          | record              | normal | invoker  | stable     | postgres | internal | record_out  | I/O  
(1 row)  

record类型输出对应的源码

src/backend/utils/adt/rowtypes.c

/*  
 * record_out           - output routine for any composite type.  
 */  
Datum  
record_out(PG_FUNCTION_ARGS)  
{  
...  
        /* And build the result string */  
        initStringInfo(&buf);  
  
        appendStringInfoChar(&buf, '(');  // 首尾使用括弧  
  
        for (i = 0; i < ncolumns; i++)  
        {  
...  
                if (needComma)  
                        appendStringInfoChar(&buf, ',');  // 字段间使用逗号  
                needComma = true;  
...  
                /* Detect whether we need double quotes for this value */  
                nq = (value[0] == '\0');        /* force quotes for empty string */  
                for (tmp = value; *tmp; tmp++)  
                {  
                        char            ch = *tmp;  
  
                        if (ch == '"' || ch == '\\' ||  
                                ch == '(' || ch == ')' || ch == ',' ||  
                                isspace((unsigned char) ch))  
                        {  
                                nq = true;  
                                break;  
                        }  
                }  
  
                /* And emit the string */  
                if (nq)  
                        appendStringInfoCharMacro(&buf, '"');  // 某些类型使用""号  
                for (tmp = value; *tmp; tmp++)  
                {  
                        char            ch = *tmp;  
  
                        if (ch == '"' || ch == '\\')  
                                appendStringInfoCharMacro(&buf, ch);  
                        appendStringInfoCharMacro(&buf, ch);  
                }  
                if (nq)  
                        appendStringInfoCharMacro(&buf, '"');  
        }  
  
        appendStringInfoChar(&buf, ')');  
...  

scws分词的问题

看似不应该有问题,只是多个逗号,多了双引号,这些都是字符,scws分词应该能处理。

但是实际上有点问题,例子:

这两个词只是末尾不一样,多个个逗号就变这样了

postgres=# select * from ts_debug('scwscfg', '子远e5a1cbb8,');  
 alias | description | token | dictionaries | dictionary | lexemes   
-------+-------------+-------+--------------+------------+---------  
 k     | head        | 子    | {}           |            |   
 a     | adjective   | 远    | {simple}     | simple     | {远}  
 e     | exclamation | e5a   | {simple}     | simple     | {e5a}  
 e     | exclamation | 1cbb  | {simple}     | simple     | {1cbb}  
 e     | exclamation | 8     | {simple}     | simple     | {8}  
 u     | auxiliary   | ,     | {}           |            |   
(6 rows)  
  
postgres=# select * from ts_debug('scwscfg', '子远e5a1cbb8');  
 alias | description |  token   | dictionaries | dictionary |  lexemes     
-------+-------------+----------+--------------+------------+------------  
 k     | head        | 子       | {}           |            |   
 a     | adjective   | 远       | {simple}     | simple     | {远}  
 e     | exclamation | e5a1cbb8 | {simple}     | simple     | {e5a1cbb8}  
(3 rows)  

问题分析的手段

PostgreSQL分词的步骤简介

screenshot

1. 使用parse将字符串拆分成多个token,以及每个token对应的token type

所以创建text search configuration时,需要指定parser,parser也是分词的核心

Command:     CREATE TEXT SEARCH CONFIGURATION  
Description: define a new text search configuration  
Syntax:  
CREATE TEXT SEARCH CONFIGURATION name (  
    PARSER = parser_name |  
    COPY = source_config  
)  

同时parser支持哪些token type也是建立parser时必须指定的

Command:     CREATE TEXT SEARCH PARSER  
Description: define a new text search parser  
Syntax:  
CREATE TEXT SEARCH PARSER name (  
    START = start_function ,  
    GETTOKEN = gettoken_function ,  
    END = end_function ,  
    LEXTYPES = lextypes_function  
    [, HEADLINE = headline_function ]  
)  

查看已创建了哪些parser

postgres=# select * from pg_ts_parser ;  
 prsname | prsnamespace |   prsstart   |     prstoken     |   prsend   |  prsheadline  |   prslextype     
---------+--------------+--------------+------------------+------------+---------------+----------------  
 default |           11 | prsd_start   | prsd_nexttoken   | prsd_end   | prsd_headline | prsd_lextype  
 scws    |         2200 | pgscws_start | pgscws_getlexeme | pgscws_end | prsd_headline | pgscws_lextype  
 jieba   |         2200 | jieba_start  | jieba_gettoken   | jieba_end  | prsd_headline | jieba_lextype  
(3 rows)  

查看parser支持的token type如下

scws中的释义

http://www.xunsearch.com/scws/docs.php#attr

postgres=# select * from ts_token_type('scws');  
 tokid | alias |  description    
-------+-------+---------------  
    97 | a     | adjective  
    98 | b     | difference  
    99 | c     | conjunction  
   100 | d     | adverb  
   101 | e     | exclamation  
   102 | f     | position  
   103 | g     | word root  
   104 | h     | head  
   105 | i     | idiom  
   106 | j     | abbreviation  
   107 | k     | head  
   108 | l     | temp  
   109 | m     | numeral  
   110 | n     | noun  
   111 | o     | onomatopoeia  
   112 | p     | prepositional  
   113 | q     | quantity  
   114 | r     | pronoun  
   115 | s     | space  
   116 | t     | time  
   117 | u     | auxiliary  
   118 | v     | verb  
   119 | w     | punctuation  
   120 | x     | unknown  
   121 | y     | modal  
   122 | z     | status  
(26 rows)  

2. 每种toke type,对应一个或多个字典进行匹配处理

ALTER TEXT SEARCH CONFIGURATION name  
    ADD MAPPING FOR token_type [, ... ] WITH dictionary_name [, ... ]  

查看已配置的token type 与 dict 的map信息

postgres=# select * from pg_ts_config_map ;  

3. 第一个适配token的字典,将token输出转换为lexeme

(会去除stop words),去复数等。    

以下几个函数可以用来调试分词的问题

  • ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text)

    返回指定parser 支持的token type

  • ts_parse(parser_name text, txt text, OUT tokid integer, OUT token text)

    指定parser, 将字符串输出为token

  • ts_debug(config regconfig, document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[])

    指定分词配置,将字符串输出为token以及额外的信息

上面的例子,我们可以看到使用scws parser时,输出的token发生了变化

postgres=# select * from pg_ts_parser ;  
 prsname | prsnamespace |   prsstart   |     prstoken     |   prsend   |  prsheadline  |   prslextype     
---------+--------------+--------------+------------------+------------+---------------+----------------  
 default |           11 | prsd_start   | prsd_nexttoken   | prsd_end   | prsd_headline | prsd_lextype  
 scws    |         2200 | pgscws_start | pgscws_getlexeme | pgscws_end | prsd_headline | pgscws_lextype  
 jieba   |         2200 | jieba_start  | jieba_gettoken   | jieba_end  | prsd_headline | jieba_lextype  
(3 rows)  
  
postgres=# select * from ts_parse('scws', '子远e5a1cbb8,');  
 tokid | token   
-------+-------  
   107 | 子  
    97 | 远  
   101 | e5a  
   101 | 1cbb  
   101 | 8  
   117 | ,  
(6 rows)  

如何解决

在不修改scws代码的情况下,我们可以先将逗号替换为空格,scws是会忽略空格的

postgres=# select replace(t1::text, ',', ' ') from t1;   
              replace                
-----------------------------------  
 (1 子远e5a1cbb8 子远e5a1cbb8 abc)  
(1 row)  
  
postgres=# select to_tsvector('scwscfg', replace(t1::text, ',', ' ')) from t1;   
              to_tsvector                
---------------------------------------  
 '1':1 'abc':6 'e5a1cbb8':3,5 '远':2,4  
(1 row)  

行全文检索 索引用法

postgres=# create or replace function rec_to_text(anyelement) returns text as $$  
select $1::text;  
$$ language sql strict immutable;  
CREATE FUNCTION  
  
postgres=# create index idx on t1 using gin (to_tsvector('scwscfg', replace(rec_to_text(t1), ',', ' ')));  
CREATE INDEX  
  
SQL写法  
postgres=# explain verbose select * from t1 where to_tsvector('scwscfg', replace(rec_to_text(t1), ',', ' ')) @@ to_tsquery('scwscfg', '子远e5a1cbb8');  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t1  (cost=4.50..6.52 rows=1 width=100)  
   Output: c1, c2, c3, c4  
   Recheck Cond: (to_tsvector('scwscfg'::regconfig, replace(rec_to_text(t1.*), ','::text, ' '::text)) @@ '''远'' & ''e5a1cbb8'''::tsquery)  
   ->  Bitmap Index Scan on idx  (cost=0.00..4.50 rows=1 width=0)  
         Index Cond: (to_tsvector('scwscfg'::regconfig, replace(rec_to_text(t1.*), ','::text, ' '::text)) @@ '''远'' & ''e5a1cbb8'''::tsquery)  
(5 rows)  

参考

  • http://www.xunsearch.com/scws/docs.php#attr

  • https://github.com/jaiminpan/pg_jieba

  • https://github.com/jaiminpan/pg_scws

  • https://yq.aliyun.com/articles/7730

  • 分词速度,每CPU核约4.44万字/s。

postgres=# create extension pg_scws;  
CREATE EXTENSION  
Time: 6.544 ms  
postgres=# alter function to_tsvector(regconfig,text) volatile;  
ALTER FUNCTION  
postgres=# select to_tsvector('scwscfg','中华人民共和国万岁,如何加快PostgreSQL结巴分词加载速度');  
                                       to_tsvector                                         
-----------------------------------------------------------------------------------------  
 'postgresql':4 '万岁':2 '中华人民共和国':1 '分词':6 '加快':3 '加载':7 '结巴':5 '速度':8  
(1 row)  
Time: 0.855 ms  
postgres=# set zhparser.dict_in_memory = t;  
SET  
Time: 0.339 ms  
postgres=# explain (buffers,timing,costs,verbose,analyze) select to_tsvector('scwscfg','中华人民共和国万岁,如何加快PostgreSQL结巴分词加载速度') from generate_series(1,100000);  
                                                               QUERY PLAN                                                                 
------------------------------------------------------------------------------------------------------------  
 Function Scan on pg_catalog.generate_series  (cost=0.00..260.00 rows=1000 width=0) (actual time=11.431..17971.197 rows=100000 loops=1)  
   Output: to_tsvector('scwscfg'::regconfig, '中华人民共和国万岁,如何加快PostgreSQL结巴分词加载速度'::text)  
   Function Call: generate_series(1, 100000)  
   Buffers: temp read=172 written=171  
 Planning time: 0.042 ms  
 Execution time: 18000.344 ms  
(6 rows)  
Time: 18000.917 ms  
postgres=# select 8*100000/18.000344;  
      ?column?        
--------------------  
 44443.595077960732  
(1 row)  

cpu

Architecture:          x86_64  
CPU op-mode(s):        32-bit, 64-bit  
Byte Order:            Little Endian  
CPU(s):                32  
On-line CPU(s) list:   0-31  
Thread(s) per core:    1  
Core(s) per socket:    32  
Socket(s):             1  
NUMA node(s):          1  
Vendor ID:             GenuineIntel  
CPU family:            6  
Model:                 63  
Model name:            Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz  
Stepping:              2  
CPU MHz:               2494.224  
BogoMIPS:              4988.44  
Hypervisor vendor:     KVM  
Virtualization type:   full  
L1d cache:             32K  
L1i cache:             32K  
L2 cache:              256K  
L3 cache:              30720K  
NUMA node0 CPU(s):     0-31  

祝大家玩得开心,欢迎随时来 阿里云促膝长谈 业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力做 最贴地气的云数据库

Flag Counter

digoal’s 大量PostgreSQL文章入口