1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL9.4: Jsonb 性能测试

• 数据库

json 特性的提升是9.4 的关键特性之一, 本人对于 json 的关注较少, 一方面由于之前版本的 json 并不十分成熟, 使用时需要配合使用外部模块如 PLV8, PLPerl 来弥补 JSON 功能的不足,一方面由于太懒没花精力研究; 但 9.4 版本的 JSON 功能完善很多, jsonb 的出现带来了更多的函数, 更多的索引创建方式, 更多的操作符和更高的性能. 接下来通过一些例子来讲解, 希望更多的朋友能够了解并测试 PostgreSQL 的 json 功能.

之前写的关于 json 的博客

环境准备

1.2 测试环境
硬件: 笔记本虚拟机
系统: RHEL 6.2
PG 版本: 9.4Beta1

生成测试数据
2.1 测试表
user_ini: 基础数据表, 200 万数据.
tbl_user_json: 含有 json 数据类型表, 200 万数据
tbl_user_jsonb: 含有 jsonb 数据类型表, 200 万数据

2.2 创建基础数据测试表

1
2
3
4
5
 
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 万测试数据.

2.3 生成 json 测试数据

1
2
3
4
5
6
 
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 ms

2.4 生成 jsonb 测试数据

1
2
3
4
5
6
 
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 比较表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
 
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 查看几条测试数据

1
2
3
4
5
6
7
 
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 检索

1
2
3
4
5
 
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 执行计划和执行时间

1
2
3
4
5
6
7
8
9
 
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 创建索引

1
2
 
francs=> create index idx_gin_user_infob_user_name on tbl_user_jsonb using btree ((user_info ->>  'user_name'));
CREATE INDEX

3.4 再次查看 plan

1
2
3
4
5
6
7
8
9
10
11
 
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 检索

1
2
3
4
5
6
7
8
9
10
 
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 索引

1
2
3
4
5
6
7
8
9
10
 
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 检索可以使用索引

1
2
3
4
5
6
7
8
9
10
11
12
 
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 以下查询不走索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
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 删除之前索引并新建以下索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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 删除之前创建的所有索引并创建函数索引

1
2
3
4
5
 
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 表范围扫描

1
2
3
4
5
6
7
8
9
10
11
 
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 表范围扫描

1
2
3
4
5
6
7
8
9
10
11
 
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 数据类型 有提到过.

参考

原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/238074.html

赞 (0)
0
上一篇 2022年1月30日
下一篇 2022年1月30日

相关推荐

发表回复

登录后才能评论