PostgreSQL 10分区表及性能测试报告小结_PostgreSQL_

[root@ad source]# git clone git://git.postgresql.org/git/postgresql.git [root@ad source]# cd postgresql [root@ad source]# ./configure --prefix=/usr/local/pgsql10 [root@ad postgresql]# gmake -j 4 [root@ad postgresql]# gmake install [root@ad postgresql]# su postgres [postgres@ad postgresql]# /usr/local/pgsql10/bin/initdb --no-locale -E utf8 -D /home/postgres/data10/ -U postgres

 postgresql.conf listen_addresses = '*' port = 10000 shared_buffers = 8096MB maintenance_work_mem = 512MB effective_cache_size = 30GB log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 log_checkpoints = off log_connections = off log_disconnections = off log_duration = off log_line_prefix = '%m %h %a %u %d %x [%p] ' log_statement = 'none' log_timezone = 'PRC' track_activity_query_size = 4096 max_wal_size = 32GB min_wal_size = 2GB checkpoint_completion_target = 0.5

[postgres@ad data10]$ /usr/local/pgsql10/bin/pg_ctl start -D /home/postgres/data10/ －－连接数据库 [postgres@ad data10]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres psql (10devel) Type "help" for help. postgres=# 

create table order_range(id bigserial not null,userid integer,product text, createdate date) partition by range ( createdate );

create table order_range(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate ); create table order_range_201701 partition of order_range(id primary key,userid,product, createdate) for values from ('2017-01-01') to ('2017-02-01'); create table order_range_201702 partition of order_range(id primary key,userid,product, createdate) for values from ('2017-02-01') to ('2017-03-01');

postgres=# \d+ order_range Table "public.order_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+---------+-----------+----------+-----------------------------------------+----------+--------------+------------- id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | | userid | integer | | | | plain | | product | text | | | | extended | | createdate | date | | not null | | plain | | Partition key: RANGE (createdate) Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'), order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01') postgres=# 

create table order_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list( area );

create table order_list_gd partition of order_list(id primary key,userid,product,area,createdate) for values in ('广东'); create table order_list_bj partition of order_list(id primary key,userid,product,area,createdate) for values in ('北京'); 

create table order_range_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list ( area );

create table order_range_list_gd partition of order_range_list for values in ('广东') partition by range(createdate); create table order_range_list_bj partition of order_range_list for values in ('北京') partition by range(createdate); 

create table order_range_list_gd_201701 partition of order_range_list_gd(id primary key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01'); create table order_range_list_gd_201702 partition of order_range_list_gd(id primary key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01'); create table order_range_list_bj_201701 partition of order_range_list_bj(id primary key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01'); create table order_range_list_bj_201702 partition of order_range_list_bj(id primary key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01'); 

postgres=# insert into order_range (userid,product,createdate) values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+ (random()*31)::integer)); INSERT 0 1 Time: 25.006 ms postgres=# insert into order_range (userid,product,createdate) values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+ (random()*31)::integer)); INSERT 0 1 Time: 7.601 ms postgres=# select * from order_range; id | userid | product | createdate ----+--------+----------------------------------+------------ 1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17 2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27 (2 rows)

postgres=# alter table order_range detach partition order_range_201701; ALTER TABLE Time: 14.129 ms

postgres=# \d+ order_range; Table "public.order_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+---------+-----------+----------+-----------------------------------------+----------+--------------+------------- id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | | userid | integer | | | | plain | | product | text | | | | extended | | createdate | date | | not null | | plain | | Partition key: RANGE (createdate) Partitions: order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01') postgres=# 

postgres=# select * from order_range; id | userid | product | createdate ----+--------+---------+------------ (0 rows) Time: 0.505 ms

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！