| Postgres2015全国用户大会--重磅嘉宾佳作分享（谭峰） |
doudou586 发布于2015-11-12 19:08:48
 评论: 3
 浏览: 7225
 顶: 909
 踩: 998
|
友情提示：Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。
报名链接：http://postgres2015.eventdove.com/
|
本期热点嘉宾：谭峰
网名：francs，PostgreSQL 中文社区版主，《PostgreSQL 9 Admin Cookbook》译者之一，热衷于博客分享技术心得，致力于 PostgreSQL 中文技术推广，曾任杭州斯凯网络科技有限公司 PostgreSQL DBA 一职，维护 100+ PostgreSQL 实例，超过 5 年的 PostgreSQL 数据库运维经验，现就职于中国移动通信集团浙江有限公司，从事开源数据库管理。 |
嘉宾佳作:PostgreSQL9.4: jsonb 性能测试
json 特性的提升是9.4 的关键特性之一, 本人对于 json 的关注较少, 一方面由于之前版本的 json 并不十分成熟, 使用时需要配合使用外部模块如 PLV8, PLPerl 来弥补 JSON 功能的不足,一方面由于太懒没花精力研究; 但 9.4 版本的 JSON 功能完善很多, jsonb 的出现带来了更多的函数, 更多的索引创建方式, 更多的操作符和更高的性能. 接下来通过一些例子来讲解, 希望更多的朋友能够了解并测试 PostgreSQL 的 json 功能.
一、环境信息
1.1 之前写的关于 json 的博客二、生成测试数据
2.1 测试表user_ini: 基础数据表, 200 万数据. tbl_user_json: 含有 json 数据类型表, 200 万数据 tbl_user_jsonb: 含有 jsonb 数据类型表, 200 万数据2.2 创建基础数据测试表
2.3 生成 json 测试数据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 TABLE francs=> insert into user_ini(id,user_id,user_name) select r,round(random()*2000000), r || '_francs' from generate_series(1,2000000) as r; INSERT 0 2000000 备注: 生成 200 万测试数据。
francs=> create table tbl_user_json(id serial, user_info json); CREATE TABLE francs=> insert into tbl_user_json(user_info) select row_to_json(user_ini) from user_ini; INSERT 0 2000000 Time: 63469.336 ms2.4 生成 jsonb 测试数据
francs=> create table tbl_user_jsonb(id serial, user_info jsonb); CREATE TABLE francs=> insert into tbl_user_jsonb(user_info) select row_to_json(user_ini)::jsonb from user_ini; INSERT 0 2000000 Time: 78300.553 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": "2014-06-21 11:54:38.371774+00"}
2 | {"id": 2, "user_id": 956659, "user_name": "2_francs", "create_time": "2014-06-21 11:54:38.373425+00"}
3 | {"id": 3, "user_id": 1017031, "user_name": "3_francs", "create_time": "2014-06-21 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": "2014-06-21 11:54:38.371774+00"}
(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..2965.837 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: 2966.380 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 INDEX3.4 再次查看 plan
francs=> 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=2483.198..4289.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: 4292.158 ms (5 rows) Time: 4321.349 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 INDEX Time: 214253.873 ms francs=> \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..4640.794 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: 4640.851 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..5387.658 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: 5387.762 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 INDEX francs=> 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 INDEX francs=> 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 快的说法.", 我在之前的博客"PostgreSQL 9.4: 新增 JSONB 数据类型"有提到过.
六、附录
6.1 json and jsonb Operators| Operator | Right Operand Type | Description | Example Example Result |
|---|---|---|---|
| -> | int | Get JSON array element (indexed from zero) | '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"} |
| -> | text | Get JSON object field by key | '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"} |
| ->> | int | Get JSON array element as text | '[1,2,3]'::json->>2 3 |
| ->> | text | Get 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}' 3 |
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
| = | jsonb | Are the two JSON values equal? | '[1,2,3]'::jsonb = '[1,2,3]'::jsonb |
| @> | jsonb | Does the left JSON value contain within it the right value? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
| <@ | jsonb | Is the left JSON value contained within the right value? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
| ? | text | Does 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'] |
七、参考
|
请在登录后发表评论，否则无法保存。
1# __
xcvxcvsdf 回答于 2024-11-11 14:51:58+08
http://huilong.sctcbmw.cn/sdyt/
http://ouyu.hftcbmw.cn/tjhx/
https://jinshancb.tiancebbs.cn/
http://huilong.sctcbmw.cn/yanbian/
http://cf.lstcxxw.cn/yaan/
http://tuiguang.hntcxxw.cn/qgqzw/
http://fuyang.tjtcbmw.cn/kaifeng/
http://tuiguang.hntcxxw.cn/xunwu/
https://songjiangzb.tiancebbs.cn/
https://sanzao.tiancebbs.cn/
http://ouyu.hftcbmw.cn/gxlb/
http://js.sytcxxw.cn/dnzr/
http://js.sytcxxw.cn/ahtl/
https://jhqjiangnan.tiancebbs.cn/
http://huaguang.jxtcbmw.cn/zhabeish/
http://yz.cqtcxxw.cn/dyjz/
https://hongcaozhen.tiancebbs.cn/
2# __
xcvxcvsdf 回答于 2024-10-29 17:12:28+08
https://aihuishou.tiancebbs.cn/sh/4413.html
https://zulin.tiancebbs.cn/sh/2053.html
https://zulin.tiancebbs.cn/sh/3965.html
https://su.tiancebbs.cn/hjzl/469114.html
https://su.tiancebbs.cn/hjzl/462258.html
https://aihuishou.tiancebbs.cn/sh/2851.html
https://zulin.tiancebbs.cn/sh/4928.html
https://su.tiancebbs.cn/hjzl/457898.html
https://su.tiancebbs.cn/hjzl/456500.html
https://aihuishou.tiancebbs.cn/sh/2972.html
https://aihuishou.tiancebbs.cn/sh/3858.html
https://sl.tiancebbs.cn/qths/469266.html
https://www.tiancebbs.cn/ershouwang/474350.html
https://aihuishou.tiancebbs.cn/sh/863.html
https://jc.tiancebbs.cn/qths/472349.html
https://www.tiancebbs.cn/ershoufang/471251.html
https://www.tiancebbs.cn/ershoufang/473087.html
3# __
xiaowu 回答于 2024-04-25 19:35:38+08
适合女生学的专业:https://www.henkao.com/zhichang/388.html 体育工作总结:https://www.henkao.com/zhichang/237.html 对母亲的祝福:https://www.henkao.com/qinggan/1653.html 简历自我评价:https://www.henkao.com/zhichang/2438.html 假如我会飞:https://www.henkao.com/zuowen/1751.html 一大串美食名字:https://www.henkao.com/wangluo/813.html 红楼梦读后感2000字:https://www.henkao.com/xuexi/1915.html 头发少怎么增加发量:https://www.henkao.com/meiyan/529.html 充满阳光的昵称:https://www.henkao.com/wangluo/1518.html 英文游戏名:https://www.henkao.com/wangluo/741.html 感恩母爱最朴实的句子:https://www.henkao.com/qinggan/450.html 幼儿园教师述职报告:https://www.henkao.com/shuzhi/2640.html 鹿肉的营养价值:https://www.henkao.com/yinshi/946.html 植树节作文:https://www.henkao.com/xuexi/308.html 职场励志语录:https://www.henkao.com/zhichang/15.html 工作能力总结:https://www.henkao.com/zhichang/2522.html 瀑布像什么:https://www.henkao.com/xuexi/834.html 非主流情侣网名:https://www.henkao.com/wangluo/1181.html 中秋节活动策划方案:https://www.henkao.com/zhichang/2166.html 好词好句好段摘抄大全:https://www.henkao.com/xuexi/592.html 社会实践总结:https://www.henkao.com/zhichang/226.html 垃圾分类倡议书:https://www.henkao.com/xuexi/1134.html 炫舞名字:https://www.henkao.com/wangluo/1276.html 安静的网名:https://www.henkao.com/wangluo/1200.html 闲不住的奶奶:https://www.henkao.com/zuowen/1749.html 牛的网名:https://www.henkao.com/wangluo/1175.html 敬老院活动方案:https://www.henkao.com/zhichang/2176.html 一个字的高冷网名:https://www.henkao.com/wangluo/941.html 变形计作文:https://www.henkao.com/zuowen/1770.html 藏红花的功效与作用:https://www.henkao.com/yinshi/950.html
