Postgresql 分区表测试

--创建父表
drop table  num_master;
create table num_master (id int not null primary key); 


--创建生成分区表的函数
create or replace function create_partition_table () returns void as $$
declare i int;
declare cnt int;
declare stmt text;
begin
i:= 0;
cnt:=4;
<<lable1>> while i < cnt loop
  stmt := 'create table num_slave'||i+1||'(check(id >='||i*100||' and id <'||(i+1)*100||')) inherits(num_master)';
  execute stmt;
  i:=i + 1;
end loop lable1;
return;
end;
$$ language plpgsql;


--执行分区表的创建
select create_partition_table ();


--创建触发器函数
create or replace function num_insert_trigger()
returns trigger as $$
begin
if (new.id >=0 and new.id <100) then
insert into num_slave1 values (new.*);
elsif (new.id >=100 and new.id <200) then
insert into num_slave2 values(new.*);
elsif (new.id >=200 and new.id <300) then
insert into num_slave3 values (new.*);
elsif (new.id >=300 and new.id <400) then
insert into num_slave4 values (new.*);
else
raise exception 'Column id out of range.';
end if;
return null;
end;
$$ language plpgsql;


--创建触发器
CREATE TRIGGER insert_num_master_trigger
BEFORE INSERT ON num_master 
FOR EACH ROW EXECUTE PROCEDURE num_insert_trigger();




test=# \d num_master
  Table "public.num_master"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "num_master_pkey" PRIMARY KEY, btree (id)
Number of child tables: 4 (Use \d+ to list them.)


test=#  \d+ num_master
                      Table "public.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_master_trigger BEFORE INSERT ON num_master FOR EACH ROW EXECUTE PROCEDURE num_insert_trigger()
Child tables: num_slave1,
              num_slave2,
              num_slave3,
              num_slave4
Has OIDs: no


--插入测试数据
\timing on
insert into num_master select id from (select generate_series(1,399))ids(id);


--查看测试的数据
test=# select count(*) from num_master;
 count 
-------
   399
(1 row)


Time: 0.577 ms
test=# select count(*) from num_slave1;
 count 
-------
    99
(1 row)


Time: 0.327 ms
test=# select count(*) from num_slave2;
 count 
-------
   100
(1 row)


Time: 0.304 ms
test=# select count(*) from num_slave3;
 count 
-------
   100
(1 row)


Time: 0.424 ms
test=# select count(*) from num_slave4;
 count 
-------
   100
(1 row)


--查看查询下优化器的选择
test=# explain select * from num_master where id > 30 and id < 120; 
                           QUERY PLAN                            
-----------------------------------------------------------------
 Append  (cost=0.00..4.98 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.48 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)
可以看到，选择了分区表进行查询