[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. 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$# <<lable1>> 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=# \d           List of relations Schema |    Name    | Type  |  Owner  --------+------------+-------+---------- ytt    | num_master | table | postgres ytt    | num_slave1 | table | postgres ytt    | num_slave2 | table | postgres 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_master                       Table "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 be sure to keep this source http://yueliangdao0608.blog.51cto.com/397025/1340860