Shulou(Shulou.com)06/02 Report--

1. Establish the master table

CREATE TABLE part_tab (C1 int, c2 int,name varchar (20)) PARTITION BY RANGE (C1)

two。 Create a sub-partition table

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

3. Attach an existing table as a child partition of the main table

1) .attach partition (additional table partition)

Before the additional partition command is executed, the table to which the partition is to be attached must already exist

Create table ext_part (C1 int not null, c2 int,name varchar (20))

Before attaching a partition, the data to be attached to the partition must meet the constraints of the partition column of the main table, and if the condition is not met, the new partition cannot be attached to the main table.

ALTER TABLE part_tab ATTACH PARTITION ext_part FOR VALUES FROM (400,500) to

2) .detach partition (remove partition table)

Unbind partitions, and the\ d + command shows that the partitions that have been removed are not included in the partition table.

ALTER TABLE part_tab DETACH PARTITION ext_part

4. Prepare an external server (child table server)

$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. Increase expansion

$psql testdb

# create extension postgres_fdw

# create server server_remote_226 foreign data wrapper postgres_fdw options (host '172.16.3.226 recording journal port' 5432 mingling dbName 'testdb')

# create user mapping for postgres server server_remote_226 options (user 'postgres',password' 111111')

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, port' 5432, dbnam)

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 an external table

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. Insert test data

-- external partitioned tables cannot insert data through the main table, but need to be inserted through external tables

Testdb=# insert into part_tab values, (101pas), (2011pas), (301pas), (301pas)

ERROR: cannot route inserted tuples to a foreign table

Testdb=#

The data is inserted on each server.

Master table server, inserting local partition data through the master table

# insert into part_tab values (1pyrrine and 1jinchis), (101pr. 101pr.)

INSERT 0 2

Testdb=#

External table servers, inserted separately through external tables

Testdb=# insert into part3 values (2011pm 2011William)

INSERT 0 1

Testdb=# insert into part4 values (301 and 301)

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=#

The external partition table has no constraint on the insertion of partition columns in violation of the partition mechanism, so that the data can be inserted arbitrarily.

Testdb=# insert into part4 values (201pc301)

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. Add primary keys and constraints

Primary table partition columns do not support the establishment of primary key constraints

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=#

-constraints and indexes need to be added in the child table

-- Master table server

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

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,200) TO

Indexes:

"part2_c1_pkey" PRIMARY KEY, btree (C1)

"idx_part2_c1_c2_name" btree (C1, c2, name)

Testdb=#

-- Child table server

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. Partition pruning of query statement

Testdb=# explain analyze select * from part_tab where c1o1

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 c114101

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 c1y201

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 c1o301

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 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=#

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.