AI 助理
文档备案控制台
开发者社区 PolarDB开源 PolarDB PostgreSQL 版 文章 正文

PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

2022-12-29 456
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
PolarClaw，2核4GB
简介： PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的 价值产出, 将数据变成生产力. 本文将介绍PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的
价值产出, 将数据变成生产力.

本文将介绍PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

测试环境为macos+docker, polardb部署请参考:

测试

PG 模糊搜索采用GIN倒排索引, 使用pg_trgm插件将字符串前1后2加上空格后, 按连续3个字符切分, 并对切分后的token建立token,ctid的倒排索引.

在模糊搜索时, 可以将搜索字符串按同样方式切分, 根据倒排搜索快速的定位到对应的ctid.

即使没有parray_gin, 我们也可以将数组或JSON格式化处理后, 用大字符串和pg_trgm来实现元素模糊搜索. 例如

array['abc','aaa','hello']  
  
把元素内容的sep char和quote char转义, 然后直接把 'abc','aaa','hello'当成字符串处理. 建立pg_trgm gin索引.    
  
搜索元素时如果需要指定元素前缀或后缀搜索, 那么带上sep char和quote char即可.

使用parray_gin就简单多了, 不需要处理那么多.

下面测试PolarDB+parray_gin 实现数组内元素的模糊搜索.

git clone --depth 1 http://github.com/theirix/parray_gin/  
  
cd parray_gin/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
export PGHOST=localhost  
  
[postgres@1bbb8082aa60 parray_gin]$ psql  
psql (11.9)  
Type "help" for help.  
  
postgres=# \q  
[postgres@1bbb8082aa60 parray_gin]$ USE_PGXS=1 make installcheck  
/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/postgres/tmp_basedir_polardb_pg_1100_bld/bin'      --inputdir=test --dbname=contrib_regression op index  
(using postmaster on localhost, default port)  
============== dropping database "contrib_regression" ==============  
NOTICE:  database "contrib_regression" does not exist, skipping  
DROP DATABASE  
============== creating database "contrib_regression" ==============  
CREATE DATABASE  
ALTER DATABASE  
============== running regression test queries        ==============  
test op                           ... ok  
test index                        ... ok  
  
  
==========================================================  
 All 2 tests passed.   
  
 POLARDB:  
 All 2 tests, 0 tests in ignore, 0 tests in polar ignore.   
==========================================================  
create table t (id int, info text[]);  
  
create or replace function gen_text_arr(int) returns text[] as $$  
  select array(select md5(random()::text) from generate_series(1,$1));  
$$ language sql strict;  
  
postgres=# select gen_text_arr(10);  
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
gen_text_arr | {4134ee81fcdc29da486df37a1725e1cc,d0bb424307f93a6374d1af5a4b1c0451,def4b4bc24bc6aefb084df8a1571d773,aff17d39b2c3e8ccebf1059c2cd466dc,3988cb3f89372081c6444b7f8a825cf6,77d3a12d9a5159bd2e11fac1782eaf90,0ecac2cd508f60221b31934ea1128223,622819cfa7c3e3e600f70ed90265edaa,e9311e8d6f23be74b2e73eae4408aaa8,207eb23a50212cb101f83a6041211d90}  
  
postgres=# insert into t select id , gen_text_arr(10) from generate_series(1,1000) id;  
INSERT 0 1000  
  
postgres=# select * from t where info @@> array['%4b1%'];  
 id  |                                                                                                                                                            
          info                                                                                                                                                    
                     
-----+----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
-------------------  
  14 | {745b761d7145edb79904c5217c0ec0b4,eab9d9d4de9afc8c7a2bc4cdcd3bcb2a,3116cd48046936709c56e952f5d50380,642eec5d3c17721dadb89759ac116821,49ba14c3c71b73c0a3b8  
6aa6f20a4f9c,01632c5889d4ae642422fea8620187e1,078ea7bf29a6f8bf53c6abcec98df5ad,2548e08ad3cb87dfcfe55a86e47cc60f,0c7002203e72d854f9c0643bec6c59b7,cfdd57d32f4bcee  
8b4b1adfe11a08a81}  
  33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c9  
