Pages

Tuesday, August 17, 2010

divide a table into a partitions

Hello I had to verify a speed of two methods of dividing data to partion. First method is based on dynamic SQL, second is based on cursor scan and prepared statements. I was surprised - both variants has a same speed.
CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  FOR i IN 0..9
  LOOP
    RAISE NOTICE '***** % *****', i;
    EXECUTE 'TRUNCATE TABLE ' || ('x' || i)::regclass;
    EXECUTE 'INSERT INTO ' || ('x' || i)::regclass ||
                ' SELECT * FROM x WHERE a >= $1 AND a < $1 + 100000'
       USING i * 100000;
  END LOOP;
  RETURN;
END;
$function$
This variant is shorter, but needs a active index on source table.
CREATE OR REPLACE FUNCTION public.foo1()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  c CURSOR FOR SELECT * FROM x;
  r RECORD;
BEGIN
  TRUNCATE TABLE x0;
  TRUNCATE TABLE x1;
  TRUNCATE TABLE x2;
  TRUNCATE TABLE x3;
  TRUNCATE TABLE x4;
  TRUNCATE TABLE x5;
  TRUNCATE TABLE x6;
  TRUNCATE TABLE x7;
  TRUNCATE TABLE x8;
  TRUNCATE TABLE x9;

  FOR r IN c
  LOOP
    CASE
        WHEN r.a BETWEEN 0 AND 99999 THEN
            INSERT INTO x0 VALUES(r.a);
        WHEN r.a BETWEEN 100000 AND 199999 THEN
            INSERT INTO x1 VALUES(r.a);
        WHEN r.a BETWEEN 200000 AND 299999 THEN
            INSERT INTO x2 VALUES(r.a);
        WHEN r.a BETWEEN 300000 AND 399999 THEN
            INSERT INTO x3 VALUES(r.a);
        WHEN r.a BETWEEN 400000 AND 499999 THEN
            INSERT INTO x4 VALUES(r.a);
        WHEN r.a BETWEEN 500000 AND 599999 THEN
            INSERT INTO x5 VALUES(r.a);
        WHEN r.a BETWEEN 600000 AND 699999 THEN
            INSERT INTO x6 VALUES(r.a);
        WHEN r.a BETWEEN 700000 AND 799999 THEN
            INSERT INTO x7 VALUES(r.a);
        WHEN r.a BETWEEN 800000 AND 899999 THEN
            INSERT INTO x8 VALUES(r.a);
        WHEN r.a BETWEEN 900000 AND 1000000 THEN
            INSERT INTO x9 VALUES(r.a);
    END CASE;
  END LOOP;
END;
$function$

This variant is longer, but doesn't need a active index on source table.

1 comment: