Chinaunix首页 | 论坛 | 博客
  • 博客访问: 305260
  • 博文数量: 81
  • 博客积分: 3813
  • 博客等级: 中校
  • 技术积分: 945
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-24 18:14
文章分类

全部博文(81)

文章存档

2013年(1)

2012年(2)

2011年(54)

2010年(15)

2009年(9)

分类: Mysql/postgreSQL

2011-03-16 16:02:32

Thanks Digoal

PostgreSQL for larger table, use partiton table to improved the performance.

The partition arithmetic use trigger to achieve. The good arithmetic can reduced CPU cost. In particular, the partition table in the large amount of concurrentoperations obviously.

Expample:

Parent table:
          Table "public.tbl_user"
  Column   |         Type          | Modifiers 
----------- ----------------------- -----------
 id        | integer               | not null
 firstname | character varying(30) | 
 lastname  | character varying(30) | 
 corp      | character varying(20) | 
 age       | integer               | 
Indexes:
    "tbl_user_pk" PRIMARY KEY, btree (id)
    "idx_user_age" btree (age)
Triggers:
    delete_tbl_user_trigger BEFORE DELETE ON tbl_user FOR EACH ROW EXECUTE PROCEDURE tbl_user_delete_trigger()
    insert_tbl_user_trigger BEFORE INSERT ON tbl_user FOR EACH ROW EXECUTE PROCEDURE tbl_user_insert_trigger()
Number of child tables: 16 (Use \d to list them.)

Child table:
        tbl_user0  
        tbl_user1  
        tbl_user10 
        tbl_user11 
        tbl_user12 
        tbl_user13 
        tbl_user14 
        tbl_user15 
        tbl_user2  
        tbl_user3  
        tbl_user4  
        tbl_user5  
        tbl_user6  
        tbl_user7  
        tbl_user8  
        tbl_user9  

Create child table function SQL :

CREATE OR REPLACE FUNCTION 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$ ;

Check Function:

CREATE OR REPLACE FUNCTION f_ck_table(i_min integer, i_max integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
begin
for i in i_min..i_max loop
execute 'alter table tbl_user'||i||' add constraint ck_user'||i||' check (mod(id,16)='||i||');';
end loop;
return;
end;
$function$ ;

Create trigger function and trigger :

CREATE OR REPLACE FUNCTION tbl_userinsert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF    ( mod(NEW.id,16) = 0 ) THEN
        INSERT INTO tbl_user0 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 1 ) THEN
        INSERT INTO tbl_user1 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 2 ) THEN
        INSERT INTO tbl_user2 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 3 ) THEN
        INSERT INTO tbl_user3 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 4 ) THEN
        INSERT INTO tbl_user4 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 5 ) THEN
        INSERT INTO tbl_user5 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 6 ) THEN
        INSERT INTO tbl_user6 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 7 ) THEN
        INSERT INTO tbl_user7 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 8 ) THEN
        INSERT INTO tbl_user8 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 9 ) THEN
        INSERT INTO tbl_user9 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 10 ) THEN
        INSERT INTO tbl_user10 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 11 ) THEN
        INSERT INTO tbl_user11 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 12 ) THEN
        INSERT INTO tbl_user12 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 13 ) THEN
        INSERT INTO tbl_user13 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 14 ) THEN
        INSERT INTO tbl_user14 VALUES (NEW.*);
    ELSIF ( mod(NEW.id,16) = 15 ) THEN
        INSERT INTO tbl_user15 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'ID out of range.  Please fix the tbl_userinsert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tbl_userdelete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF    ( mod(OLD.id,16) = 0 ) THEN
        DELETE FROM tbl_user0 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 1 ) THEN
        DELETE FROM tbl_user1 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 2 ) THEN
        DELETE FROM tbl_user2 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 3 ) THEN
        DELETE FROM tbl_user3 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 4 ) THEN
        DELETE FROM tbl_user4 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 5 ) THEN
        DELETE FROM tbl_user5 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 6 ) THEN
        DELETE FROM tbl_user6 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 7 ) THEN
        DELETE FROM tbl_user7 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 8 ) THEN
        DELETE FROM tbl_user8 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 9 ) THEN
        DELETE FROM tbl_user9 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 10 ) THEN
        DELETE FROM tbl_user10 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 11 ) THEN
        DELETE FROM tbl_user11 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 12 ) THEN
        DELETE FROM tbl_user12 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 13 ) THEN
        DELETE FROM tbl_user13 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 14 ) THEN
        DELETE FROM tbl_user14 where id=OLD.id;
    ELSIF ( mod(OLD.id,16) = 15 ) THEN
        DELETE FROM tbl_user15 where id=OLD.id;
    ELSE
        RAISE EXCEPTION 'ID out of range.  Please fix the tbl_userdelete_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();

CREATE TRIGGER delete_tbl_user_trigger
    BEFORE DELETE ON tbl_user
    FOR EACH ROW EXECUTE PROCEDURE tbl_user_delete_trigger();


Performace testing:

insert into tbl_user select generate_series(0,16000000,16),'Huang','Kyle','Bsmart sz',27;
Time: 47938 (ms)

truncate table tbl_user;
Time: 482 (ms)

insert into tbl_user select generate_series(15,16000000,16),'Huang','Kyle','Bsmart sz',27; 
Time: 63191 (ms)

bmcv3=# select count(*) from tbl_user;
  count  
---------
 1000000
(1 row)

(Accurate arrived in target table)
bmcv3=# select count(*) from tbl_user15;
  count  
---------
 1000000
(1 row)
each insert command that check from first to last cost time :  (63191 - 47938)/ 100w = 0.015253 ms

not use trigger 
insert into tbl_user_single select generate_series(0,16000000,16),'Huang','Kyle','Bsmart sz',27; 
Time: 8599 (ms)

each check cost time = (47938 - 8599) / 100w = 0.039339 ms
total cost time = 0.054592



阅读(399) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~