PostgreSQL 行级全文检索

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索。这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子: postgres=# create table t(phonenum text, info text, c1 in

在一些应用程序中,可能需要对表的所有字段进行检索,有些字段可能需要精准查询,有些字段可能需要模糊查询或全文检索。
这种需求对于应用开发人员来说,会很蛋疼,因为写SQL很麻烦,例子:

postgres=# create table t(phonenum text, info text, c1 int, c2 text, c3 text, c4 timestamp);
CREATE TABLE
postgres=# insert into t values ('13888888888','i am digoal, a postgresqler',123,'china','中华人民共和国,阿里巴巴,阿',now());
INSERT 0 1
postgres=# select * from t;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

例如查询t表,条件是,任意字段匹配digoal就返回该记录。

select * from t where phonenum='digoal' or info ~ 'digoal' or c1='digoal' or ......;

每个字段都要写一个条件,有精准匹配,有全文检索。

使用行级全文检索,可以大大简化这个查询。
以结巴分词为例:
源码如下,
https://github.com/jaiminpan/pg_jieba
还有一个基于scws的pg_scws,
https://github.com/jaiminpan/pg_scws
以上都支持自定义词典。
安装略,下面看看用法:

postgres=# select t::text from t;
                                                        t                                                        
-----------------------------------------------------------------------------------------------------------------
 (13888888888,"i am digoal, a postgresqler",123,china,中华人民共和国,阿里巴巴,阿,"2016-04-19 11:15:55.208658")
(1 row)

postgres=# select to_tsvector('jiebacfg',t::text) from t;
                                                                                 to_tsvector                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ' ':6,8,11,13,33 '04':30 '11':34 '123':17 '13888888888':2 '15':36 '19':32 '2016':28 '55.208658':38 'china':19 'digoal':9 'postgresqler':14 '中华人民共和国':21 '阿里巴巴':23
(1 row)

使用t::text可以将行转成一个大文本。

postgres=# select to_tsvector('jiebacfg',t::text) @@ to_tsquery('digoal & china') from t;
 ?column? 
----------
 t
(1 row)

postgres=# select to_tsvector('jiebacfg',t::text) @@ to_tsquery('digoal & post') from t;
 ?column? 
----------
 f
(1 row)

创建行级文本索引,需要用到immutable函数索引

postgres=# create or replace function f1(regconfig,text) returns tsvector as 
$$

 select to_tsvector($1,$2);
 
$$
 language sql immutable strict;
CREATE FUNCTION

postgres=# create or replace function f1(text) returns tsvector as 
$$
          
select to_tsvector($1);   

$$
 language sql immutable strict;
CREATE FUNCTION

postgres=# alter function record_out(record) immutable;
ALTER FUNCTION
postgres=# alter function textin(cstring) immutable;
ALTER FUNCTION
postgres=# create index idx_t_1 on t using gin (f1('jiebacfg'::regconfig,t::text)) ;
CREATE INDEX

验证:

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & post') ;
 phonenum | info | c1 | c2 | c3 | c4 
----------+------+----+----+----+----
(0 rows)
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & china') ;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
  phonenum   |            info             | c1  |  c2   |              c3              |             c4             
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国,阿里巴巴,阿 | 2016-04-19 11:15:55.208658
(1 row)

postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1.52 rows=1 width=140)
   Filter: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
(2 rows)

如果记录数很多,就会用到索引,记录数很少的时候,我们可以用hint或者开关来强制索引:

postgres=# set enable_seqscan=off;
SET
postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=12.25..16.77 rows=1 width=140)
   Recheck Cond: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..12.25 rows=1 width=0)
         Index Cond: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))
(4 rows)

happy it.

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
Web App开发 关系型数据库 数据库
用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询
用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询作者digoal 日期2017-12-05 标签PostgreSQL , 搜索引擎 , GIN , ranking , high light , 全文检索 , 模糊查询 , 正则查询 , 相似查询 , ADHOC查询 背景字符串搜索是非常常见的业务需求,它包括: 1、前缀+模糊查询。
10302 1
|
9月前
|
存储 SQL 自然语言处理
如何使用AnalyticDB PostgreSQL 版实现“一站式全文检索”业务
本文从阿里云用户使用云原生数据仓库AnalyticDB PostgreSQL版(以下简称ADB PG)的实际体验出发,介绍ADB PG如何实现“一站式全文检索”业务,并详细阐述ADB PG使用的优势技术,最后提供对应业务案例分析。
32152 33
|
存储 关系型数据库 Linux
centos7 postgresql10 安装 zhparser,配置中文全文检索
centos7 postgresql10 安装 zhparser,配置中文全文检索
227 0
centos7 postgresql10 安装 zhparser,配置中文全文检索
|
存储 SQL 人工智能
4 PostgreSQL 索引,全文检索,模糊匹配,近似度匹配(三)
4 PostgreSQL 索引,全文检索,模糊匹配,近似度匹配(三)
890 1
4 PostgreSQL 索引,全文检索,模糊匹配,近似度匹配(三)
|
Web App开发 SQL 关系型数据库
|
自然语言处理 关系型数据库 PostgreSQL
PostgreSQL中文全文检索
一 安装必备软件 1.1 安装SCWS 下载scws:http://www.xunsearch.com/scws/down/scws-1.2.2.tar.
2450 0

相关产品

  • 云原生数据库 PolarDB