Pages

Monday, May 18, 2015

faster plpgsql in PostgreSQL 9.5 (second part)

Tom Lane introduced new state for TOAST types - expanded state. The side effect of this change together with some changes in plpgsql internals has big impact to speed of array operation in plpgsql.
I have two plpgsql block with cycles. First enforces a array fields update, second a array append.
DO $$ 
DECLARE a int[] = array_fill(10, ARRAY[10000]);
BEGIN
FOR i IN 1 .. 10000 LOOP
  a[i] := 0;
END LOOP;
END;
$$;

DO $$ 
DECLARE a int[] = '{}';
BEGIN
FOR i IN 1 .. 10000 LOOP
  a := a || 10;
END LOOP;
END;
$$;
You can try this code on PostgreSQL 9.4 - and you can get time 450 and 220ms. Same code needs 6 and 5 ms only on PostgreSQL 9.5! It is more than one order speedup.

3 comments:

  1. Wow - very good news!

    I'm a big plv8 fan (except for trigger-functions) - but this looks promising.

    ReplyDelete
  2. What's wrong with pl/V8 trigger functions? (looking to start using pl/V8 soon, so want to find out as much as possible about known problems).

    ReplyDelete
  3. Nothing wrong - it works perfect.
    plv8 is faster in all sitiuations/functions than plpgslq
    except Trigger-Functions (pgslq is about 2 times faster here).

    There seems to be some overhead behind the scenes...

    ReplyDelete