PostgreSql性能测试

部门:产品技术部-风控团队

作者:镇模 zhenmo@maihaoche.com

1. 环境

  • 版本:9.4.9
  • 系统:OS X 10.11.5
  • CPU:Core i5 2.7G
  • 内存:16G
  • 硬盘:256G SSD

2. 测试情况

2.1 测试表结构

/*
 Navicat Premium Data Transfer

 Source Server         : postgresql
 Source Server Type    : PostgreSQL
 Source Server Version : 90409
 Source Host           : localhost
 Source Database       : postgres
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 90409
 File Encoding         : utf-8

 Date: 09/10/2016 16:59:58 PM
*/

-- ----------------------------
--  Table structure for bi_object
-- ----------------------------
DROP TABLE IF EXISTS "public"."bi_object";  
CREATE TABLE "public"."bi_object" (  
    "id" int8 NOT NULL DEFAULT nextval('id_seq'::regclass),
    "features" jsonb,
    "creator" varchar(64) COLLATE "default",
    "type" int2,
    "is_deleted" bool,
    "gmt_create" timestamp(6) NULL,
    "gmt_modify" timestamp(6) NULL
)
WITH (OIDS=FALSE);  
ALTER TABLE "public"."bi_object" OWNER TO "postgres";

COMMENT ON COLUMN "public"."bi_object"."id" IS '对象ID,主键';  
COMMENT ON COLUMN "public"."bi_object"."features" IS '对象特征集合,json结构';  
COMMENT ON COLUMN "public"."bi_object"."type" IS '对象类型';  
COMMENT ON COLUMN "public"."bi_object"."is_deleted" IS '是否已删除';  
COMMENT ON COLUMN "public"."bi_object"."gmt_create" IS '创建时间';

-- ----------------------------
--  Primary key structure for table bi_object
-- ----------------------------
ALTER TABLE "public"."bi_object" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;  

2.2 测试数据

数据量:500万条;

样例数据:

insert into "public"."Object" ( "gmt_modify", "id", "gmt_create", "type", "features", "creator", "is_deleted") values ( null, '1', '2016-09-10 15:35:32', '1', '{"age": 18, "sex": 1, "city": "Hangzhou", "height": 180, "userId": 1, "weight": 70, "country": "China", "userName": "zhenmo"}', 'zhenmo', 'f') RETURNING *;  

2.3 插入性能测试

  • 只有主键索引的情况下,插入500万条记录测试结果

执行sql:

insert into bi_object(creator, features, gmt_create, is_deleted, type)  
select 'zhenmo', row_to_json(bi_user)::jsonb, now(), false, 1 from bi_user  

执行结果:

Affected rows : 5000000, Time: 51.24sec  

平均:97580条/秒

  • 除了主键,单个B-Tree索引的情况下,插入500万条记录测试

执行sql:同上

执行结果:

Affected rows : 5000000, Time: 194.78sec  

索引对于插入数据的性能影响非常大,带有索引后插入速度慢了近4倍。

  • 除了主键,单个GIN索引的情况下,插入500万条记录测试

执行sql:同上

执行结果:

Affected rows : 5000000, Time: 100.40sec  

测试了几次,时间稳定在50秒多一点

2.4 查询性能测试

2.4.1 无索引查询

执行sql:

explain analyze select id, features from bi_object where features->>'name' = 'user_4000000';  

执行结果:

Seq Scan on bi_object  (cost=0.00..177028.79 rows=25000 width=113) (actual time=1234.339..1520.186 rows=1 loops=1)  
  Filter: ((features ->> 'name'::text) = 'user_4000000'::text)
  Rows Removed by Filter: 4999999
Planning time: 0.044 ms  
Execution time: 1520.204 ms  

测试了多次,时间稳定在1.5秒左右

2.4.2 创建索引

  • 创建B-Tree索引

执行sql:

create index idx_bi_object_features_name on bi_object using btree ((features ->> 'name'));  

执行结果:

OK, Time: 183.59sec  

可见创建索引耗时比较很长

  • 创建jsonb字段单个key的GIN索引

执行sql:

create index idx_bi_object_features_name on bi_object using gin ((features -> 'name'));

执行结果:

OK, Time: 26.82sec  
  • 创建jsonb字段GIN索引,索引class为默认的jsonb_ops

