Sunday, May 10, 2015

faster plpgsql in PostgreSQL 9.5 (first part)

The PL/pgSQL is important part of PostgreSQL. A implementation of plpgsql is terrible simple, what has two impacts (one positive, one negative).
  • We are able to implement new features very simply - implementation of some new functionality like ASSER or RAISE WITH CONTEXT needs only few hours.
  • Some usual features from other languages are missing or it its implementation is suboptimal. But there are significant progress in prepared PostgreSQL 9.5.
One less known issue of PLpgSQL is casting. PLpgSQL uses IO casting - every time, when type of variables or typmod isnot identical, plpgsql runtime transforms values from source type to text and back to target type. This IO casting is very simple for implementation, but it should be very hidden performance killer. plpgsql_check can raise warnings about it. You can see a impact of this IO cast on following test:
-- tested on PostgreSQL 9.4 (synthetic test, worst case) 
-- IO cast
postgres=# do $$declare s numeric = 0; begin for i in 1..10000000 loop s := i; end loop; end $$;
DO
Time: 6708.727 ms
-- enforced binary casts
postgres=# do $$declare s numeric = 0; begin for i in 1..10000000 loop s := i::numeric; end loop; end $$;
DO
Time: 4738.093 ms
    Tom Lane wrote and commited patch, that enforce binary casting everywhere it is possible. When it is impossible, then IO casting is used as before.
    -- tested on PostgreSQL 9.5
    postgres=# do $$declare s numeric = 0; begin for i in 1..10000000 loop s := i; end loop; end $$;
    DO
    Time: 3992.551 ms
    postgres=# do $$declare s numeric = 0; begin for i in 1..10000000 loop s := i::numeric; end loop; end $$;
    DO
    Time: 3693.739 ms
    
    There is still some overhead, but it is significantly less 30% versus 7.5%. Other positive - PostgreSQL is in this test about 30% faster (it is synthetic test, so reality should be different, but, the expectation so 9.5 will be faster than 9.4 is valid).

    1 Comments:

    At May 19, 2015 at 10:31 PM , Blogger Elizabeth J. Neal said...

    I found so many interesting stuff in your blog especially its discussion. Libros ePub Download

     

    Post a Comment

    Subscribe to Post Comments [Atom]

    Links to this post:

    Create a Link

    << Home