安全检查中...
请打开浏览器的javascript，然后刷新浏览器
< 浏览器安全检查中...
还剩 5 秒&PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1
在有些应用场景中，可能会涉及多个字段的匹配。
例如这样的场景，一张表包含了几个字段，分别为歌手，曲目，专辑名称，作曲，歌词，
用户可能要在所有的字段中以分词的方式匹配刘德华，任意字段匹配即返回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;
-----------------------------------
(1,子远e5a1cbb8,子远e5a1cbb8,abc)
postgres=# \df+ record_out
List of functions
| Result data type | Argument data types |
| Security | Volatility |
| Language | Source code | Description
------------+------------+------------------+---------------------+--------+----------+------------+----------+----------+-------------+-------------
pg_catalog | record_out | cstring
| normal | invoker
| postgres | internal | record_out
record类型输出对应的源码src/backend/utils/adt/rowtypes.c
* record_out
- output routine for any composite type.
record_out(PG_FUNCTION_ARGS)
/* And build the result string */
initStringInfo(&buf);
appendStringInfoChar(&buf, '(');
// 首尾使用括弧
for (i = 0; i & i++)
if (needComma)
appendStringInfoChar(&buf, ',');
// 字段间使用逗号
needComma =
/* Detect whether we need double quotes for this value */
nq = (value[0] == '\0');
/* force quotes for empty string */
for (tmp = * tmp++)
if (ch == '"' || ch == '\\' ||
ch == '(' || ch == ')' || ch == ',' ||
isspace((unsigned char) ch))
/* And emit the string */
appendStringInfoCharMacro(&buf, '"');
// 某些类型使用""号
for (tmp = * tmp++)
if (ch == '"' || ch == '\\')
appendStringInfoCharMacro(&buf, ch);
appendStringInfoCharMacro(&buf, ch);
appendStringInfoCharMacro(&buf, '"');
appendStringInfoChar(&buf, ')');
scws分词的问题
看似不应该有问题，只是多个逗号，多了双引号，这些都是字符，scws分词应该能处理。但是实际上有点问题，例子：这两个词只是末尾不一样，多个个逗号就变这样了
postgres=# select * from ts_debug('scwscfg', '子远e5a1cbb8,');
alias | description | token | dictionaries | dictionary | lexemes
-------+-------------+-------+--------------+------------+---------
| adjective
| {simple}
| exclamation | e5a
| {simple}
| exclamation | 1cbb
| {simple}
| exclamation | 8
| {simple}
| auxiliary
postgres=# select * from ts_debug('scwscfg', '子远e5a1cbb8');
alias | description |
| dictionaries | dictionary |
-------+-------------+----------+--------------+------------+------------
| adjective
| {simple}
| exclamation | e5a1cbb8 | {simple}
| {e5a1cbb8}
问题分析的手段
PostgreSQL分词的步骤简介
.1. 使用parse将字符串拆分成多个token，以及每个token对应的token type所以创建text search configuration时，需要指定parser，parser也是分词的核心
CREATE TEXT SEARCH CONFIGURATION
Description: define a new text search configuration
CREATE TEXT SEARCH CONFIGURATION name (
PARSER = parser_name |
COPY = source_config
同时parser支持哪些token type也是建立parser时必须指定的
CREATE TEXT SEARCH PARSER
Description: define a new text search parser
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_
prsname | prsnamespace |
prsheadline
prslextype
---------+--------------+--------------+------------------+------------+---------------+----------------
11 | prsd_start
| prsd_nexttoken
| prsd_end
| prsd_headline | prsd_lextype
2200 | pgscws_start | pgscws_getlexeme | pgscws_end | prsd_headline | pgscws_lextype
2200 | jieba_start
| jieba_gettoken
| jieba_end
| prsd_headline | jieba_lextype
查看parser支持的token type如下scws中的释义
postgres=# select * from ts_token_type('scws');
tokid | alias |
description
-------+-------+---------------
| adjective
| difference
| conjunction
| exclamation
| position
| word root
| abbreviation
| onomatopoeia
| prepositional
| quantity
| auxiliary
| punctuation
.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_
.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_
prsname | prsnamespace |
prsheadline
prslextype
---------+--------------+--------------+------------------+------------+---------------+----------------
11 | prsd_start
| prsd_nexttoken
| prsd_end
| prsd_headline | prsd_lextype
2200 | pgscws_start | pgscws_getlexeme | pgscws_end | prsd_headline | pgscws_lextype
2200 | jieba_start
| jieba_gettoken
| jieba_end
| prsd_headline | jieba_lextype
postgres=# select * from ts_parse('scws', '子远e5a1cbb8,');
tokid | token
-------+-------
101 | 1cbb
在不修改scws代码的情况下，我们可以先将逗号替换为空格，scws是会忽略空格的
postgres=# select replace(t1::text, ',', ' ') from t1;
-----------------------------------
(1 子远e5a1cbb8 子远e5a1cbb8 abc)
postgres=# select to_tsvector('scwscfg', replace(t1::text, ',', ' ')) from t1;
to_tsvector
---------------------------------------
'1':1 'abc':6 'e5a1cbb8':3,5 '远':2,4
行全文检索 索引用法
postgres=# create or replace function rec_to_text(anyelement) returns text as $$
select $1::
$$ language
CREATE FUNCTION
postgres=# create index idx on t1 using gin (to_tsvector('scwscfg', replace(rec_to_text(t1), ',', ' ')));
CREATE INDEX
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)
分词速度，每CPU核约4.44万字/s。
postgres=# create extension pg_
CREATE EXTENSION
Time: 6.544 ms
postgres=# alter function to_tsvector(regconfig,text)
ALTER FUNCTION
postgres=# select to_tsvector('scwscfg','中华人民共和国万岁，如何加快PostgreSQL结巴分词加载速度');
to_tsvector
-----------------------------------------------------------------------------------------
'postgresql':4 '万岁':2 '中华人民共和国':1 '分词':6 '加快':3 '加载':7 '结巴':5 '速度':8
Time: 0.855 ms
postgres=# set zhparser.dict_in_memory =
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.. 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:
postgres=# select 8*.000344;
--------------------
Architecture:
CPU op-mode(s):
32-bit, 64-bit
Byte Order:
Little Endian
On-line CPU(s) list:
Thread(s) per core:
Core(s) per socket:
Socket(s):
NUMA node(s):
Vendor ID:
GenuineIntel
CPU family:
Model name:
Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz
Hypervisor vendor:
Virtualization type:
L1d cache:
L1i cache:
NUMA node0 CPU(s):
祝大家玩得开心，欢迎随时来 阿里云促膝长谈 业务需求 ，恭候光临。
阿里云的小伙伴们加油，努力做 最贴地气的云数据库 。
本文为云栖社区原创内容，未经允许不得转载，如需转载请发送邮件至yqeditor@list.；如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@ 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
用云栖社区APP，舒服~
【云栖快讯】红轴机械键盘、无线鼠标等753个大奖，先到先得，云栖社区首届博主招募大赛9月21日-11月20日限时开启，为你再添一个高端技术交流场所&&
PostgreSQL被业界誉为“最先进的开源数据库”，面向企业复杂SQL处理的OLTP在线事务处理场景，支持No...
融合云计算、大数据优势，连接用户、医疗设备、医疗机构以及医疗ISV，致力于构建医疗行业云生态。 云计算弹性可扩展...
立足于对政务信息化的深刻理解，在云计算和大数据技术上持续创新，构筑开放共享、敏捷高效、安全可信的政务云基础架构，...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效率，降低 IT 成本...
Loading...2008年11月 总版技术专家分月排行榜第三2008年5月 总版技术专家分月排行榜第三2008年3月 总版技术专家分月排行榜第三
2009年3月 Web 开发大版内专家分月排行榜第一2009年2月 Web 开发大版内专家分月排行榜第一2008年11月 Web 开发大版内专家分月排行榜第一2008年7月 Web 开发大版内专家分月排行榜第一2008年6月 Web 开发大版内专家分月排行榜第一2008年5月 Web 开发大版内专家分月排行榜第一2008年4月 Web 开发大版内专家分月排行榜第一2008年3月 Web 开发大版内专家分月排行榜第一2008年2月 Web 开发大版内专家分月排行榜第一
2008年11月 总版技术专家分月排行榜第三2008年5月 总版技术专家分月排行榜第三2008年3月 总版技术专家分月排行榜第三
2009年3月 Web 开发大版内专家分月排行榜第一2009年2月 Web 开发大版内专家分月排行榜第一2008年11月 Web 开发大版内专家分月排行榜第一2008年7月 Web 开发大版内专家分月排行榜第一2008年6月 Web 开发大版内专家分月排行榜第一2008年5月 Web 开发大版内专家分月排行榜第一2008年4月 Web 开发大版内专家分月排行榜第一2008年3月 Web 开发大版内专家分月排行榜第一2008年2月 Web 开发大版内专家分月排行榜第一
本帖子已过去太久远了，不再提供回复功能。2010年4月 荣获微软MVP称号
2008年10月 MS-SQL Server大版内专家分月排行榜第二2008年6月 MS-SQL Server大版内专家分月排行榜第二
2005年 总版技术专家分年内排行榜第四
2006年11月 总版技术专家分月排行榜第一2006年3月 总版技术专家分月排行榜第一2006年2月 总版技术专家分月排行榜第一2006年1月 总版技术专家分月排行榜第一2005年12月 总版技术专家分月排行榜第一
2010年4月 荣获微软MVP称号
2008年10月 MS-SQL Server大版内专家分月排行榜第二2008年6月 MS-SQL Server大版内专家分月排行榜第二
本帖子已过去太久远了，不再提供回复功能。