pg性能测试

插入测试

1.1. 环境信息

数据库	postgresql
版本	9.6
系统	linux centos 7.3
共享内存	1280M
其它配置	保持默认

1.2. 建表sql语句汇总

--创建随机日期时间函数

CREATE OR REPLACE FUNCTION rand_date_time(start_date date, end_date date) RETURNS TIMESTAMP AS

$BODY$

DECLARE

interval_days integer;

random_seconds integer;

random_dates integer;

random_date date;

random_time time;

BEGIN

interval_days := end_date - start_date;

random_dates:= trunc(random()*interval_days);

random_date := start_date + random_dates;

random_seconds:= trunc(random()*3600*24);

random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL;

RETURN random_date +random_time;

END;

$BODY$

LANGUAGE plpgsql;

--创建随机数的存储过程

create OR REPLACE function f_random_str(length INTEGER) returns character varying

LANGUAGE plpgsql

AS $$

DECLARE

result varchar(50);

BEGIN

SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)

FROM generate_series(1,length)), '') INTO result;

return result;

END

$$;

drop table if exists tbl_index5000;

create table if not exists tbl_index5000(a bigint,b timestamp without time zone, c varchar(16), d varchar(32), e varchar(48));

insert into tbl_index5000 (a,b,c,d,e) select generate_series(1,5000*10000), '2019-06-05',f_random_str(16),f_random_str(32),f_random_str(48);

1.3. 建索引sql语句汇总

create index index_a1 on tbl_index1 using btree(a);

create index index_a10 on tbl_index10 using btree(a);

create index index_a100 on tbl_index100 using btree(a);

create index index_a1000 on tbl_index1000 using btree(a);

create index index_a5000 on tbl_index5000 using btree(a);

create index index_b1 on tbl_index1 using btree(b);

create index index_b10 on tbl_index10 using btree(b);

create index index_b100 on tbl_index100 using btree(b);

create index index_b1000 on tbl_index1000 using btree(b);

create index index_b5000 on tbl_index5000 using btree(b);

create index index_c1 on tbl_index1 using btree(c);

create index index_c10 on tbl_index10 using btree(c);

create index index_c100 on tbl_index100 using btree(c);

create index index_c1000 on tbl_index1000 using btree(c);

create index index_c5000 on tbl_index5000 using btree(c);

create index index_a10000 on tbl_index10000 using btree(a);

create index index_b10000 on tbl_index10000 using btree(b);

create index index_c10000 on tbl_index10000 using btree(c);

1.4. 查询sql语句汇总

select "count"(*) from tbl_index1;

select "count"(*) from tbl_index10;

select "count"(*) from tbl_index100;

select "count"(*) from tbl_index1000;

select "count"(*) from tbl_index5000;

select "count"(*) from tbl_index10000;

select * from tbl_index1 where a=123456;

select * from tbl_index10 where a=123456;

select * from tbl_index100 where a=123456;

select * from tbl_index1000 where a=123456;

select * from tbl_index5000 where a=123456;

select * from tbl_index10000 where a=123456;

select * from tbl_index1 where b='2019-06-05 15:46:56';

select * from tbl_index10 where b='2019-06-05 15:46:56';

select * from tbl_index100 where b='2019-06-05 15:46:56';

select * from tbl_index1000 where b='2019-06-05 14:46:56';

select * from tbl_index5000 where b='2019-06-06 14:46:56';

select * from tbl_index10000 where b='2019-06-05 14:46:56';

select * from tbl_index1 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index10 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index100 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index1000 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index5000 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index10000 where c='FKXXEJJYRQSJKVTT';

select * from tbl_index1 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';

select * from tbl_index10 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';

select * from tbl_index100 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';

select * from tbl_index1000 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';

select * from tbl_index5000 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';

1.5. 测试使用查询语句

计数	select "count"() from tbl_index;	这里的表名需要替换为相应的各个表
查询1	select * from tbl_index* where a=9999;	这里由于a不是主键，所以需要全表扫描，所以查询时间与a的值无关
查询2	select * from tbl_index* where b='2019-06-05 15:46:56';	随机一个时间
查询3	select * from tbl_index* where c='FKXXEJJYRQSJKVTT';	随机16位字符串