7817人阅读
数据库（18）
文章出处：http://it.taocms.org/07/4097.htm
PostgreSQL官方关于JSON的文档：http://www.postgresql.org/docs/9.3/static/functions-json.html
摘要: 从PostgreSQL 9.3版本开始，JSON已经成为内置数据类型，“一等公民”啦。还在羡慕什么文档数据库或者BSON么，赶紧玩玩吧。另外9.4版本，提供JSONB（Binary），提供更多JSON函数和索引支持。刚好手头有一个需求，是涉及到数组类型的，...
从PostgreSQL 9.3版本开始，JSON已经成为内置数据类型，“一等公民”啦。
还在羡慕什么文档数据库或者BSON么，赶紧玩玩吧。另外9.4版本，提供JSONB（Binary），提供更多JSON函数和索引支持。
刚好手头有一个需求，是涉及到数组类型的，懒的插入多条数据库记录，想起了ARRAY数据类型。
常用的读取操作符目前大概有三类：-&、-&&和#&。还是直接看SQL查询的例子吧。
先看-&类：
postgres=# select '[1,2,3]'::json-&2;
----------
postgres=# select '{&a&:1,&b&:2}'::json-&'b';
----------
再来-&&例子：
postgres=# select '[1,2,3]'::json-&&2;
----------
postgres=# select '{&a&:1,&b&:2}'::json-&&'b';
----------
有没有发现其实-&和-&&出来的结果肉眼看起来是一样的？区别在于后者是返回text。
上面两个操作符实现了读取，其实大部分时候我们的JSON不是这么简单，会内嵌各种数组和哈希，这么读下去会死人的吧。当然，有一种类似path的读取，看例子吧：
postgres=# select '{&a&:[1,2,3],&b&:[4,5,6]}'::json#&'{a,2}';
----------
postgres=# select '{&a&:[1,2,3],&b&:[4,5,6]}'::json#&&'{a,2}';
----------
当然里面可以嵌套很多，比如{a,2,b,3}等等。下面再来点表的例子：
postgres=# create table testjson(id serial, data json);
postgres=# insert into testjson (data) values('{&a&: 1, &b&: 2}'::json);
postgres=# insert into testjson (data) values('{&a&: 3, &b&: 4, &c&: 5}'::json);
postgres=# insert into testjson (data) values('{&a&: 6, &c&: 7}'::json);
插入数据是不是很熟悉，基本和普通使用JSON一致，初窥下select结果：
postgres=# select * from testjson;
----+--------------------------
1 | {&a&: 1, &b&: 2}
2 | {&a&: 3, &b&: 4, &c&: 5}
3 | {&a&: 6, &c&: 7}
很眼熟，where条件可以这么用：
postgres=# select * from testjson where (data-&&'a')::int&1;
----+--------------------------
2 | {&a&: 3, &b&: 4, &c&: 5}
3 | {&a&: 6, &c&: 7}
注意这里是-&&转换成text然后在::int进行比较。
不过这里有个坑，不知道怎么解决，比如：
postgres=# insert into testjson (data) values('{&a&: &smallfish&}');
postgres=# select * from testjson;
----+--------------------------
1 | {&a&: 1, &b&: 2}
2 | {&a&: 3, &b&: 4, &c&: 5}
3 | {&a&: 6, &c&: 7}
5 | {&a&: &smallfish&}
这个时候上面的::int&1这样就报错了，因为最后一行a为字符串。
mongodb这个问题解决的挺好，回头搜搜在Pg里怎么搞。
话说，Pg里支持的JSON是不是和之前提到的类型有的一相似的地方？
两者优劣回头我再贴文吧。
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：311640次
积分：4176
积分：4176
排名：第7534名
原创：87篇
转载：101篇
评论：58条
(2)(1)(2)(2)(1)(1)(5)(1)(2)(2)(2)(1)(2)(1)(1)(1)(2)(5)(3)(6)(6)(2)(3)(3)(10)(4)(12)(12)(4)(7)(3)(6)(1)(27)(13)(4)(10)(16)(2)Postgres 9.4 重要特性：jsonb 数据类型 - 技术翻译 - 开源中国社区
Postgres 9.4 重要特性：jsonb 数据类型
【已翻译100%】
英文原文：
推荐于 3年前 (共 3 段, 翻译完成于 11-19)
参与翻译&(2人)&: ,
PostgreSQL 9.4 正在加载一项新功能叫jsonb，是一种新型资料，可以储存支援GIN索引的JSON 资料。换言之，此功能，在即将来临的更新中最重要的是，如果连这都不重要的话，那就把Postgres 置于文件为本数据库系统的推荐位置吧。
自从9.2开始，一个整合JSON 资料类型已经存在，带有一整套功能（例如资料产生和资料解构功能），还有9.3新增的操作者。当使用JSON 资料类型，资料的被存储成一完全一样的副本，功能还在此之上运作，还另外需要后台运作的重新分析。
这心得JSONB 资料类型以已降解的2元格式存储，所以，插入此资料会比JSON高效，因为后台不再需要重新分析，因此让它更快速运行，而且还兼顾GIN 索引。就是因为最后这个原因，我们实际上建议读者使用jsonb来代替json制作程式（当然你还可以因应需要而使用json）。请记住jsonb使用相同的操作者和功能，读者们可以看我之前的帖子去令你得到些什么启发（或者干脆看Postgres的文件）。
&翻译得不错哦！
现在让我们看一下JSONB是如何工作的，同时和JSON比较一下。采用的测试数据是860万的类型数据，大概1.1G大小，包括了城市名，国家代码（可以参见完整列表）等很多字段。首先通过底层复制(raw copy)来把这些数据存储到数据库的一个新表里面，之后把这张表通过一组填充因子是100的表转换成JSON/JSONB，之后来看它们各占多少空间。
=#©&geodata&FROM&'$HOME/Downloads/allCountries.txt';
COPY&8647839
=#&CREATE&TABLE&geodata_jsonb&(data&jsonb)&with&(fillfactor=100);
CREATE&TABLE
=#&CREATE&TABLE&geodata_json&(data&json)&with&(fillfactor=100);
CREATE&TABLE
=#&\timing
Timing&is&on.
=#&INSERT&INTO&geodata_json&SELECT&row_to_json(geodata)&FROM&
INSERT&0&8647839
=#&INSERT&INTO&geodata_jsonb&SELECT&row_to_json(geodata)::jsonb&FROM&
INSERT&0&8647839
生成JSONB数据花费稍微长一点时间，大小有没有区别呢？
=#&SELECT&pg_size_pretty(pg_relation_size('geodata_json'::regclass))&AS&json,
&&&&&&&&&&pg_size_pretty(pg_relation_size('geodata_jsonb'::regclass))&AS&
&&json&&&|&&jsonb&&
---------+---------
&3274&MB&|&3816&MB
在JSON数据上面做索引从9.3版本开始，比如用操作符（注意 因为它返回文本，所以'-&&'被采用；并且根据查询不同，索引采用不同的关键字）
=#&CREATE&INDEX&geodata_index&ON
&&&&geodata_json&((data-&&'country_code'),&(data-&&'asciiname'));
CREATE&INDEX
=#&SELECT&pg_size_pretty(pg_relation_size('geodata_index'::regclass))
&&&&AS&json_
&json_index&
------------
=#&SELECT&(data-&&'population')::int&as&population,
&&&&&&&&&&data-&'latitude'&as&latitude,
&&&&&&&&&&data-&'longitude'&as&longitude
&&&FROM&geodata_json&WHERE&data-&&'country_code'&=&'JP'&AND
&&&&&&&&data-&&'asciiname'&=&'Tokyo'&AND
&&&&&&&&(data-&&'population')::int&!=&0;
&population&|&latitude&|&longitude&
------------+----------+-----------
&&&&8336599&|&35.6895&&|&139.69171
=#&--&Explain&of&previous&query
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&QUERY&PLAN&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
-------------------------------------------------------------------------------------------------------------------------
&Bitmap&Heap&Scan&on&geodata_json&&(cost=6.78..865.24&rows=215&width=32)
&&&Recheck&Cond:&(((data&-&&&'country_code'::text)&=&'JP'::text)&AND&((data&-&&&'asciiname'::text)&=&'Tokyo'::text))
&&&Filter:&(((data&-&&&'population'::text))::integer&&&&0)
&&&-&&&Bitmap&Index&Scan&on&geodata_index&&(cost=0.00..6.72&rows=216&width=0)
&&&&&&&&&Index&Cond:&(((data&-&&&'country_code'::text)&=&'JP'::text)&AND&((data&-&&&'asciiname'::text)&=&'Tokyo'::text))
&Planning&time:&0.172&ms
在这个例子里，计划(planner)可以使用bitmap索引扫描，同时使用了之前产生的索引。
&翻译得不错哦！
现在，JSONB的一个新特点就是检查包含带有操作符@&的数据容量，这种数据是可以用GIN来索引的，这种操作符数据也包括了?，?|和?&(为了检查给定的关键字是否存在)。 GIN索引对两类操作符起作用：
缺省操作符类，之前列出的四个；
jsonb_hash_ops，仅支持@&，但是当搜索数据时性能表现不错，而且所占磁盘空间较小；
下面是它如何工作：
=#&CREATE&INDEX&geodata_gin&ON&geodata_jsonb
&&&&&&USING&GIN&(data&jsonb_hash_ops);
CREATE&INDEX
=#&SELECT&(data-&&'population')::int&as&population,
&&&&&&data-&'latitude'&as&latitude,
&&&&&&data-&'longitude'&as&longitude
&&&FROM&geodata_jsonb&WHERE&data&@&&'{"country_code":&"JP",&"asciiname":&"Tokyo"}'&AND
&&&&&&&(data-&&'population')::int&!=&0;
&population&|&latitude&|&longitude&
------------+----------+-----------
&&&&8336599&|&35.6895&&|&139.69171
&=#&SELECT&pg_size_pretty(pg_relation_size('geodata_gin'::regclass))&AS&jsonb_
&jsonb_gin
-----------
=#&--&EXPLAIN&of&previous&query
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&QUERY&PLAN&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
-------------------------------------------------------------------------------------
&Bitmap&Heap&Scan&on&geodata_jsonb&&(cost=131.01..31317.76&rows=8605&width=418)
&&&Recheck&Cond:&(data&@&&'{"asciiname":&"Tokyo",&"country_code":&"JP"}'::jsonb)
&&&Filter:&(((data&-&&&'population'::text))::integer&&&&0)
&&&-&&&Bitmap&Index&Scan&on&geodata_gin&&(cost=0.00..128.86&rows=8648&width=0)
&&&&&&&&&Index&Cond:&(data&@&&'{"asciiname":&"Tokyo",&"country_code":&"JP"}'::jsonb)
&&Planning&time:&0.134&ms
根据应用的需求，你或许想采用空间消耗低的索引，比如BTree建立在JSON数据上的索引类型；GIN索引有着更多的优点，因为它覆盖了所有的JSON字段，并且检查容量；
&翻译得不错哦！
我们的翻译工作遵照 ，如果我们的工作有侵犯到您的权益，请及时联系我们Postgresql 版本 ：9.5
CREATE TABLE person(id serial, info jsonb);
[{"num":"学号","name":"姓名","score":"成绩"},&{"num":"学号","name":"姓名","score":"成绩"}]
INSERT INTO person (info) VALUES ('[{"num":"1","name":"张三","score":"90"}]'::jsonb);
UPDATE person SET info = info || '[{"num":"2","name":"李四","score":"91"}]'::
SELECT t.* FROM person, jsonb_to_record(info) AS t(num text, name text, score text) WHERE person.id=1;
CREATE TABLE rt_person_info(no text, name text, score text);
SELECT t.* FROM person, jsonb_populate_recordset(null::rt_person_info, info) WHERE person.id=1;
SELECT * FROM person WHERE info @& '[{"num":"1"}]'::
UPDATE person t1 SET info = jsonb_set(info, array[(SELECT ORDINALITY::INT - 1 FROM person t2, jsonb_array_elements(info) WITH
ORDINALITY WHERE t1.id = t2.id AND value-&&'num' = '1')::text, 'score'::text], '"92"') WHERE id = '1'
阅读(...) 评论()温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
& & & & & json 特性的提升是9.4 的关键特性之一, 本人对于 json 的关注较少, 一方面由于之前版本的 json 并不十分成熟, 使用时需要配合使用外部模块如 PLV8, PLPerl 来弥补 JSON 功能的不足,一方面由于太懒没花精力研究; 但 9.4 版本的 JSON 功能完善很多, &jsonb 的出现带来了更多的函数, 更多的索引创建方式, 更多的操作符和更高的性能. 接下来通过一些例子来讲解, 希望更多的朋友能够了解并测试 PostgreSQL 的 json 功能.一 环境信息--1.1 之前写的关于 json 的博客&--1.2 测试环境硬件: 笔记本虚拟机系统: RHEL 6.2PG 版本: 9.4Beta1二生成测试数据--2.1 测试表user_ini: & & & & & & &基础数据表, 200 万数据.tbl_user_json: & &含有 json 数据类型表, 200 万数据tbl_user_jsonb: &含有 jsonb 数据类型表, &200 万数据--2.2 创建基础数据测试表francs=& create table user_ini(id int4 ,user_id int8, user_name character varying(64),create_time timestamp(6) with time zone default clock_timestamp());CREATE TABLEfrancs=& insert into user_ini(id,user_id,user_name) select r,round(random()*2000000), r || '_francs' from generate_series(1,2000000)INSERT 0 2000000备注: 生成 200 万测试数据.--2.3 生成 json 测试数据francs=& &create table tbl_user_json(id serial, user_info json);CREATE TABLEfrancs=& &insert into tbl_user_json(user_info) select row_to_json(user_ini) from user_INSERT 0 2000000Time:
ms--2.4 生成 jsonb 测试数据francs=& create table tbl_user_jsonb(id serial, user_info jsonb);CREATE TABLEfrancs=& insert into tbl_user_jsonb(user_info) select row_to_json(user_ini)::jsonb from user_ & &INSERT 0 2000000Time:
ms备注: &从时间来看, jsonb 插入速度比 json 插入速度稍慢, 再来看下两个表的大小如何?--2.5 比较表大小francs=& \dt+ tbl_user_json& & & & & & & & & & & &List of relations&Schema | & & Name & & &| Type &| Owner &| &Size &| Description&--------+---------------+-------+--------+--------+-------------&francs | tbl_user_json | table | francs | 269 MB |&(1 row)francs=& \dt+ tbl_user_jsonb& & & & & & & & & & & & List of relations&Schema | & & &Name & & &| Type &| Owner &| &Size &| Description&--------+----------------+-------+--------+--------+-------------&francs | tbl_user_jsonb | table | francs | 329 MB |&(1 row)--2.6 查看几条测试数据francs=& select * from tbl_user_jsonb limit 3;&id | & & & & & & & & & & & & & & & & & & & & & & & user_info & & & & & & & & & & & & & & & & & & & & & & &&----+-------------------------------------------------------------------------------------------------------& 1 | {"id": 1, "user_id": 116179, "user_name": "1_francs", "create_time": " 11:54:38."}& 2 | {"id": 2, "user_id": 956659, "user_name": "2_francs", "create_time": " 11:54:38."}& 3 | {"id": 3, "user_id": 1017031, "user_name": "3_francs", "create_time": " 11:54:38.37344+00"}(3 rows)备注: 以上是生成的测试数据, 列几条出来,方便查阅, 接下来看一个查询.三 基于 jsonb 字段 key 值的检索效率--3.1 根据 user_info 字段的 user_name key 检索&francs=& &select * from tbl_user_jsonb where user_info-&&'user_name'= '1_francs'; & &&id | & & & & & & & & & & & & & & & & & & & & & & & user_info & & & & & & & & & & & & & & & & & & & & & & &&----+-------------------------------------------------------------------------------------------------------& 1 | {"id": 1, "user_id": 116179, "user_name": "1_francs", "create_time": " 11:54:38."}(1 row)--3.2 执行计划和执行时间francs=& explain analyze select * from tbl_user_jsonb where user_info-&&'user_name'= '1_francs'; && & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & &---------------------------------------------------------------------------------------------------------------------&Seq Scan on tbl_user_jsonb &(cost=0.00..72097.82 rows=10000 width=140) (actual time=0.033.. rows=1 loops=1)& &Filter: ((user_info -&& 'user_name'::text) = '1_francs'::text)& &Rows Removed by Filter: 1999999&Planning time: 1.657 ms&Execution time:
ms(5 rows)备注: &此时还没建索引,走的全表扫, 花了将近 3 秒.--3.3 创建索引francs=& create index idx_gin_user_infob_user_name on tbl_user_jsonb using btree ((user_info -&& 'user_name'));CREATE INDEX--3.4 再次查看 &planfrancs=& explain analyze select * from tbl_user_jsonb where user_info-&&'user_name'= '1_francs'; && & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &&-------------------------------------------------------------------------------------------------------------------------------------------&Bitmap Heap Scan on tbl_user_jsonb &(cost=233.93..23782.62 rows=10000 width=140) (actual time=0.046..0.047 rows=1 loops=1)& &Recheck Cond: ((user_info -&& 'user_name'::text) = '1_francs'::text)& &Heap Blocks: exact=1& &-& &Bitmap Index Scan on idx_gin_user_infob_user_name &(cost=0.00..231.43 rows=10000 width=0) (actual time=0.035..0.035 rows=1 loops=1)& & & & &Index Cond: ((user_info -&& 'user_name'::text) = '1_francs'::text)&Planning time: 0.144 ms&Execution time: 0.101 ms(7 rows)备注: 创建索引后, &上述查询走了索引, 仅花 &0.101 ms 完成检索, 挺给力!--3.5 根据 user_info 字段的 user_id 检索francs=& explain analyze select * from tbl_user_jsonb where user_info-&&'user_id'= '1'; && & & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & &&------------------------------------------------------------------------------------------------------------------------&Seq Scan on tbl_user_jsonb &(cost=0.00..72098.00 rows=10000 width=140) (actual time=89.888 rows=1 loops=1)& &Filter: ((user_info -&& 'user_id'::text) = '1'::text)& &Rows Removed by Filter: 1999999&Planning time: 3.304 ms&Execution time:
ms(5 rows)Time:
ms备注: 没走索引,花了 4 秒多,因为没建这个 key 上的索引.四 使用 GIN 索引& & &可以给 jsonb 字段创建 GIN 索引, GIN 索引有两种模式, 默认模式支持 &@&, ?, ?& 和 ?| 的索引查询, 我们这里使用默认模式.--4.1 删除之前索引,新建 gin 索引francs=& create index idx_tbl_user_jsonb_user_Info on tbl_user_jsonb using gin (user_Info);CREATE INDEXTime:
msfrancs=& \di+ idx_tbl_user_jsonb_user_Info& & & & & & & & & & & & & & & & & & & &List of relations&Schema | & & & & & & Name & & & & & & | Type &| Owner &| & & Table & & &| &Size &| Description&--------+------------------------------+-------+--------+----------------+--------+-------------&francs | idx_tbl_user_jsonb_user_info | index | francs | tbl_user_jsonb | 428 MB |&(1 row)备注: 索引很大,创建很慢,一般不会这么建索引.--4.2 基于 key/value 检索可以使用索引francs=& explain analyze select * from tbl_user_jsonb where user_info @& '{"user_id": 1017031}';& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &-----------------------------------------------------------------------------------------------------------------------------------------&Bitmap Heap Scan on tbl_user_jsonb &(cost=59.50..6637.58 rows=2000 width=140) (actual time=0.340..0.345 rows=1 loops=1)& &Recheck Cond: (user_info @& '{"user_id": 1017031}'::jsonb)& &Rows Removed by Index Recheck: 1& &Heap Blocks: exact=2& &-& &Bitmap Index Scan on idx_tbl_user_jsonb_user_info &(cost=0.00..59.00 rows=2000 width=0) (actual time=0.319..0.319 rows=2 loops=1)& & & & &Index Cond: (user_info @& '{"user_id": 1017031}'::jsonb)&Planning time: 0.118 ms&Execution time: 0.391 ms(8 rows)--4.3 以下查询不走索引francs=& explain analyze select * from tbl_user_jsonb where user_info-&&'user_name' ='4_francs';& & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & &---------------------------------------------------------------------------------------------------------------------&Seq Scan on tbl_user_jsonb &(cost=0.00..72098.00 rows=10000 width=140) (actual time=0.036.. rows=1 loops=1)& &Filter: ((user_info -&& 'user_name'::text) = '4_francs'::text)& &Rows Removed by Filter: 1999999&Planning time: 1.101 ms&Execution time:
ms(5 rows)francs=& explain analyze select * from tbl_user_jsonb where user_info-&'user_name' ?'4_francs';&& & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & &&--------------------------------------------------------------------------------------------------------------------&Seq Scan on tbl_user_jsonb &(cost=0.00..72098.00 rows=2000 width=140) (actual time=0.187.. rows=1 loops=1)& &Filter: ((user_info -& 'user_name'::text) ? '4_francs'::text)& &Rows Removed by Filter: 1999999&Planning time: 0.382 ms&Execution time:
ms(5 rows)备注: 以上的 ? 操作没走索引, 但 ? 操作支持索引检索,创建以下索引.--4.4 删除之前索引并新建以下索引francs=& create index idx_gin_user_info_user_name on tbl_user_jsonb using gin((user_info -& 'user_name'));CREATE INDEXfrancs=& explain analyze select * from tbl_user_jsonb where user_info-&'user_name' ?'4_francs';& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &&----------------------------------------------------------------------------------------------------------------------------------------&Bitmap Heap Scan on tbl_user_jsonb &(cost=35.50..6618.58 rows=2000 width=140) (actual time=0.067..0.069 rows=1 loops=1)& &Recheck Cond: ((user_info -& 'user_name'::text) ? '4_francs'::text)& &Heap Blocks: exact=1& &-& &Bitmap Index Scan on idx_gin_user_info_user_name &(cost=0.00..35.00 rows=2000 width=0) (actual time=0.037..0.037 rows=1 loops=1)& & & & &Index Cond: ((user_info -& 'user_name'::text) ? '4_francs'::text)&Planning time: 0.151 ms&Execution time: 0.129 ms(7 rows)备注: 速度很快.五 对比 json 和 jsonb 的检索性能& & & &文档上提到了 jsonb 的检索效率要高于 json 的检索效率, 下面通过例子测试.& &&--5.1 删除之前创建的所有索引并创建函数索引francs=& create index idx_gin_user_info_id on tbl_user_json using btree (((user_info -&& 'id')::integer)); &CREATE INDEXfrancs=& create index idx_gin_user_infob_id on tbl_user_jsonb using btree (((user_info -&& 'id')::integer)); &CREATE INDEX备注: 为什么使用函数索引? &由于 --& 操作返回的是 text 类型, &接下来的查询会用到 id 字段比较, 需要转换成整型.--5.2 json 表范围扫描francs=& &explain analyze select id,user_info-&'id',user_info-&'user_name' from tbl_user_json where (user_info-&&'id')::int4 & '1' and (user_info-&&'id')::int4 & '10000';&& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &--------------------------------------------------------------------------------------------------------------------------------------&Bitmap Heap Scan on tbl_user_json &(cost=190.94..22275.60 rows=10000 width=36) (actual time=2.417..60.585 rows=9998 loops=1)& &Recheck Cond: ((((user_info -&& 'id'::text))::integer & 1) AND (((user_info -&& 'id'::text))::integer & 10000))& &Heap Blocks: exact=167& &-& &Bitmap Index Scan on idx_gin_user_info_id &(cost=0.00..188.44 rows=10000 width=0) (actual time=2.329..2.329 rows=9998 loops=1)& & & & &Index Cond: ((((user_info -&& 'id'::text))::integer & 1) AND (((user_info -&& 'id'::text))::integer & 10000))&Planning time: 0.183 ms&Execution time: 64.116 ms(7 rows)--5.3 jsonb 表范围扫描francs=& &explain analyze select id,user_info-&'id',user_info-&'user_name' from tbl_user_jsonb where (user_info-&&'id')::int4 & '1' and (user_info-&&'id')::int4 & '10000';&& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & QUERY PLAN & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &&---------------------------------------------------------------------------------------------------------------------------------------&Bitmap Heap Scan on tbl_user_jsonb &(cost=190.94..23939.63 rows=10000 width=140) (actual time=2.593..24.308 rows=9998 loops=1)& &Recheck Cond: ((((user_info -&& 'id'::text))::integer & 1) AND (((user_info -&& 'id'::text))::integer & 10000))& &Heap Blocks: exact=197& &-& &Bitmap Index Scan on idx_gin_user_infob_id &(cost=0.00..188.44 rows=10000 width=0) (actual time=2.494..2.494 rows=9998 loops=1)& & & & &Index Cond: ((((user_info -&& 'id'::text))::integer & 1) AND (((user_info -&& 'id'::text))::integer & 10000))&Planning time: 0.142 ms&Execution time: 27.851 ms(7 rows)备注: 这里实验发现, jsonb 检索确实比 json 要快很多, &而本文开头插入数据时 jsonb 比 json 稍慢, 这也正好验证了 "jsonb 写入比 json 慢,但检索较 json 快的说法.", 我在之前的博客 && 有提到过.--六 附&&6.1&json&and&jsonb&OperatorsOperatorRight Operand TypeDescriptionExampleExample Result-&intGet JSON array element (indexed from zero)'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json-&2{"c":"baz"}-&textGet JSON object field by key'{"a": {"b":"foo"}}'::json-&'a'{"b":"foo"}-&&intGet JSON array element as&text'[1,2,3]'::json-&&23-&&textGet JSON object field as&text'{"a":1,"b":2}'::json-&&'b'2#&text[]Get JSON object at specified path'{"a": {"b":{"c": "foo"}}}'::json#&'{a,b}'{"c": "foo"}#&&text[]Get JSON object at specified path as&text'{"a":[1,2,3],"b":[4,5,6]}'::json#&&'{a,2}'36.2&&Additional&jsonb&OperatorsOperatorRight Operand TypeDescriptionExample=jsonbAre the two JSON values equal?'[1,2,3]'::jsonb = '[1,2,3]'::jsonb@&jsonbDoes the left JSON value contain within it the right value?'{"a":1, "b":2}'::jsonb @& '{"b":2}'::jsonb&@jsonbIs the left JSON value contained within the right value?'{"b":2}'::jsonb &@ '{"a":1, "b":2}'::jsonb?textDoes the key/element&string&exist within the JSON value?'{"a":1, "b":2}'::jsonb ? 'b'?|text[]Do any of these key/element&strings&exist?'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']?&text[]Do all of these key/element&strings&exist?'["a", "b"]'::jsonb ?& array['a', 'b']--七 参考
阅读(13284)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'PostgreSQL9.4:
性能测试',
blogAbstract:'& & & & & json 特性的提升是9.4 的关键特性之一, 本人对于 json 的关注较少, 一方面由于之前版本的 json 并不十分成熟, 使用时需要配合使用外部模块如 PLV8, PLPerl 来弥补 JSON 功能的不足,一方面由于太懒没花精力研究; 但 9.4 版本的 JSON 功能完善很多, &jsonb 的出现带来了更多的函数, 更多的索引创建方式, 更多的操作符和更高的性能. 接下来通过一些例子来讲解, 希望更多的朋友能够了解并测试 PostgreSQL 的 json 功能.一 环境信息--1.1 之前写的关于 json 的博客&',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:3,
publishTime:6,
permalink:'blog/static/',
commentCount:1,
mainCommentCount:1,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:true,
hostIntro:'',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}
