[Original] POSTGRESQL partition table first experience

Different from MYSQL, POSTGRESQL has a dedicated partition table, while POSTGRESQL uses its object-oriented features for partitioning. Next we will give you a simple experience. Create a parent table first. Note

Different from MYSQL, POSTGRESQL has a dedicated partition table, while POSTGRESQL uses its object-oriented features for partitioning. Next we will give you a simple experience. Create a parent table first. Note

Different from MYSQL, POSTGRESQL has a dedicated partition table, while POSTGRESQL uses its object-oriented features for partitioning. Next we will give you a simple experience.

Create a parent table first. Remember, all partition tables must inherit from them.

T_girl = # create table num_master (id int not null primary key); CREATE TABLE

Next we will create a simple function to dynamically create a partition table.

T_girl = # create or replace function create_partition_table () returns void as $ t_girl $ # declare I int; t_girl $ # declare cnt int; t_girl $ # declare stmt text; t_girl $ # begint_girl $ # -- Created by ytt at 2013/12/15. dynamic creating partition tables. t_girl $ # I: = 0; t_girl $ # cnt: = 4; t_girl $ # < > While I <cnt loopt_girl $ # stmt: = 'create table num_slave '| I + 1 |' (check (id> = '| I * 100 |' and id <'| (I + 1) * 100 | ') inherits (num_master)'; t_girl $ # execute stmt; t_girl $ # I: = I + 1; t_girl $ # end loop lable1; t_girl $ # return; t_girl $ # end; t_girl $ # $ language plpgsql; CREATE FUNCTIONt_girl = #

OK. Now you can execute.

T_girl = # select create_partition_table (); create_partition_table ---------------------- (1 row)

List all tables

T_girl = # \ dList of relations Schema | Name | Type | Owner -------- + ------------ + ------- + ---------- ytt | num_master | table | S ytt | num_slave1 | table | postgres ytt | num_slave2 | table | S ytt | num_slave3 | table | postgres ytt | num_slave4 | table | postgres ytt | t1 | table | t_girl (6 rows)

We create a trigger function body for the parent table, corresponding to the data distribution of its partition table.

T_girl = # create or replace function num_insert_trigger () t_girl-# returns trigger as $ t_girl $ # begint_girl $ # -- Created by ytt at 2013/12/15. do how to distribute data. t_girl $ # if (new. id> = 0 and new. id <100) thent_girl $ # insert into num_slave1 values (new. *); t_girl $ # elsif (new. id> = 100 and new. id <200) thent_girl $ # insert into num_slave2 values (new. *); t_girl $ # elsif (new. id> = 200 and new. id <300) thent_girl $ # insert into num_slave3 values (new. *); t_girl $ # elsif (new. id> = 300 and new. id <400) thent_girl $ # insert into num_slave4 values (new. *); t_girl $ # elset_girl $ # raise exception 'column id out of range. '; t_girl $ # end if; t_girl $ # return null; t_girl $ # end; t_girl $ # $ t_girl-# language plpgsql; CREATE FUNCTION

Let's take a look at the created trigger:

T_girl = # \ d + num_masterTable "ytt. num_master "Column | Type | Modifiers | Storage | Stats target | Description -------- + --------- + ----------- + --------- + -------------- + ------------- id | integer | not null | plain | Indexes: "num_master_pkey" primary key, btree (id) Triggers: insert_num_slave_trigger before insert on num_master for each row execute procedure ytt. num_insert_trigger () Child tables: num_slave1, num_slave2, num_slave3, num_slave4Has OIDs: no

We now generate simple test data.

T_girl = # select func_create_sample_data (); func_create_sample_data ----------------------- (1 row)

The above function generates approximately 400 rows of data.

To view how the optimizer handles queries, let's look at simple queries.

T_girl = # explain select * from num_master where id> 30 and id <120; query plan --------------------------------------------------------------- Append (cost = 0. 00 .. 5.00 rows = 91 width = 4)-> Seq Scan on num_master (cost = 0. 00 .. 0.00 rows = 1 width = 4) Filter: (id> 30) AND (id <120)-> Seq Scan on num_slave1 (cost = 0. 00 .. 2.50 rows = 70 width = 4) Filter: (id> 30) AND (id <120)-> Seq Scan on num_slave2 (cost = 0. 00 .. 2.50 rows = 20 width = 4) Filter: (id> 30) AND (id <120) (7 rows) t_girl = #

I am also new to POSTGRESQL's partition table. I hope to raise some questions.

This article is from "god, let's see it !" Blog, please keep this source

,