Using jsonb on PostgreSQL

jsonb datatype, a specialised representation of the JSON data, allowing PostgreSQL to be competitive in managing the "lingua franca" of the moment for the exchange of data via web services. It is useful to perform a number of tests to verify its actual performance. In this post I have do some test for using jsonb datatype in PostgreSQL, hardware information: iMac 14.2, 3.4 GHz Intel Core i5 CPU, 8 GB 1600 MHz DDR3 RAM, 7200 RPM HDD.

I have create 3 tables: user_ini base info data (2 000 000 rows), tbl_user_json with json type column (2 000 000 rows), tbl_user_jsonb with jsonb type column (2 000 000 rows).

Connect database and create benchmark data

$ psql -d postgres
psql (9.5.0)
Type "help" for help.

postgres=# 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

postgres=# insert into user_ini(id,user_id,user_name) select r,round(random()*2000000), r || '_test' from generate_series(1,2000000) as r;
INSERT 0 2000000

Generate json data

postgres=# create table tbl_user_json(id serial, user_info json);
CREATE TABLE

postgres=# insert into tbl_user_json(user_info) select row_to_json(user_ini) from user_ini;
INSERT 0 2000000

Generate jsonb data

postgres=# create table tbl_user_jsonb(id serial, user_info jsonb);
CREATE TABLE

postgres=# insert into tbl_user_jsonb(user_info) select row_to_json(user_ini)::jsonb from user_ini;    
INSERT 0 2000000

Get table size

postgres=# \dt+ tbl_user_json
                       List of relations
 Schema |     Name      | Type  | Owner |  Size  | Description 
--------+---------------+-------+-------+--------+-------------
 public | tbl_user_json | table | xuri  | 274 MB | 
(1 row)

postgres=# \dt+ tbl_user_jsonb
                       List of relations
 Schema |      Name      | Type  | Owner |  Size  | Description 
--------+----------------+-------+-------+--------+-------------
 public | tbl_user_jsonb | table | xuri  | 332 MB | 
(1 row)

Query rows

postgres=# select * from tbl_user_jsonb limit 3;
 id |                                                user_info                                                
----+---------------------------------------------------------------------------------------------------------
  1 | {"id": 1, "user_id": 1961705, "user_name": "1_test", "create_time": "2016-02-04T11:46:47.434434+08:00"}
  2 | {"id": 2, "user_id": 1977164, "user_name": "2_test", "create_time": "2016-02-04T11:46:47.434508+08:00"}
  3 | {"id": 3, "user_id": 1731350, "user_name": "3_test", "create_time": "2016-02-04T11:46:47.43451+08:00"}
(3 rows)

jsonb key and value query test

Query user_info field from tbl_user_jsonb table:

postgres=# select * from tbl_user_jsonb where user_info->>'user_name'= '1_test';
 id |                                                user_info                                                
----+---------------------------------------------------------------------------------------------------------
  1 | {"id": 1, "user_id": 1961705, "user_name": "1_test", "create_time": "2016-02-04T11:46:47.434434+08:00"}
(1 row)

Query explain analyze without index:

postgres=# explain analyze select * from tbl_user_jsonb where user_info->>'user_name'= '1_test';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl_user_jsonb  (cost=0.00..72545.09 rows=10000 width=141) (actual time=0.012..359.949 rows=1 loops=1)
   Filter: ((user_info ->> 'user_name'::text) = '1_test'::text)
   Rows Removed by Filter: 1999999
 Planning time: 0.033 ms
 Execution time: 359.966 ms
(5 rows)

Create index for tbl_user_jsonb field in user_info table:

postgres=# create index idx_gin_user_infob_user_name on tbl_user_jsonb using btree ((user_info ->> 'user_name'));
CREATE INDEX

Query plan, just use 0.057 ms:

postgres=# explain analyze select * from tbl_user_jsonb where user_info->>'user_name'= '1_test';
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_user_jsonb  (cost=233.93..23867.07 rows=10000 width=141) (actual time=0.037..0.037 rows=1 loops=1)
   Recheck Cond: ((user_info ->> 'user_name'::text) = '1_test'::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.034..0.034 rows=1 loops=1)
         Index Cond: ((user_info ->> 'user_name'::text) = '1_test'::text)
 Planning time: 0.127 ms
 Execution time: 0.057 ms
(7 rows)

Query by user_id in user_info table without index:

postgres=# explain analyze select * from tbl_user_jsonb where user_info->>'user_id'= '1';
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl_user_jsonb  (cost=0.00..72545.00 rows=10000 width=141) (actual time=459.846..486.119 rows=1 loops=1)
   Filter: ((user_info ->> 'user_id'::text) = '1'::text)
   Rows Removed by Filter: 1999999
 Planning time: 0.037 ms
 Execution time: 486.131 ms
(5 rows)

GIN index

Before use GIN index, drop index idx_gin_user_infob_user_name:

postgres=# drop index idx_gin_user_infob_user_name;
DROP INDEX

Create GIN index cost 202147.862 ms:

postgres=# create index idx_tbl_user_jsonb_user_Info on tbl_user_jsonb using gin (user_Info);
CREATE INDEX

postgres=# \di+ idx_tbl_user_jsonb_user_Info
                                       List of relations
 Schema |             Name             | Type  | Owner |     Table      |  Size  | Description 
--------+------------------------------+-------+-------+----------------+--------+-------------
 public | idx_tbl_user_jsonb_user_info | index | xuri  | tbl_user_jsonb | 445 MB | 
(1 row)

Query by key and value with index:

postgres=# 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..6647.26 rows=2000 width=141) (actual time=56.129..56.145 rows=3 loops=1)
   Recheck Cond: (user_info @> '{"user_id": 1017031}'::jsonb)
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=4
   ->  Bitmap Index Scan on idx_tbl_user_jsonb_user_info  (cost=0.00..59.00 rows=2000 width=0) (actual time=56.098..56.098 rows=4 loops=1)
         Index Cond: (user_info @> '{"user_id": 1017031}'::jsonb)
 Planning time: 28.148 ms
 Execution time: 56.183 ms
(8 rows)

Query by key and value without index:

postgres=# explain analyze select * from tbl_user_jsonb where user_info->>'user_name' ='6_test';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on tbl_user_jsonb  (cost=0.00..72545.00 rows=10000 width=141) (actual time=0.028..423.282 rows=1 loops=1)
   Filter: ((user_info ->> 'user_name'::text) = '6_test'::text)
   Rows Removed by Filter: 1999999
 Planning time: 0.036 ms
 Execution time: 423.295 ms
(5 rows)

Drop GIN index idx_tbl_user_jsonb_user_Info:

postgres=# drop index idx_tbl_user_jsonb_user_Info;
DROP INDEX

Create new index idx_gin_user_info_user_name and query explain analyze, so fast:

postgres=# create index idx_gin_user_info_user_name on tbl_user_jsonb using gin((user_info -> 'user_name'));
CREATE INDEX

postgres=# explain analyze select * from tbl_user_jsonb where user_info->'user_name' ?'6_test';
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_user_jsonb  (cost=35.50..6628.26 rows=2000 width=141) (actual time=0.019..0.019 rows=1 loops=1)
   Recheck Cond: ((user_info -> 'user_name'::text) ? '6_test'::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.009..0.009 rows=1 loops=1)
         Index Cond: ((user_info -> 'user_name'::text) ? '6_test'::text)
 Planning time: 0.121 ms
 Execution time: 0.035 ms
(7 rows)

json and jsonb query benchmark

Before compare json and jsonb, drop GIN index idx_gin_user_info_user_name:

postgres=# drop index idx_gin_user_info_user_name;
DROP INDEX

Create function index for id field on tbl_user_json and tbl_user_jsonb table:

postgres=# create index idx_gin_user_info_id on tbl_user_json using btree (((user_info ->> 'id')::integer));
CREATE INDEX

postgres=# create index idx_gin_user_infob_id on tbl_user_jsonb using btree (((user_info ->> 'id')::integer));
CREATE INDEX

Because ->> return text type value, and this value will be used for compare with id field, so we need create function index.

json query

postgres=# 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=214.93..22443.74 rows=10000 width=111) (actual time=16.235..32.507 rows=9998 loops=1)
   Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=172
   ->  Bitmap Index Scan on idx_gin_user_info_id  (cost=0.00..212.43 rows=10000 width=0) (actual time=16.179..16.179 rows=9998 loops=1)
         Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
 Planning time: 6.588 ms
 Execution time: 32.929 ms
(7 rows)

jsonb query

postgres=# 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..24048.07 rows=10000 width=141) (actual time=16.194..20.925 rows=9998 loops=1)
   Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=205
   ->  Bitmap Index Scan on idx_gin_user_infob_id  (cost=0.00..212.43 rows=10000 width=0) (actual time=16.164..16.164 rows=9998 loops=1)
         Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
 Planning time: 0.136 ms
 Executio
Using jsonb on PostgreSQL
6 votes, 4.83 avg. rating (96% score)