POSTGRESQL分区表初次体验

t_girl=# create table num_master (id int not null primary key);CREATE TABLEt_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$# begin t_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 loop t_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 FUNCTION t_girl=#t_girl=# select create_partition_table(); create_partition_table ------------------------ (1 row)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)t_girl=# create or replace function num_insert_trigger() t_girl-# returns trigger as $$ t_girl$# begin t_girl$# -- Created by ytt at 2013/12/15. Do how to distribute data. t_girl$# if (new.id >=0 and new.id <100) then t_girl$# insert into num_slave1 values (new.*); t_girl$# elsif (new.id >=100 and new.id <200) then t_girl$# insert into num_slave2 values(new.*); t_girl$# elsif (new.id >=200 and new.id <300) then t_girl$# insert into num_slave3 values (new.*); t_girl$# elsif (new.id >=300 and new.id <400) then t_girl$# insert into num_slave4 values (new.*); t_girl$# else t_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 

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: not_girl=# select func_create_sample_data(); func_create_sample_data ------------------------- (1 row)

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