PG社区分区表

• 复制
  复制成功

#创建测试表2048个子表
[tbase@TENCENT64 shell]$ psql -d pgbench
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.
pgbench=#drop table t_native_range; create table t_native_range(f1 bigint,f2 timestamp default now(),f3 integer) partition by range ( f2 );
\q
[tbase@TENCENT64 shell]$ psql -At  -c "select 'create table t_native_range_'||num::text||' partition of t_native_range(f1,f2,f3) for values from ('''||startyf||''') to ('''||endyf||'''); ' from (select t::text as num, ('2017-09-01'::date + (t::text||' months')::interval)::date::text as startyf,('2017-10-01'::date + (t::text||' months')::interval)::date::text as endyf from generate_series(0,2047) as t) as t;"|psql -d pgbench 
#每个分区插入一条数据
psql -At  -c "select 'insert into t_native_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_native_range() returns t_native_range as
$$
declare
    v_start timestamp;
    v_end timestamp;
    v_random integer;
    v_rec t_native_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_native_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_native_range.sql
select * from get_t_native_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_native_range.sql > select_t_native_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: 35978
latency average = 106.832 ms
tps = 599.068955 (including connections establishing)
tps = 599.090129 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
       105.706  select * from get_t_native_range();