PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , json , gin , rum


背景

用户在使用JSON类型时,常见的一些JSON搜索包括:

1、存在,JSON中是否存在某个KEY,某些KEY,某些KEY的任意一个

存在某个KEY(TOP LEVEL)

'{"a":1, "b":2}'::jsonb ? 'b'  

存在所有KEY

'{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'c']  

存在任意KEY、元素

'["a", "b"]'::jsonb ?| array['a', 'b']  

2、等值,JSON中是否存在指定的key:value对(支持嵌套JSON)

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb  

3、包含,JSON中某个路径下的VALUE(数组)中,是否包含指定的所有元素。

postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';  
 ?column?   
----------  
 t  
(1 row)  

4、相交,JSON中某个路径下的VALUE(数组)中,是否包含指定的任意元素。

postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2]}}'   
or  
jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[3]}}'  
;  
  
 ?column?   
----------  
 t  
(1 row)  

或(注意1,2,3需要双引号,作为text类型存储,因为操作符?| ?&暂时只支持了text[],如果是numeric匹配不上)

postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?& array['2','3','4'] ;  
 ?column?   
----------  
 f  
(1 row)  
  
postgres=# select jsonb '{"a":1, "b": {"c":["1","2","3"], "d":["k","y","z"]}, "d":"kbc"}' -> 'b' -> 'c' ?| array['2','3','4'] ;  
 ?column?   
----------  
 t  
(1 row)  

5、范围查找,JSON中某个路径下的VALUE,是否落在某个范围内。

(js ->> 'key1' )::numeric between xx and xx  
  
(js ->> 'key2' )::numeric between xx and xx  

这些操作如何加速,或者如何使用索引加速?

一、json 索引支持

GIN的两个OPS,分别支持JSON:

The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.  
  
The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only.  

1、支持 @> 操作符的索引如下(jsonb_path_ops只支持@>操作符,但是效率高)

postgres=# create table tbl(id int, js jsonb);  
CREATE TABLE  
postgres=# create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);  
CREATE INDEX  

2、支持除范围查询以外的所有查询的索引如下

postgres=# create table tbl(id int, js jsonb);  
CREATE TABLE  
postgres=# create index idx_tbl_1 on tbl using gin (js);  -- 使用默认ops即可  
CREATE INDEX  

二、JSON KEY VALUE值范围查询加速

某些使用,需要对VALUE使用范围查询,比如时间(如果要建索引,请使用numeric表示,否则需要自定义immutable函数),数值都有这些需求。

通常的做法,把范围查询的类型提取出来,创建btree表达式索引,如果有任意组合的范围查询,使用gin或rum表达式索引。

例子

create index idx1 on tbl ( ((js->>'k1')::float8) );  
create index idx2 on tbl ( ((js->>'k2')::numeric) );  
...  
create index idxn on tbl ( ((js->>'kn')::float8) );  

create extension btree_gin;  
create index idx1 on tbl using gin( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );  

create extension rum;  
create index idx1 on tbl using rum( ((js->>'k1')::float8), ((js->>'k2')::numeric), ... ((js->>'kn')::float8) );  

create or replace function to_timestamp(text) returns timestamp as $$  
  select $1::timestamp;  
$$ language sql strict immutable;  
  
  
create index idx1 on tbl using gin( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );  
或  
create index idx1 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ... ((js->>'kn')::float8) );  

三、索引使用例子

create table tbl(id int, js jsonb);  
create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);  
create index idx_tbl_2 on tbl using gin (js);  
create index idx_tbl_3 on tbl using rum( ((js->>'k1')::float8), to_timestamp(js->>'k2'), ((js->>'k3')::numeric) );  
  
postgres=# explain select * from tbl where js ? 'a';  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.21..3.32 rows=1 width=36)  
   Recheck Cond: (js ? 'a'::text)  
   ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..2.21 rows=1 width=0)  
         Index Cond: (js ? 'a'::text)  
(4 rows)  
  
postgres=# explain select * from tbl where js @> '{"a":"b"}';  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.21..3.32 rows=1 width=36)  
   Recheck Cond: (js @> '{"a": "b"}'::jsonb)  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..2.21 rows=1 width=0)  
         Index Cond: (js @> '{"a": "b"}'::jsonb)  
(4 rows)  

postgres=# explain select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02';
                                                                                               QUERY PLAN                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tbl_3 on tbl  (cost=5.50..12.22 rows=6 width=36)
   Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text)) <= '2018-01-02 00:00:00'::timestamp without time zone))
(2 rows)

postgres=# explain select * from tbl where to_timestamp(js->>'k2') between '2018-01-01' and '2018-01-02' and ((js->>'k3')::numeric) between 1 and 200;
                                                                                                                                                     QUERY PLAN                                                                              
                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
 Index Scan using idx_tbl_3 on tbl  (cost=9.90..12.11 rows=1 width=36)
   Index Cond: ((to_timestamp((js ->> 'k2'::text)) >= '2018-01-01 00:00:00'::timestamp without time zone) AND (to_timestamp((js ->> 'k2'::text)) <= '2018-01-02 00:00:00'::timestamp without time zone) AND (((js ->> 'k3'::text))::numeric >
= '1'::numeric) AND (((js ->> 'k3'::text))::numeric <= '200'::numeric))
(2 rows)

  
postgres=# select * from tbl where js @> '{"a": {"b":"c"}}';  
 id | js   
----+----  
(0 rows)  
  
SELECT doc->'site_name' FROM websites  
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';  
  
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}';  
                               jsonb                                 
