自研分区表

• 复制
  复制成功

#创建测试表2048个子表 
drop table t_time_range;
create table t_time_range
(
  f1 bigint not null, f2 timestamp ,f3 bigint
) 
partition by range (f2) begin (timestamp without time zone '2017-09-01 0:0:0') 
step (interval '1 month') 
partitions (2048) distribute by shard(f1) 
to group default_group;
#每个分区插入一条数据 
psql -At  -c "select 'insert into t_time_range values(1,'''||startyf||''',1);' from (select ('2017-09-01'::date + (t::text||' months')::interval)::date::text as startyf from generate_series(0,2047) as t) as t"|psql -d pgbench 
#编写测试存储过程
create or replace function get_t_time_range() returns t_time_range as
$$
declare
    v_start timestamp;
    v_end timestamp;
    v_random integer;
    v_rec t_time_range%rowtype;
begin
    v_random:=(random()*50000)::integer;
    v_start:=(to_char('2017-09-01'::date+v_random,'YYYY-MM')||'-01')::timestamp;
    v_end:=(to_char('2017-09-01'::date+v_random,'YYYY-MM')||'-01')::DATE+'1 months'::interval;
    select * into v_rec from  t_time_range where f2>=v_start and f2<v_end;
    return v_rec;
end;
$$
LANGUAGE plpgsql; 
#pgbench sql脚本 
[tbase@eg-9-117-182-250 pgbench]$ cat select_t_time_range.sql
select * from get_t_time_range(); 
#pgbench方法
pgbench -h 9.117.183.12 -p 15432 -d pgbench -U tbase -c 64 -j 1 -n -M prepared -T 60 -r -f select_t_time_range.sql > select_t_time_range.log 2>&1
#测试结果
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 1
duration: 60 s
number of transactions actually processed: 1002843
latency average = 3.829 ms
tps = 16712.957914 (including connections establishing)
tps = 16713.547319 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         3.813  select * from get_t_time_range();