PG普通类型数据和Jsonb数据对比试验

PG数据库JSonb类型数据测试

1. 测试大纲

1.1. 数据表写入速率对比测试。

1.2. 数据表容量对比测试。

1.3. 全表扫描查询性能对比测试。

1.4. 索引扫描查询性能对比测试。

2. 测试步骤

使用定时记录数据对比测试两种表之间的差别，即普通表定时记录月表和jonsb表。两种表结构定义如下：

普通定时记录月表：

    CREATE TABLE pd_tb_12_test    (      sourceid integer NOT NULL,      logtime timestamp without time zone NOT NULL,      msec smallint NOT NULL,      data1 double precision,      data2 double precision,      data3 double precision,      data4 double precision,      data5 double precision,      data6 double precision,      data7 double precision,      data8 double precision,      data9 double precision,      data10 double precision,      data11 double precision,      data12 double precision,      data13 double precision,      data14 double precision,      data15 double precision,      data16 double precision,      status smallint,      CONSTRAINT pk_pd_tb_12_test PRIMARY KEY (sourceid, logtime, msec),      CONSTRAINT ck_pd_tb_12_test_time CHECK (logtime >= to_date    ('2015-12-01'::text, 'YYYY-MM-DD'::text) AND logtime < to_date  ('2016-01-01'::text, 'YYYY-MM-DD'::text)))

Jsonb类型表：

create table pd_tb_12_test_jsonb(datalog jsonb);

说明：未对Jsonb类型表创建主键和检查约束，在数据写入测试时，普通表会吃亏。

2.1. 数据表写入速率测试

测试数据规模：4000组定时记录，每5min产生一次数据，制造12月份一个月的数据。总数据条数为：4000*6*24*31 = 35712000（条）。

普通表写入测试：

临时表中存储4000条12月1号0点0分的源数据，使用此源数据向测试表pd_tb_12_test中循环插入数据，每次循环logtime增加5min，直到12月31号23点55分。
写入耗时：1608s。

JSonb表写入测试：

直接把pd_tb_12_test表中的数据转换为jsonb类型写入到pd_tb_12_test_jsonb表中，写入语句为：

insert into pd_tb_12_test_jsonb(datalog) select row_to_json(pd_tb_12_test)::jsonb from pd_tb_12_test;    写入耗时：2639s。

说明：此时未对jsonb表创建主键和检查约束，从这方面来说jsonb表占了一些便宜。

结论：单从写入同样数据量的耗时来看，jsonb表的写入速度比普通表要慢很多。

2.2. 数据表容量测试

使用上述写入数据后的表进行对比。

普通表pd_tb_12_test的数据表大小为：6488MB；

普通表pd_tb_12_test的主键索引大小为：1383MB；

Jsonb表pd_tb_12_test_jsonb的数据表大小为：18GB；

Jsonb表pd_tb_12_test_jsonb此时无索引。

结论：可以看出，同样的数据量Jsonb表的大小为普通表的3倍。

2.3. 全表扫描查询性能测试

为了保证查询测试的公平性，在测试查询性能之前，先删除了普通表pd_tb_12_test的主键索引，然后再进行查询性能的测试。

普通表查询测试：

PECSTAR_DATA_GZZD=# explain analyze select * from pd_tb_12_test where sourceid= 20 and logtime >= '2015-12-12' and logtime <= '2015-12-13';                QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------------------------------Seq Scan on pd_tb_12_test  (cost=0.00..1455472.00 rows=283 width=144)   (actual time=136292.211..144357.127 rows=289 loops=1)    Filter: ((logtime >= '2015-12-12 00:00:00'::timestamp without time zone) AND (logtime <= '2015-12-13 00:00:00'::timestamp without time zone) AND (sourceid = 20))    Rows Removed by Filter: 35711711 Planning time: 0.223 ms Execution time: 144357.435 ms(5 行记录)

查询总耗时144.357s；

Jsonb表查询测试：