执行sql:

create index idx_bi_object_features on bi_object using gin (features);  

执行结果:

OK, Time: 85.74sec  
  • 创建jsonb字段GIN索引,索引class为默认的jsonb_ops

执行sql:

create index idx_bi_object_features on bi_object using gin (features jsonb_path_ops);  

执行结果:

OK, Time: 74.71sec  

2.4.3 使用索引查询

  • 使用B-Tree索引(为features.name单独建立索引)查询

执行sql:

explain analyze select id, features from bi_object where features->>'name' = 'user_4000000';  

执行结果:

Bitmap Heap Scan on bi_object  (cost=582.20..58832.89 rows=25002 width=113) (actual time=0.045..0.045 rows=1 loops=1)  
  Recheck Cond: ((features ->> 'name'::text) = 'user_4000000'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_bi_object_features_name  (cost=0.00..575.95 rows=25002 width=0) (actual time=0.041..0.041 rows=1 loops=1)
        Index Cond: ((features ->> 'name'::text) = 'user_4000000'::text)
Planning time: 0.055 ms  
Execution time: 0.061 ms  

添加索引前查询耗时:1520.204 ms,创建索引后查询耗时:0.061 ms,查询速度提升:24921倍

  • 使用GIN索引(为features.name单独建立索引,索引class为默认的jsonb_ops)查询

执行sql:

explain analyze select * from bi_object where features->'name' ? 'user_4000000';  

执行结果:

Bitmap Heap Scan on bi_object  (cost=66.75..16463.02 rows=5000 width=139) (actual time=0.021..0.022 rows=1 loops=1)  
  Recheck Cond: ((features -> 'name'::text) ? 'user_4000000'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_bi_object_features_name  (cost=0.00..65.50 rows=5000 width=0) (actual time=0.012..0.012 rows=1 loops=1)
        Index Cond: ((features -> 'name'::text) ? 'user_4000000'::text)
Planning time: 0.062 ms  
Execution time: 0.043 ms  

相比较B-Tree索引,查询速度并没有提升很多,但是GIN索引占用的存储空间却是B-Tree的3倍左右,这样看来使用GIN索引并不是一个好选择。

  • 将整个jsonb字段建立GIN索引(索引class为默认的jsonb_ops)后查询

执行sql:

explain analyze select * from bi_object where features @> '{"name":"user_4000000"}';  

执行结果:

Bitmap Heap Scan on bi_object  (cost=86.75..16470.52 rows=5000 width=139) (actual time=0.100..0.100 rows=1 loops=1)  
  Recheck Cond: (features @> '{"name": "user_4000000"}'::jsonb)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_bi_object_features  (cost=0.00..85.50 rows=5000 width=0) (actual time=0.091..0.091 rows=1 loops=1)
        Index Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Planning time: 0.064 ms  
Execution time: 0.123 ms  

疑问:
一下查询没有用到索引,感觉很奇怪,在单独为features.name建立的GIN索引时,可以利用索引查询,但是为features建立索引后,再次查询却不会使用索引,而官方文档却说可以支持'?',操作很疑惑:什么鬼??GIN的脾气很奇怪。

执行sql:

explain analyze select * from bi_object where features->'name' ? 'user_4000000';  

执行结果:

Seq Scan on bi_object  (cost=0.00..177030.00 rows=5000 width=139) (actual time=100.820..2059.617 rows=1 loops=1)  
  Filter: ((features -> 'name'::text) ? 'user_4000000'::text)
  Rows Removed by Filter: 4999999
Planning time: 0.051 ms  
Execution time: 2059.635 ms  
  • 将整个jsonb字段建立GIN索引(索引class为jsonbpathops)后查询

执行sql:

explain analyze select * from bi_object where features @> '{"name":"user_4000000"}';  

执行结果:

Bitmap Heap Scan on bi_object  (cost=66.75..16450.52 rows=5000 width=139) (actual time=0.019..0.019 rows=1 loops=1)  
  Recheck Cond: (features @> '{"name": "user_4000000"}'::jsonb)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_bi_object_features  (cost=0.00..65.50 rows=5000 width=0) (actual time=0.012..0.012 rows=1 loops=1)
        Index Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Planning time: 0.065 ms  
Execution time: 0.045 ms  

看一看出来2和4两次实验的查询速度接近,4比2的查询速度有了明显提升。2和4两次查询区别在于两点,前者使用的是GIN索引class是jsonbops,后者是jsonbpathops。后者仅支持'@>'操作,前者支持'? ?& ?| @>'等操作。按照官方文档的说法jsonbpathops的性能要高于jsonbops,这一点已经得到验证,并且前者占用的空间要小,以本次实验为例,jsonbops索引占用空间为686M,而jsonbpath_ops占用空间为446M。实际情况来看宁可牺牲点空间

  • jsonb类型的字段的某个key单独建立B-Tree索引

说明:jsonb类型的字段features的age建立B-Tree索引,然后使用索引检索。

执行sql:

step1:建立索引

create index idx_bi_object_features_age on bi_object using btree (((features ->> 'age')::integer));  

step2:查询,注意建立索引时指定的类型是integer那么查询也需要将数据转换成integer,其他的int类型如int2是不会使用索引查询的,这一点不友好,建议pg可以兼容所有int类型

explain analyze select * from bi_object where (features->>'age')::integer = 50;  

执行结果:

Bitmap Heap Scan on bi_object  (cost=470.19..58840.83 rows=25000 width=139) (actual time=39.967..251.822 rows=50174 loops=1)  
  Recheck Cond: (((features ->> 'age'::text))::integer = 50)
  Heap Blocks: exact=39786
  ->  Bitmap Index Scan on idx_bi_object_features_age  (cost=0.00..463.94 rows=25000 width=0) (actual time=17.719..17.719 rows=50174 loops=1)
        Index Cond: (((features ->> 'age'::text))::integer = 50)
Planning time: 0.082 ms  
Execution time: 255.228 ms  

即便是使用了索引,查询速度依然不理想,看来jsonb字段建立的btree索引效率相较于普通字段的btree来说效率很差,慢了差不多4200多倍。

2.4.5 删除测试

删除500万条记录测试

执行sql:

delete from bi_object  

执行结果:

Affected rows : 5000000, Time: 14.12sec  

2.4.6 表占用磁盘大小

表bi_object记录数5000000,建立单个字段B-Tree索引后总共占用磁盘1055M,其中索引258M,索引约占用1/4的空间。将索引类型改为GIN索引后,磁盘空间占用增大到1376M,索引占579M。详见下表:

|索引|类型|大小(M)|备注| |---|----|-------|---| |Objectpkey|B-Tree|107|| |idxbiobjectfeaturesname|B-Tree|151|| |idxbiobjectfeaturesname|GIN|472|索引class为默认的jsonbops| |idxbiobjectfeatures|GIN|686|索引class为默认的jsonbops| |idxbiobjectfeatures|GIN|446|索引class为jsonbpath_ops|

GIN索引占用的空间是B-Tree的3倍多。

3. 总结

3.1 我们的使用场景

我们需要为一类对象动态计算其所具有的特征,具体到一张表中来说,就是一个主键对应多个列,但是列的个数是不定的,会经常增加。同时了为了便于和其他关系表做关联查询,保证快速的查询响应时间,我们需要将其存储在关系型数据库中。由于列的数量动态变化,频繁修改表字段肯定不可取,所以想到将其存储到JSON字段中。

3.2 可以利用的资源

我们的应用环境依托于阿里云数加环境,数加的rds只提供了mysql5.6版本(5.7版本的mysql才支持json类型)和postgresql 9.4版,postgresql支持json和jsonb类型(后者存储空间小,支持部分索引操作)。所以我们决定采用postgresql作为数据存储工具。

3.3 频繁的查询

实际频繁查询的情况是,需要利用jsonb字段中的某些key来组合做条件查询,比如:features->'age' >= 20 and features->'age' < 30等

3.4 结论及问题

结合上面的测试,postgresql基本满足了我们的应用场景需求。在写入速度、简单查询响应时间、存储动态数据等方面都可以满足。

测试下来发现的一个潜在的问题就是jsonb类型字段的gin索引的不支持按范围检索,需要做全表扫描。postgresql的jsonb字段建立gin索引后,只支持精确查询,无法做范围检索,这样会导致查询比较慢。本地测试下来500万条记录检索时间是2秒多,以后如果数据量增大,那么很有可能导致查询超时。