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).
I found so many interesting stuff in your blog especially its discussion. Libros ePub Download
ReplyDelete