2240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0d  
e41b70e804dfcc41d}  
...  
  
postgres=# select * from t where info @@> array['%4b1ac%'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
postgres=# select * from t where info @@> array['%4b1acd%'];  
 id | info   
----+------  
(0 rows)  
  
postgres=# explain select * from t where info @@> array['%4b1ac%'];  
                                QUERY PLAN                                  
--------------------------------------------------------------------------  
 Bitmap Heap Scan on t  (cost=28.01..32.02 rows=1 width=36)  
   Recheck Cond: (info @@> '{%4b1ac%}'::text[])  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..28.01 rows=1 width=0)  
         Index Cond: (info @@> '{%4b1ac%}'::text[])  
(4 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=60.01..64.02 rows=1 width=36) (actual time=0.121..0.122 rows=1 loops=1)  
   Output: id, info  
   Recheck Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Heap Blocks: exact=1  
   Buffers: shared hit=16  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..60.01 rows=1 width=0) (actual time=0.109..0.109 rows=1 loops=1)  
         Index Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
         Buffers: shared hit=15  
 Planning Time: 0.075 ms  
 Execution Time: 0.144 ms  
(10 rows)  
  
Time: 0.699 ms  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 0.733 ms  
  
postgres=# insert into t select id , gen_text_arr(10) from generate_series(1,120000) id;  
INSERT 0 100000  
Time: 9242.877 ms (00:09.243)  
postgres=# \dt+  
                   List of relations  
 Schema | Name | Type  |  Owner   | Size  | Description   
--------+------+-------+----------+-------+-------------  
 public | t    | table | postgres | 50 MB |   
(1 row)  
  
  
  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 4.783 ms  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                      QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=96.94..529.04 rows=121 width=36) (actual time=4.114..4.115 rows=1 loops=1)  
   Output: id, info  
   Recheck Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Heap Blocks: exact=1  
   Buffers: shared hit=48  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..96.91 rows=121 width=0) (actual time=4.103..4.103 rows=1 loops=1)  
         Index Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
         Buffers: shared hit=47  
 Planning Time: 0.090 ms  
 Execution Time: 4.170 ms  
(10 rows)

全表扫描性能差了几十倍

postgres=# set enable_bitmapscan =off;  
SET  
Time: 0.473 ms  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------  
 Seq Scan on public.t  (cost=0.00..7881.50 rows=121 width=36) (actual time=0.632..193.929 rows=1 loops=1)  
   Output: id, info  
   Filter: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Rows Removed by Filter: 120999  
   Buffers: shared hit=6229 read=140  
 Planning Time: 0.081 ms  
 Execution Time: 193.947 ms  
(7 rows)  
  
Time: 194.697 ms  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 199.342 ms

有了parray_gin, 在设计数据结构时, 可以更加灵活, 例如将“一个时间段、一个组、一个对象”的“多个标签、多个信息”打包成1行数组存储, 对数组进行元素搜索, 则可以快速匹配到符合条件的“一个时间段、一个组、一个对象”.

参考

http://github.com/theirix/parray_gin/

《PostgreSQL 数组或JSON内容的模糊匹配索引插件: parray_gin》

