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:

At May 19, 2015 at 12:13 PM , Anonymous Anonymous said...

Wow - very good news!

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

 
At May 19, 2015 at 11:29 PM , Anonymous Anonymous said...

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).

 
At May 20, 2015 at 12:00 AM , Anonymous Anonymous said...

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...

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home