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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 背景 在有些应用场景中,可能会涉及多个字段的匹配。 例如这样的场景,一张表包含了几个字段,分别为歌手,曲目,专辑名称,作曲,歌词, 。。。 用户可能要在所有的字段中以分词的方式匹配刘德华,任意字段匹配即返回TRUE。 传统的做法是每个字段建立分词索引,然后挨个匹配。

背景

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

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

用户可能要在所有的字段中以分词的方式匹配刘德华,任意字段匹配即返回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)

参考

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

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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
关系型数据库 PostgreSQL
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
150 0
|
7月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
7月前
|
运维 Cloud Native 关系型数据库
云原生数据仓库产品使用合集之原生数据仓库AnalyticDB PostgreSQL版如果是列存表的话, adb支持通过根据某个字段做upsert吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
6月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL和greenplum的copy命令可以添加字段吗?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗?
96 3
|
6月前
|
关系型数据库 PostgreSQL
postgresql如何将没有关联关系的两张表的字段合并
【6月更文挑战第2天】postgresql如何将没有关联关系的两张表的字段合并
153 3
|
7月前
|
关系型数据库 MySQL 数据库
实时计算 Flink版产品使用合集之使用PostgreSQL作为源时,遇到before字段为NULL该如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
存储 NoSQL 关系型数据库
深入探索地理空间查询:如何优雅地在MySQL、PostgreSQL及Redis中实现精准的地理数据存储与检索技巧
深入探索地理空间查询:如何优雅地在MySQL、PostgreSQL及Redis中实现精准的地理数据存储与检索技巧
2143 0
|
7月前
|
分布式计算 关系型数据库 大数据
MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
7月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
172 0
|
SQL 存储 自然语言处理
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版