文章标签：
云原生数据库 PolarDB
自然语言处理
SQL
关系型数据库
索引
PostgreSQL
PolarDB
数据格式
存储
JSON
分布式数据库
关键词：
云原生数据库 PolarDB开源
开源JSON
JSON数组
云原生数据库 PolarDB搜索
数组JSON
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
体验瑶池数据库
|
6月前
|
SQL 关系型数据库 MySQL
开源新发布｜PolarDB-X v2.4.2开源生态适配升级
PolarDB-X v2.4.2开源发布，重点完善生态能力：新增客户端驱动、开源polardbx-proxy组件，支持读写分离与高可用；强化DDL变更、扩缩容等运维能力，并兼容MySQL主备复制及MCP AI生态。
体验瑶池数据库
1597 20
开源新发布｜PolarDB-X v2.4.2开源生态适配升级
阿里云开发者
|
6月前
|
SQL 关系型数据库 MySQL
开源新发布｜PolarDB-X v2.4.2开源生态适配升级
PolarDB-X v2.4.2发布，新增开源Proxy组件与客户端驱动，支持读写分离、无感高可用切换及DDL在线变更，兼容MySQL生态，提升千亿级大表运维稳定性。
阿里云开发者
1622 24
开源新发布｜PolarDB-X v2.4.2开源生态适配升级
德哥
|
存储 NoSQL 关系型数据库
PolarDB开源数据库进阶课17 集成数据湖功能
本文介绍了如何在PolarDB数据库中接入pg_duckdb、pg_mooncake插件以支持数据湖功能, 可以读写对象存储的远程数据, 支持csv, parquet等格式, 支持delta等框架, 并显著提升OLAP性能。
德哥
1032 2
德哥
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课15 集成DeepSeek等大模型
本文介绍了如何在PolarDB数据库中接入私有化大模型服务，以实现多种应用场景。实验环境依赖于Docker容器中的loop设备模拟共享存储，具体搭建方法可参考相关系列文章。文中详细描述了部署ollama服务、编译并安装http和openai插件的过程，并通过示例展示了如何使用这些插件调用大模型API进行文本分析和情感分类等任务。此外，还探讨了如何设计表结构及触发器函数自动处理客户反馈数据，以及生成满足需求的SQL查询语句。最后对比了不同模型的回答效果，展示了deepseek-r1模型的优势。
德哥
863 3
德哥
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课14 纯享单机版
PolarDB不仅支持基于“共享存储+多计算节点”的集群版，还提供类似开源PostgreSQL的单机版。单机版部署简单，适合大多数应用场景，并可直接使用PostgreSQL生态插件。通过Docker容器、Git克隆代码、编译软件等步骤，即可完成PolarDB单机版的安装与配置。具体操作包括启动容器、进入容器、克隆代码、编译软件、初始化实例、配置参数及启动数据库。此外，还有多个相关教程和视频链接供参考，帮助用户更好地理解和使用PolarDB单机版。
德哥
964 1
德哥
|
存储 容灾 关系型数据库
PolarDB开源数据库进阶课11 激活容灾(Standby)节点
本文介绍了如何激活PolarDB容灾（Standby）节点，实验环境依赖于Docker容器中用loop设备模拟共享存储。通过`pg_ctl promote`命令可以将Standby节点提升为主节点，使其能够接收读写请求。激活后，原Standby节点不能再成为PolarDB集群的Standby节点。建议删除对应的复制槽位以避免WAL文件堆积。相关操作和配置请参考系列文章及视频教程。
德哥
322 1
德哥
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课13 单机版转换为集群版
本文介绍如何将“本地存储实例”转换为“共享存储实例”，依赖于先前搭建的实验环境。主要步骤包括：准备PFS二进制文件、格式化共享盘为pfs文件系统、启动pfsd服务、停库并拷贝数据到pfs内、修改配置文件，最后启动实例。通过这些操作，成功实现了从本地存储到共享存储的转换，并验证了新实例的功能。相关系列文章和视频链接提供了更多背景信息和技术细节。
德哥
365 0
体验瑶池数据库
|
8月前
|
人工智能 关系型数据库 MySQL
开源PolarDB-X：单节点误删除binlog恢复
本文由邵亚鹏撰写，分享了在使用开源PolarDB-X过程中，因误删binlog导致数据库服务无法启动的问题及恢复过程。作者结合实践经验，详细介绍了在无备份情况下如何通过单节点恢复机制重启数据库，并提出了避免类似问题的几点建议，包括采用高可用部署、定期备份及升级至最新版本等。
体验瑶池数据库
534 3
体验瑶池数据库
|
11月前
|
供应链 关系型数据库 分布式数据库
2025开源之夏火热报名｜一起来设计PolarDB Dashboard
2025开源之夏正在火热报名中，PolarDB邀请全球学子参与云原生与Web开发的前沿项目。活动由中国科学院软件研究所发起，旨在鼓励高校学生通过实际开发维护开源软件，培养优秀开发者，推动开源生态发展。PolarDB项目聚焦设计与开发PolarDB-X Dashboard，要求掌握K8S Client-go和Web开发技术。参与者将根据项目难度获得税前8000至12000元人民币报酬，并获取结项证书。每位学生仅可申请一个项目，详情见官网。
体验瑶池数据库
478 7
2025开源之夏火热报名｜一起来设计PolarDB Dashboard
考拉不拉
|
11月前
|
SQL 关系型数据库 分布式数据库
PolarDB开源数据库入门教程
PolarDB是阿里云推出的云原生数据库，基于PostgreSQL、MySQL和Oracle引擎构建，具备高性能、高扩展性和高可用性。其开源版采用计算与存储分离架构，支持快速弹性扩展和100%兼容PostgreSQL/MySQL。本文介绍了PolarDB的安装方法（Docker部署或源码编译）、基本使用（连接数据库、创建表等）及高级特性（计算节点扩展、存储自动扩容、并行查询等）。同时提供了性能优化建议和监控维护方法，帮助用户在生产环境中高效使用PolarDB。
考拉不拉
3208 21
PolarDB开源

