PostgreSQL的对于应用透明的分区表需要依赖RULE或TRIGGER来实现，应用-awareness的除外。因此在数据库端做分区需要消耗大
量的CPU资源。
那么ORACLE的分区表是不是也要消耗大量CPU资源呢？
下面来简单的测试一下：
ORACLE：
分区
表
create table tbl_user(id int,firstname varchar2(64),lastname
varchar2(64),corp varchar2(64),age int,primary key (id)) partition by
range ( id ) (
partition p_1 values less than (-14000001),
partition
p_2 values less than (-13000001),
partition p_3 values less than
(-12000001),
partition p_4 values less than (-11000001),
partition
p_5 values less than (-10000001),
partition p_6 values less than
(-9000001),
partition p_7 values less than (-8000001),
partition
p_8 values less than (-7000001),
partition p_9 values less than
(-6000001),
partition p_10 values less than (-5000001),
partition
p_11 values less than (-4000001),
partition p_12 values less than
(-3000001),
partition p_13 values less than (-2000001),
partition
p_14 values less than (-1000001),
partition p_15 values less than
(-1),
partition p_16 values less than (1000001)
);
SQL>
insert into tbl_user(id,firstname,lastname,corp,age) select
level,'zhou','digoal','sky-mobi',27 from dual connect by level
<1000001;
1000000 rows created.
Elapsed: 00:00:04.71
SQL>
commit;
Elapsed: 00:00:00.02
SQL> select count(*) from
tbl_user partition (p_16);
COUNT(*)
----------
1000000
SQL>
drop table tbl_user;
Elapsed: 00:00:36.76
插入耗时 : 4730ms
单表
SQL>
create table tbl_user (id int,firstname varchar2(64),lastname
varchar2(64),corp varchar2(64),age int,primary key (id));
Elapsed:
00:00:00.03
SQL> insert into
tbl_user(id,firstname,lastname,corp,age) select
level,'zhou','digoal','sky-mobi',27 from dual connect by level
<1000001;
1000000 rows created.
Elapsed: 00:00:04.43
SQL> commit;
Elapsed: 00:00:00.02
SQL> drop table
tbl_user;
Elapsed: 00:00:33.44
插入耗时 : 4450ms
PostgreSQL :
分
区表
create table tbl_user (id int,firstname varchar(64),lastname
varchar(64),corp varchar(64) ,age int,primary key (id));
CREATE
OR REPLACE FUNCTION digoal.f_create_table(i_min integer, i_max integer)
RETURNS
void
LANGUAGE plpgsql
AS $function$
declare
begin
for i
in i_min..i_max loop
execute 'create table tbl_user_'||i||' (like
tbl_user including constraints including defaults including indexes)
inherits (tbl_user);';
end loop;
return;
end;
$function$;
select
* from f_create_table(0,15);
CREATE OR REPLACE FUNCTION
tbl_user_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF
( NEW.id < -14000001 ) THEN
INSERT INTO tbl_user_0 VALUES
(NEW.*);
ELSIF ( NEW.id < -13000001 and NEW.id >=
-14000001 ) THEN
INSERT INTO tbl_user_1 VALUES (NEW.*);
ELSIF ( NEW.id < -12000001 and NEW.id >= -13000001 ) THEN
INSERT INTO tbl_user_2 VALUES (NEW.*);
ELSIF ( NEW.id <
-11000001 and NEW.id >= -12000001 ) THEN
INSERT INTO
tbl_user_3 VALUES (NEW.*);
ELSIF ( NEW.id < -10000001 and
NEW.id >= -11000001 ) THEN
INSERT INTO tbl_user_4 VALUES
(NEW.*);
ELSIF ( NEW.id < -9000001 and NEW.id >= -10000001 )
THEN
INSERT INTO tbl_user_5 VALUES (NEW.*);
ELSIF (
NEW.id < -8000001 and NEW.id >= -9000001 ) THEN
INSERT
INTO tbl_user_6 VALUES (NEW.*);
ELSIF ( NEW.id < -7000001 and
NEW.id >= -8000001 ) THEN
INSERT INTO tbl_user_7 VALUES
(NEW.*);
ELSIF ( NEW.id < -6000001 and NEW.id >= -7000001 )
THEN
INSERT INTO tbl_user_8 VALUES (NEW.*);
ELSIF (
NEW.id < -5000001 and NEW.id >= -6000001 ) THEN
INSERT
INTO tbl_user_9 VALUES (NEW.*);
ELSIF ( NEW.id < -4000001 and
NEW.id >= -5000001 ) THEN
INSERT INTO tbl_user_10 VALUES
(NEW.*);
ELSIF ( NEW.id < -3000001 and NEW.id >= -4000001 )
THEN
INSERT INTO tbl_user_11 VALUES (NEW.*);
ELSIF (
NEW.id < -2000001 and NEW.id >= -3000001 ) THEN
INSERT
INTO tbl_user_12 VALUES (NEW.*);
ELSIF ( NEW.id < -1000001 and
NEW.id >= -2000001 ) THEN
INSERT INTO tbl_user_13 VALUES
(NEW.*);
ELSIF ( NEW.id < -1 and NEW.id >= -1000001 ) THEN
INSERT INTO tbl_user_14 VALUES (NEW.*);
ELSIF ( NEW.id <
1000001 and NEW.id >= -1 ) THEN
INSERT INTO tbl_user_15
VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'ID out of
range. Please fix the tbl_user_insert_trigger() function!';
END
IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE
TRIGGER insert_tbl_user_trigger
BEFORE INSERT ON tbl_user
FOR EACH ROW EXECUTE PROCEDURE tbl_user_insert_trigger();
digoal=>
insert into tbl_user select
generate_series(1,1000000),'zhou','digoal','sky-mobi',27;
INSERT 0 0
Time:
30157.443 ms
digoal=> select count(*) from tbl_user_15;
count
---------
1000000
digoal=> insert into tbl_user
select
generate_series(-15000001,-14000000),'zhou','digoal','sky-mobi',27;
INSERT
0 0
Time: 21707.555 ms
digoal=> select count(*) from
tbl_user_0;
count
---------
1000000
插入耗时 : 30157ms
PostgreSQL
分区表的插入性能完全取决于触发器的开销，你会发现第一次判断跳出和最后一次判断跳出相差了30157-21707=8450ms。
单表
digoal=> insert into tbl_user_info_single select
generate_series(-15000001,-14000000),'zhou','digoal','sky-mobi',27;
INSERT
0 1000002
Time: 1469.540 ms
插入耗时 1469ms
分区表比较:
oracle
insert 100W 耗时 4730ms
postgresql insert 100W 耗时 30157ms
单表比较:
oracle
insert 100W 耗时 4450ms
postgresql insert 100W 耗时 1469ms
从以上对比可以看
出，如果要做应用透明的分区，PostgreSQL不如Oracle。如果做application-awareness的分区，也就是让应用来处理分区
的代码，PostgreSQL的性能好过Oracle。在做PostgreSQL开发是开发人员需要认识到这点。