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

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

2022-12-29 620
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
竹相_左小空空
|
JSON 人工智能 数据可视化
实用工具推荐第二期结合deepseek的思维导图开源免费工具 JSON 可视化工具
JSON 数据可视化为交互式的树形图或图形，帮助直观理解文件结构和模块之间的联系。
竹相_左小空空
1860 19
小猪课堂
|
人工智能 JSON 搜索推荐
猫步简历 - 开源免费AI简历生成器 | 一键导出PDF/JSON
猫步简历是一款免费开源的AI简历生成器，帮助用户轻松创建独特、专业的简历。支持导出超高清PDF、图片、JSON等多种格式，并提供AI智能创作、润色和多语种切换等功能。拥有海量模板、高度定制化模块及完善的后台管理系统，助力求职者脱颖而出。官网：https://maobucv.com，GitHub开源地址：https://github.com/Hacker233/resume-design。
小猪课堂
5342 10
蓝易云
|
JSON 前端开发 JavaScript
php中JSON或数组到formData的键值对转换
转换JSON或数组到formData格式的键值对并不复杂。PHP的 `json_decode()`与 `http_build_query()`是实现这一转换过程的关键函数。理解这个转换过程对于开发中处理各种AJAX请求时调整数据格式至关重要。这样，无论是处理来自客户端的JSON字符串，还是服务器端的数组数据，都能够灵活地转换为适合网络传输的格式，确保数据交换的顺畅和高效。
蓝易云
433 4
好奇的菜鸟
|
JSON 关系型数据库 MySQL
MySQL中GROUP_CONCAT与JSON_OBJECT、GROUP BY的巧妙结合：打造高效JSON数组汇总
MySQL中GROUP_CONCAT与JSON_OBJECT、GROUP BY的巧妙结合：打造高效JSON数组汇总
好奇的菜鸟
1092 1
土木林森
|
SQL JSON 关系型数据库
"SQL老司机大揭秘：如何在数据库中玩转数组、映射与JSON，解锁数据处理的无限可能，一场数据与技术的激情碰撞！"
【8月更文挑战第21天】SQL作为数据库语言，其能力不断进化，尤其是在处理复杂数据类型如数组、映射及JSON方面。例如，PostgreSQL自8.2版起支持数组类型，并提供`unnest()`和`array_agg()`等函数用于数组的操作。对于映射类型，虽然SQL标准未直接支持，但通过JSON数据类型间接实现了键值对的存储与查询。如在PostgreSQL中创建含JSONB类型的表，并使用`->>`提取特定字段或`@>`进行复杂条件筛选。掌握这些技巧对于高效管理现代数据至关重要，并预示着SQL在未来数据处理领域将持续扮演核心角色。
土木林森
572 0
VipSoft
|
JSON JavaScript 数据格式
Jquery 将 JSON 列表的 某个属性值，添加到数组中，并判断一个值，在不在数据中
Jquery 将 JSON 列表的 某个属性值，添加到数组中，并判断一个值，在不在数据中
VipSoft
343 0
花开富贵111
|
JSON 资源调度 Kubernetes
实时计算 Flink版操作报错合集之解析JSON数组时，遇到报错，该怎么解决
在使用实时计算Flink版过程中，可能会遇到各种错误，了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题，查看Flink的日志是关键，它们通常会提供更详细的错误信息和堆栈跟踪，有助于定位问题。此外，Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
花开富贵111
496 0
正在走向自律
|
JSON JavaScript 数据格式
1.js动态的往json数据添加新属性和值 2.JSON 和 JS 对象互转 3.对象转化为数组
1.js动态的往json数据添加新属性和值 2.JSON 和 JS 对象互转 3.对象转化为数组
正在走向自律
335 0
嘟嘟嘟嘟嘟嘟
|
SQL JSON 监控
实时计算 Flink版产品使用合集之直接将 JSON 字符串解析为数组的内置函数如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
嘟嘟嘟嘟嘟嘟
1145 0
技术交流18179014480
|
JSON API 数据格式
淘宝拍立淘按图搜索API系列，json数据返回
淘宝拍立淘按图搜索API系列通过图像识别技术实现商品搜索功能，调用后返回的JSON数据包含商品标题、图片链接、价格、销量、相似度评分等核心字段，支持分页和详细商品信息展示。以下是该API接口返回的JSON数据示例及详细解析：
技术交流18179014480
1074 136
PolarDB开源

PolarDB PostgreSQL 版

热门文章

最新文章

  • 1
    PolarDB 开源基础教程系列 3 安装部署
  • 2
    PolarDB 开源基础教程系列 7.4 应用实践之 AI大模型外脑
  • 3
    PolarDB for PostgreSQL 开源必读手册-用户和权限管理（中）
  • 4
    若依框架数据源切换为pg库
  • 5
    PolarDB开源新作：DuckDB-paimon，助力企业构建面向AI的多模数据底座
  • 6
    PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍（中）
  • 7
    PolarDB开源数据库进阶课17 集成数据湖功能
  • 8
    PolarDB开源数据库进阶课16 接入PostGIS全功能及应用举例
  • 9
    PolarDB开源数据库进阶课12 集群版转换为单机版
  • 10
    [版本更新]PolarDB-X v2.2.1 生产级关键能力开源升级
  • 1
    PolarDB开源新作：DuckDB-paimon，助力企业构建面向AI的多模数据底座
    1024
  • 2
    PolarDB开源数据库进阶课18 通过pg_bulkload适配pfs实现批量导入提速
    972
  • 3
    PolarDB开源数据库进阶课17 集成数据湖功能
    1441
  • 4
    PolarDB开源数据库进阶课16 接入PostGIS全功能及应用举例
    1052
  • 5
    PolarDB开源数据库进阶课15 集成DeepSeek等大模型
    1230
  • 6
    PolarDB开源数据库进阶课14 纯享单机版
    1414
  • 7
    PolarDB开源数据库进阶课13 单机版转换为集群版
    539
  • 8
    PolarDB开源数据库进阶课12 集群版转换为单机版
    762
  • 9
    PolarDB开源数据库进阶课11 激活容灾(Standby)节点
    523
  • 10
    PolarDB开源数据库进阶课9 读写分离
    681

    • 相关产品

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

    • 相关课程

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

    • 相关电子书

    更多
  • PolarDB+AnalyticDB助力交通物流行业系统升级
  • PolarDB NL2SQL: 帮助您写出准确、优化的SQL
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设

    • 相关实验场景

    更多
  • 使用PolarDB和ECS搭建门户网站
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！