PECSTAR_DATA_GZZD=# explain analyze select * from pd_tb_12_test_jsonb where datalog->>'sourceid'='20' and datalog->>'logtime' >= '2015-12-12T00:00:00' and datalog->>'logtime' <= '2015-12-13T00:00:00';                                                                                            QUERY PLAN    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    Seq Scan on pd_tb_12_test_jsonb  (cost=0.00..3276593.50 rows=893 width=506) (actual time=219834.645..241907.125 rows=289 loops=1)        Filter: (((datalog ->> 'logtime'::text) >= '2015-12-12T00:00:00'::text) AND ((datalog ->> 'logtime'::text) <= '2015-12-13T00:00:00'::text) AND ((datalog ->> 'sourceid'::text) = '20'::text))        Rows Removed by Filter: 35711711    Planning time: 1.479 ms    Execution time: 241907.384 ms    (5 行记录)

查询总耗时241.907s；

2.4. 索引扫描查询性能测试

对两张表的sourceID，logtime，msec三个字段创建索引，具体如下：

PECSTAR_DATA_GZZD=# alter table pd_tb_12_test add constraint pk_pd_tb_12_test primary key (sourceid,logtime,msec);    ALTER TABLE    时间：458263.365 ms    PECSTAR_DATA_GZZD=# create index ix_pd_tb_12_test_jsonb on pd_tb_12_test_jsonb using btree((datalog->>'sourceid'),(datalog->>'logtime'),(datalog->>'msec'));    CREATE INDEX    时间：2298165.514 ms

索引创建完成后，再次执行查询操作，查询语句和2.3节中查询语句相同。
普通表查询测试：

PECSTAR_DATA_GZZD=# explain analyze select * from pd_tb_12_test where sourceid= 20 and logtime >= '2015-12-12' and logtime <= '2015-12-13';                                                                                    QUERY PLAN    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    Bitmap Heap Scan on pd_tb_12_test  (cost=12.17..1133.45 rows=283 width=144) (actual time=44.777..2277.358 rows=289 loops=1)        Recheck Cond: ((sourceid = 20) AND (logtime >= '2015-12-12 00:00:00'::timestamp without time zone) AND (logtime <= '2015-12-13 00:00:00'::timestamp without time zone))        Heap Blocks: exact=289            ->  Bitmap Index Scan on pk_pd_tb_12_test  (cost=0.00..12.10 rows=283 width=0) (actual time=35.126..35.126 rows=289 loops=1)                Index Cond: ((sourceid = 20) AND (logtime >= '2015-12-12 00:00:00'::timestamp without time zone) AND (logtime <= '2015-12-13 00:00:00'::timestamp without time zone))    Planning time: 51.055 ms    Execution time: 2302.445 ms    (7 行记录)

时间：2302.445 ms

Jsonb表查询测试：

PECSTAR_DATA_GZZD=# explain analyze select * from pd_tb_12_test_jsonb where datalog->>'sourceid'='20' and datalog->>'logtime' >= '2015-12-12T00:00:00' and datalog->>'logtime' <= '2015-12-13T00:00:00';                                                                                                    QUERY PLAN    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    Bitmap Heap Scan on pd_tb_12_test_jsonb  (cost=35.96..3578.43 rows=893 width=506) (actual time=29.621..1423.518 rows=289 loops=1)        Recheck Cond: (((datalog ->> 'sourceid'::text) = '20'::text) AND ((datalog ->> 'logtime'::text) >= '2015-12-12T00:00:00'::text) AND ((datalog ->> 'logtime'::text) <= '2015-12-13T00:00:00'::text))        Heap Blocks: exact=289        ->  Bitmap Index Scan on ix_pd_tb_12_test_jsonb  (cost=0.00..35.73 rows=893 width=0) (actual time=20.306..20.306 rows=289 loops=1)            Index Cond: (((datalog ->> 'sourceid'::text) = '20'::text) AND ((datalog ->> 'logtime'::text) >= '2015-12-12T00:00:00'::text) AND ((datalog ->> 'logtime'::text) <= '2015-12-13T00:00:00'::text))    Planning time: 1.319 ms    Execution time: 1423.766 ms    (7 行记录)

时间：1423.766 ms

结论：可以看出，在数据写入速率、数据容量、创建索引耗时、索引容量、全表扫描速率上，jsonb表都处于劣势，但是在btree索引查询上，jsonb表却比普通表快很多。所以，如果是对查询速率要求较高，对容量要求低的需求，可以考虑使用jsonb表，而不使用普通表。