PostgreSQL 10 Beta1分区和分区外部表测试说明
|
1.建立主表 CREATE TABLE part_tab (c1 int,c2 int,name varchar(20)) PARTITION BY RANGE (c1); 2.建立子分区表 CREATE TABLE part1 PARTITION OF part_tab FOR VALUES FROM (0) TO (100); CREATE TABLE part2 PARTITION OF part_tab FOR VALUES FROM (100) TO (200); 3.附加已经存在的表作为主表的子分区 1).attach partition（附加表分区） -- 执行附加分区命令前，要附加分区的表必须已经存在 create table ext_part(c1 int not null,name varchar(20)); 附加分区前，要附加分区的数据必须满足主表分区列的约束条件，如果不满足条件则无法把新的分区附加到主表。 ALTER TABLE part_tab ATTACH PARTITION ext_part FOR VALUES FROM (400) to (500); 2).detach partition（摘除分区表） --解除分区绑定，解除后d+命令显示分区表中就不包含已经摘除的分区。 ALTER TABLE part_tab DETACH PARTITION ext_part; 4.准备外部服务器(子表服务器) $ psql testdb # create table fpart3 (c1 int not null,name varchar(20)); # create table fpart4 (c1 int not null,name varchar(20)); testdb=# dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | fpart3 | table | postgres public | fpart4 | table | postgres (2 rows) testdb=# 5.增加扩展 $ psql testdb # create extension postgres_fdw; # create server server_remote_226 foreign data wrapper postgres_fdw options(host '172.16.3.226',port '5432',dbname 'testdb'); # create user mapping for postgres server server_remote_226 options(user 'postgres',password '222221'); testdb=# des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description -------------------+----------+----------------------+-------------------+------+---------+----------------------------------------- ------------+------------- server_remote_226 | postgres | postgres_fdw | | | | (host '172.16.3.226',dbnam e 'testdb') | (1 row) testdb=# deu+ List of user mappings Server | User name | FDW Options -------------------+-----------+---------------------------------------- server_remote_226 | postgres | ("user" 'postgres',password '222221') (1 row) 6.建立外部表 CREATE FOREIGN TABLE part3 PARTITION OF part_tab FOR VALUES FROM (200) TO (300) SERVER server_remote_226 options (schema_name 'public',table_name 'fpart3'); CREATE FOREIGN TABLE part4 PARTITION OF part_tab FOR VALUES FROM (300) TO (400) SERVER server_remote_226 options (schema_name 'public',table_name 'fpart4'); testdb=# det+ List of foreign tables Schema | Table | Server | FDW Options | Description --------+-------+-------------------+---------------------------------------------+------------- public | part3 | server_remote_226 | (schema_name 'public',table_name 'fpart3') | public | part4 | server_remote_226 | (schema_name 'public',table_name 'fpart4') | (2 rows) testdb=# 7.插入测试数据 -- 外部分区表无法通过主表插入数据,需要通过外部表方式插入 testdb=# insert into part_tab values(1,1,'Chris'),(101,101,'Peter'),(201,201,'William'),(301,301,'Feng'); ERROR: cannot route inserted tuples to a foreign table testdb=# 数据分别在各自服务器插入 主表服务器,通过主表插入本地分区数据 # insert into part_tab values(1,'Peter'); INSERT 0 2 testdb=# 外部表服务器，通过外部表分别插入 testdb=# insert into part3 values(201,'William'); INSERT 0 1 testdb=# insert into part4 values(301,'Feng'); INSERT 0 1 testdb=# testdb=# testdb=# select * from part_tab ; c1 | c2 | name -----+-----+--------- 1 | 1 | Chris 101 | 101 | Peter 201 | 201 | William 301 | 301 | Feng (4 rows) testdb=# 外部分区表对违反分区列的插入无约束机制，这样的数据可以任意插入。 testdb=# insert into part4 values(201,'Feng'); INSERT 0 1 testdb=# select * from part_tab ; c1 | c2 | name -----+-----+--------- 1 | 1 | Chris 101 | 101 | Peter 201 | 201 | William 301 | 301 | Feng 201 | 301 | Feng (5 rows) testdb=# 8.添加主键和约束 -- 主表分区列不支持建立主键约束 testdb=# alter table part_tab add constraint part_tab_c1_pkey primary key(c1); ERROR: primary key constraints are not supported on partitioned tables LINE 1: alter table part_tab add constraint part_tab_c1_pkey primary... ^ testdb=# --- 约束、索引需在子表添加 --主表服务器 testdb=# alter table part1 add constraint part1_c1_pkey primary key(c1); ALTER TABLE testdb=# alter table part2 add constraint part2_c1_pkey primary key(c1); ALTER TABLE testdb=# testdb=# create index idx_part1_c1_c2_name on part1(c1,c2,name); CREATE INDEX testdb=# create index idx_part2_c1_c2_name on part2(c1,name); CREATE INDEX testdb=# testdb=# d part1 Table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | | name | character varying(20) | | | Partition of: part_tab FOR VALUES FROM (0) TO (100) Indexes: "part1_c1_pkey" PRIMARY KEY,btree (c1) "idx_part1_c1_c2_name" btree (c1,name) testdb=# testdb=# d part2 Table "public.part2" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | | name | character varying(20) | | | Partition of: part_tab FOR VALUES FROM (100) TO (200) Indexes: "part2_c1_pkey" PRIMARY KEY,btree (c1) "idx_part2_c1_c2_name" btree (c1,name) testdb=# -- 子表服务器 testdb=# alter table fpart3 add constraint fpart3_c1_pkey primary key(c1); ALTER TABLE testdb=# alter table fpart4 add constraint fpart4_c1_pkey primary key(c1); ALTER TABLE testdb=# create index idx_fpart3_c1_c2_name on fpart3(c1,name); CREATE INDEX testdb=# create index idx_fpart4_c1_c2_name on fpart4(c1,name); CREATE INDEX testdb=# testdb=# d fpart3 Table "public.fpart3" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | | name | character varying(20) | | | Indexes: "fpart3_c1_pkey" PRIMARY KEY,btree (c1) "idx_fpart3_c1_c2_name" btree (c1,name) testdb=# testdb=# d fpart4 Table "public.fpart4" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | integer | | not null | c2 | integer | | | name | character varying(20) | | | Indexes: "fpart4_c1_pkey" PRIMARY KEY,btree (c1) "idx_fpart4_c1_c2_name" btree (c1,name) testdb=# 9.查询语句的分区修剪 testdb=# explain analyze select * from part_tab where c1=1; QUERY PLAN ------------------------------------------------------------------------------------------------------ Append (cost=0.00..1.01 rows=1 width=66) (actual time=0.009..0.009 rows=1 loops=1) -> Seq Scan on part1 (cost=0.00..1.01 rows=1 width=66) (actual time=0.008..0.009 rows=1 loops=1) Filter: (c1 = 1) Planning time: 0.234 ms Execution time: 0.027 ms (5 rows) testdb=# explain analyze select * from part_tab where c1=101; QUERY PLAN ------------------------------------------------------------------------------------------------------ Append (cost=0.00..1.01 rows=1 width=66) (actual time=0.025..0.028 rows=1 loops=1) -> Seq Scan on part2 (cost=0.00..1.01 rows=1 width=66) (actual time=0.024..0.026 rows=1 loops=1) Filter: (c1 = 101) Planning time: 0.271 ms Execution time: 0.066 ms (5 rows) testdb=# explain analyze select * from part_tab where c1=201; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Append (cost=100.00..121.47 rows=5 width=66) (actual time=2.179..2.180 rows=1 loops=1) -> Foreign Scan on part3 (cost=100.00..121.47 rows=5 width=66) (actual time=2.178..2.178 rows=1 loops=1) Planning time: 0.308 ms Execution time: 3.551 ms (4 rows) testdb=# explain analyze select * from part_tab where c1=301; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Append (cost=100.00..121.47 rows=5 width=66) (actual time=1.218..1.219 rows=1 loops=1) -> Foreign Scan on part4 (cost=100.00..121.47 rows=5 width=66) (actual time=1.217..1.218 rows=1 loops=1) Planning time: 0.312 ms Execution time: 2.178 ms (4 rows) testdb=# explain analyze select * from part_tab where c1<201; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Append (cost=0.00..129.46 rows=305 width=66) (actual time=0.014..2.881 rows=2 loops=1) -> Seq Scan on part1 (cost=0.00..1.01 rows=1 width=66) (actual time=0.014..0.015 rows=1 loops=1) Filter: (c1 < 201) -> Seq Scan on part2 (cost=0.00..1.01 rows=1 width=66) (actual time=0.009..0.009 rows=1 loops=1) Filter: (c1 < 201) -> Foreign Scan on part3 (cost=100.00..127.44 rows=303 width=66) (actual time=2.855..2.855 rows=0 loops=1) Planning time: 0.234 ms Execution time: 3.884 ms (8 rows) testdb=# （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |