相关文章推荐
伤情的咖啡  ·  AI ...·  11 月前    · 
痴情的铁链  ·  【漫画推荐】ratatatat74云顶_哔哩 ...·  1 年前    · 
才高八斗的双杠  ·  徐麟在贵州磷化集团调研 - 贵州省人民政府·  1 年前    · 
温柔的针织衫  ·  荒野赤子-🌈️包子漫畫·  2 年前    · 
腼腆的松树  ·  迈凯伦史上首款V6混动超跑 ...·  2 年前    · 
Code  ›  PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速-阿里云开发者社区
https://developer.aliyun.com/article/647475
暴走的苹果
1 年前
备案 控制台
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议 》和 《 阿里云开发者社区知识产权保护指引 》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单 进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

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

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

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

postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2]}}'   
jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[3]}}'  
 ?column?   
----------  
(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?   
----------  
(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?   
----------  
(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?   
----------  
(1 row)  
postgres=# select jsonb '{"a":1, "b": {"c":[1,2,3], "d":["k","y","z"]}, "d":"kbc"}' @> '{"b":{"c":[2,4]}}';  
 ?column?   
----------  
(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》
 
推荐文章
伤情的咖啡  ·  AI 博士招聘，想知道在弗吉尼亚大学就读是怎样的体验吗？|哈佛大学|耶鲁大学|卡内基梅隆大学_网易订阅
11 月前
痴情的铁链  ·  【漫画推荐】ratatatat74云顶_哔哩哔哩_bilibili
1 年前
才高八斗的双杠  ·  徐麟在贵州磷化集团调研 - 贵州省人民政府
1 年前
温柔的针织衫  ·  荒野赤子-🌈️包子漫畫
2 年前
腼腆的松树  ·  迈凯伦史上首款V6混动超跑 238万你买账吗? - 知乎
2 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号