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, c2 int,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, c2 int,name varchar(20));

# create table fpart4 (c1 int not null, c2 int,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 '111111');

testdb=# \des+

List of foreign servers

| Description

-------------------+----------+----------------------+-------------------+------+---------+-----------------------------------------

------------+-------------

e 'testdb') |

(1 row)

testdb=# \deu+

List of user mappings

Server | User name | FDW Options

-------------------+-----------+----------------------------------------

server_remote_226 | postgres | ("user" 'postgres', password '111111')

(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,1,'Chris'),(101,101,'Peter');

INSERT 0 2

testdb=#

外部表服务器，通过外部表分别插入

testdb=# insert into part3 values(201,201,'William');

INSERT 0 1

testdb=# insert into part4 values(301,301,'Feng');

INSERT 0 1

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,301,'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,c2,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, c2, 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, c2, 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,c2,name);

CREATE INDEX

testdb=# create index idx_fpart4_c1_c2_name on fpart4(c1,c2,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, c2, 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, c2, 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=#

本文出自 “yiyi” 博客，请务必保留此出处http://heyiyi.blog.51cto.com/205455/1927626

秒客网

PostgreSQL 10 Beta1分区和分区外部表测试说明

相关文章