PG json/jsonb简单性能测试

读写性能测试：

• 200万行数据写入对比：

postgres@findb:create table user_ini(id int4,user_id int8,user_name varchar(64),create_time timestamp default clock_timestamp());
CREATE TABLE
postgres@findb:\d user_ini
                               Table "public.user_ini"
   Column    |            Type             | Collation | Nullable |      Default      
-------------+-----------------------------+-----------+----------+-------------------
 id          | integer                     |           |          | 
 user_id     | bigint                      |           |          | 
 user_name   | character varying(64)       |           |          | 
 create_time | timestamp without time zone |           |          | clock_timestamp()

postgres@findb:insert into user_ini(id,user_id,user_name)
findb-# select r,round(random()*2000000),r||'_leadx'
findb-# from generate_series(1,2000000) as r;
INSERT 0 2000000
postgres@findb:\dt+ user_ini
                      List of relations
 Schema |   Name   | Type  |  Owner   |  Size  | Description 
--------+----------+-------+----------+--------+-------------
 public | user_ini | table | postgres | 130 MB | 
(1 row)

postgres@findb:select count(*) from user_ini ;
  count  
---------
 2000000
(1 row)
postgres@findb:create table tbl_user_json(id serial,userinfo json);
CREATE TABLE
postgres@findb:create table tbl_user_jsonb(id serial,user_info jsonb);
CREATE TABLE
postgres@findb:\timing on
Timing is on.
postgres@findb:insert into tbl_user_json(userinfo) select row_to_json(user_ini) from user_ini;
INSERT 0 2000000
Time: 11461.166 ms (00:11.461)
postgres@findb:insert into tbl_user_jsonb(user_info) select row_to_json(user_ini) from user_ini;
INSERT 0 2000000
Time: 23366.464 ms (00:23.366)
postgres@findb:\dt+
                           List of relations
 Schema |      Name      | Type  |  Owner   |    Size    | Description 
--------+----------------+-------+----------+------------+-------------
 public | tbl_user_json  | table | postgres | 269 MB     | 
 public | tbl_user_jsonb | table | postgres | 326 MB     | 
 public | user_ini       | table | postgres | 130 MB     | 
(3 rows)

可见相同200万的数据，json写入耗时11.46秒，jsonb写入耗时23.36秒，并且jsonb的体积326M也比json的269M略大

• 200万行数据读取测试

postgres@findb:explain analyze select * from tbl_user_jsonb where user_info->>'user_name'='1_leadx';
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..56173.49 rows=10000 width=136) (actual time=58.207..2721.276 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbl_user_jsonb  (cost=0.00..54173.49 rows=4167 width=136) (actual time=1740.873..2626.759 rows=0 loops=3)
         Filter: ((user_info ->> 'user_name'::text) = '1_leadx'::text)
         Rows Removed by Filter: 666666
 Planning Time: 30.226 ms
 Execution Time: 2721.788 ms
(8 rows)
postgres@findb:explain analyze select * from tbl_user_jsonb where user_info->>'user_name'='1_leadx';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..56173.49 rows=10000 width=136) (actual time=0.489..395.663 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbl_user_jsonb  (cost=0.00..54173.49 rows=4167 width=136) (actual time=256.120..386.660 rows=0 loops=3)
         Filter: ((user_info ->> 'user_name'::text) = '1_leadx'::text)
         Rows Removed by Filter: 666666
 Planning Time: 0.115 ms
 Execution Time: 395.698 ms
(8 rows)
postgres@findb:create index idx_jsonb on tbl_user_jsonb using btree((user_info->>'user_name'));
CREATE INDEX
postgres@findb:\di+
                                List of relations
 Schema |     Name      | Type  |  Owner   |     Table      | Size  | Description 
--------+---------------+-------+----------+----------------+-------+-------------
 public | idx_jsonb     | index | postgres | tbl_user_jsonb | 60 MB | 
 public | score_pkey    | index | fin_er   | score          | 16 kB | 
 public | tbl_json_gin  | index | postgres | tbl_json       | 24 kB | 
 public | tbl_mvcc_pkey | index | postgres | tbl_mvcc       | 16 kB | 
(4 rows)
postgres@findb:explain analyze select * from tbl_user_jsonb where user_info->>'user_name'='1_leadx';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_user_jsonb  (cost=233.93..23700.60 rows=10000 width=136) (actual time=0.145..0.147 rows=1 loops=1)
   Recheck Cond: ((user_info ->> 'user_name'::text) = '1_leadx'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonb  (cost=0.00..231.43 rows=10000 width=0) (actual time=0.135..0.136 rows=1 loops=1)
         Index Cond: ((user_info ->> 'user_name'::text) = '1_leadx'::text)
 Planning Time: 0.300 ms
 Execution Time: 0.287 ms
(7 rows)

第一次全扫描2.7秒，后续300多ms，在userinfo上针对创建索引后，降至0.287ms

Filter移除行数666666不对？

postgres@findb:create index idx_gin_user_info_id on tbl_user_json using btree(((userinfo->>'id')::integer));
CREATE INDEX
Time: 5671.853 ms (00:05.672)
postgres@findb:create index idx_gin_user_info_id1 on tbl_user_jsonb using btree(((user_info->>'id')::integer));
CREATE INDEX
Time: 3128.501 ms (00:03.129)
postgres@findb:\di+
                                    List of relations
 Schema |         Name          | Type  |  Owner   |     Table      | Size  | Description 
--------+-----------------------+-------+----------+----------------+-------+-------------
 public | idx_gin_user_info_id  | index | postgres | tbl_user_json  | 43 MB | 
 public | idx_gin_user_info_id1 | index | postgres | tbl_user_jsonb | 43 MB | 
 public | idx_jsonb             | index | postgres | tbl_user_jsonb | 60 MB | 
 public | score_pkey            | index | fin_er   | score          | 16 kB | 
 public | tbl_json_gin          | index | postgres | tbl_json       | 24 kB | 
 public | tbl_mvcc_pkey         | index | postgres | tbl_mvcc       | 16 kB | 
(6 rows)
postgres@findb:explain analyze select id,userinfo->'id',userinfo->'user_name' from tbl_user_json where (userinfo->>'id')::int4>1 and (userinfo->>'id')::int4<10000;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_user_json  (cost=214.93..22291.91 rows=10000 width=68) (actual time=4.804..73.262 rows=9998 loops=1)
   Recheck Cond: ((((userinfo ->> 'id'::text))::integer > 1) AND (((userinfo ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=164
   ->  Bitmap Index Scan on idx_gin_user_info_id  (cost=0.00..212.43 rows=10000 width=0) (actual time=3.626..3.626 rows=9998 loops=1)
         Index Cond: ((((userinfo ->> 'id'::text))::integer > 1) AND (((userinfo ->> 'id'::text))::integer < 10000))
 Planning Time: 2.478 ms
 Execution Time: 74.730 ms
(7 rows)
postgres@findb: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=214.93..23881.60 rows=10000 width=68) (actual time=1.325..24.342 rows=9998 loops=1)
   Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=193
   ->  Bitmap Index Scan on idx_gin_user_info_id1  (cost=0.00..212.43 rows=10000 width=0) (actual time=1.186..1.186 rows=9998 loops=1)
         Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
 Planning Time: 0.244 ms
 Execution Time: 25.724 ms
(7 rows)

相同列上创建索引，索引大小相同，但明显jsonb读取更快导致索引创建时间更快，json:jsonb--5.67s:3.12s
走相同索引的查询，jsonb的也更快，json:jsonb--74.73ms:25.72 ms

• 总结，除纯索引扫描外，需要回表的情况下，jsonb的访问都比json快，但插入json较jsonb快