PolarDB PostgreSQL 版

热门文章

最新文章

  • 1
    若依框架数据源切换为pg库
  • 2
    PolarDB开源新作：DuckDB-paimon，助力企业构建面向AI的多模数据底座
  • 3
    PolarDB 开源基础教程系列 3 安装部署
  • 4
    沉浸式学习PostgreSQL|PolarDB 9: AI大模型+向量数据库, 提升AI通用机器人在专业领域的精准度, 完美诠释柏拉图提出的“知识是回忆而不是知觉”
  • 5
    Curve 块存储实践 _ 十分钟带你体验高性能云原生数据库PolarDB
  • 6
    PG内核解读-第1节PostgreSQL系统概述
  • 7
    如何一键本地部署PolarDB for PostgreSQL
  • 8
    PolarDB开源数据库进阶课14 纯享单机版
  • 9
    PolarDB 开源版 使用PostGIS 数据寻龙点穴（空间聚集分析）- 大数据与GIS分析解决线下店铺选址问题
  • 10
    PolarDB开源数据库进阶课17 集成数据湖功能
  • 1
    PolarDB开源新作：DuckDB-paimon，助力企业构建面向AI的多模数据底座
    382
  • 2
    PolarDB开源数据库进阶课18 通过pg_bulkload适配pfs实现批量导入提速
    695
  • 3
    PolarDB开源数据库进阶课17 集成数据湖功能
    1032
  • 4
    PolarDB开源数据库进阶课16 接入PostGIS全功能及应用举例
    797
  • 5
    PolarDB开源数据库进阶课15 集成DeepSeek等大模型
    863
  • 6
    PolarDB开源数据库进阶课14 纯享单机版
    964
  • 7
    PolarDB开源数据库进阶课13 单机版转换为集群版
    365
  • 8
    PolarDB开源数据库进阶课12 集群版转换为单机版
    446
  • 9
    PolarDB开源数据库进阶课11 激活容灾(Standby)节点
    322
  • 10
    PolarDB开源数据库进阶课9 读写分离
    497

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • JSON 自学手册图文教程

    • 相关电子书

    更多
  • PolarDB开发者大会：PolarDB产品演进和未来展望
  • PolarDB+AnalyticDB助力交通物流行业系统升级
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL
    • 下一篇
    PolarDB MySQL 版 Serverless-测评