-------------------------------------------------------------------  
 {"a": 1, "b": {"c": [1, 2, 3], "d": ["k", "y", "z"]}, "d": "kbc"}  
(1 row)  
  
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,3]}}';  
 ?column?   
----------  
 t  
(1 row)  
  
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,4]}}';  
 ?column?   
----------  
 f  
(1 row)  
  
  
postgres=# explain select * from tbl where js @> '{"b":{"c":[2,4]}}';  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=3.31..4.42 rows=1 width=36)  
   Recheck Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)  
   ->  Bitmap Index Scan on idx_tbl_1  (cost=0.00..3.31 rows=1 width=0)  
         Index Cond: (js @> '{"b": {"c": [2, 4]}}'::jsonb)  
(4 rows)  

参考

https://www.postgresql.org/docs/devel/static/datatype-json.html

https://www.postgresql.org/docs/devel/static/functions-json.html

《PostgreSQL 电商业务(任意维度商品圈选应用) - json包range数组的命中优化 - 展开+索引优化》

《如何让json里面的value支持索引范围检索》

《PostgreSQL 11 preview - jsonb_plpython, jsonb_plperl 插件, PG类型jsonb与pyton,perl程序类型的相互转换》

《PostgreSQL 店铺运营实践 - JSON[]数组 内部标签数据等值、范围检索100倍+加速示例 (含,单值+多值列合成)》

《PostgreSQL json 任意位置 append 功能实现》

《多流实时聚合 - 记录级实时快照 - JSON聚合与json全文检索的功能应用》

《HTAP数据库 PostgreSQL 场景与性能测试之 46 - (OLTP) 大json字段的高并发更新》

《HTAP数据库 PostgreSQL 场景与性能测试之 32 - (OLTP) 高吞吐数据进出(堆存、行扫、无需索引) - 阅后即焚(JSON + 函数流式计算)》

《plpgsql 编程 - JSON数组循环》

《JSONB 压缩版本 ZSON》

《PostgreSQL 10.0 preview 功能增强 - SQL:2016标准(之SQL/JSON) Oracle 12c兼容》

《PostgreSQL 10.0 preview 功能增强 - JSON 内容全文检索》

《如何从PostgreSQL json中提取数组》

《PostgreSQL merge json的正确姿势》

《PostgreSQL json jsonb 支持的value数据类型,如何构造一个jsonb》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
12小时前
|
关系型数据库 MySQL 数据库
关系型数据库索引设计优化
【5月更文挑战第18天】
4 1
|
2天前
|
负载均衡 关系型数据库 分布式数据库
【PolarDB开源】PolarDB读写分离实践:优化读取性能与负载均衡策略
【5月更文挑战第26天】PolarDB是云原生关系型数据库,通过读写分离优化性能和扩展性。它设置主节点处理写操作,从节点处理读操作,异步复制保证数据一致性。优化读取性能的策略包括增加从节点数量、使用只读实例和智能分配读请求。负载均衡策略涉及基于权重、连接数和地理位置的分配。实践示例中,电商网站通过主从架构、只读实例和负载均衡策略提升商品查询效率。PolarDB的读写分离与负载均衡为企业应对大数据和高并发提供了有效解决方案。
35 0
|
4天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
22 1
|
5天前
|
存储 关系型数据库 MySQL
PolarDB-X 存储引擎核心技术 | 索引回表优化
数据库系统为了高效地存储、检索和维护数据,采用了多种不同的数据组织结构。不同的组织结构有其特定的用途和优化点,比如提高查询速度、优化写入性能、减少存储空间等,目前 PolarDB-X 采用了 B-Tree 的索引组织结构。
|
7天前
|
监控 关系型数据库 分布式数据库
【PolarDB 开源】PolarDB HTAP 实践:混合事务与分析处理的性能优化策略
【5月更文挑战第21天】PolarDB开源后在HTAP领域表现出色,允许在同一系统处理事务和分析工作负载,提高数据实时性。通过资源分配、数据分区、索引优化等策略提升性能。示例代码展示了创建和查询事务及分析表的基本操作。PolarDB还提供监控工具,帮助企业优化系统并应对业务变化。其HTAP能力为开发者和企业提供了强大支持,推动技术进步,加速数字化时代的业务发展。
28 1
|
13天前
|
存储 关系型数据库 分布式数据库
数据库索引回表困难?揭秘PolarDB存储引擎优化技术
PolarDB分布式版存储引擎采用CSM方案均衡资源开销与可用性。
数据库索引回表困难?揭秘PolarDB存储引擎优化技术
|
13天前
|
关系型数据库 数据库 索引
关系型数据库使用索引
关系型数据库使用索引
26 1
|
13天前
|
关系型数据库 大数据 数据库
关系型数据库索引优化
关系型数据库索引优化是一个综合的过程,需要综合考虑数据的特点、查询的需求以及系统的性能要求。通过合理的索引策略和技术,可以显著提高数据库的查询性能和整体效率。
24 4
|
13天前
|
SQL 关系型数据库 分布式数据库
数据库索引,一文揭秘PolarDB XPlan索引选择
深度解读PolarDB分布式版XPlan的索引选择
数据库索引,一文揭秘PolarDB XPlan索引选择
|
13天前
|
存储 JSON 关系型数据库
PostgreSQL Json应用场景介绍和Shared Detoast优化
PostgreSQL Json应用场景介绍和Shared Detoast优化

相关产品

  • 云原生数据库 PolarDB