PG社区分区表

• 复制
  复制成功

#创建测试表2048个子表
[tbase@TENCENT64 shell]$ psql -d pgbench
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.
pgbench=#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
#pgbench sql脚本
[tbase@eg-9-117-182-250 pgbench]$ cat insert_t_native_range.sql
\set f1 random(1, 10000000)
\set f2 random(1, 50000)
insert into t_native_range values(:f1,('2017-09-01'::date+:f2::integer)::timestamp,:f1); 
#测试方法
[tbase@eg-9-117-182-250 pgbench]$  pgbench -h 9.117.183.12 -p 15432 -d pgbench -U tbase -c 64 -j 1 -n -M prepared -T 60 -r -f insert_t_native_range.sql > insert_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: 781613
latency average = 4.913 ms
tps = 13025.700700 (including connections establishing)
tps = 13026.161416 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.007  \set f1 random(1, 10000000)
         0.004  \set f2 random(1, 50000)
         4.850  insert into t_native_range values(:f1,('2017-09-01'::date+:f2::integer)::timestamp,:f1);