Postgresql – Oracle PL/SQL stored procedure compiler vs PostgreSQL PGSQL stored procedure compiler


I noticed that Oracle takes a while to compile a stored procedure but it runs much faster than its PostgreSQL PGSQL counterpart.

With PostgreSQL, the same procedure (i.e. it's all in SQL-92 format with functions in the select and where clauses) takes less time to compile but longer to run.

Is there a metrics web site where I can find side by side Oracle vs PostgreSQL performance metrics on stored procedures, SQL parsing, views and ref_cursors?

Is the PostgreSQL PGSQL compiler lacking in optimization routines? What are the main differences between how Oracle handles stored procedures and how PostgreSQL handles them?

I'm thinking of writing a PGSQL function library that will allow PL/SQL code to be compiled and run in PGSQL. (i.e. DECODE, NVL, GREATEST, TO_CHAR, TO_NUMBER, all
PL/SQL functions) but I don't know if this is feasible.

Best Solution

This is a hard question to answer fully because it can run pretty deep, but I'll add my 2 cents towards a high level contribution to an answer. First off I really like PostgreSQL and I really like Oracle. However PL/SQL is so much more deep of a language/environment than PL/PGSQL provides or really any other database engines procedure language that I have ever ran into for that matter. Oracle since at least 10G uses a optimizing compiler for PL/SQL. Which most likely contributes to why it compiles slower in your use cases. PL/SQL also has native compilation. You can compile the PL/SQL code down to machine code with a simple compiler directive. This is good for computation intensive logic not for SQL logic. My point of all this is Oracle has spent lots of resources on making PL/SQL a real treat from a functionality standpoint and a performance stand point and I only touched on two of many examples.PL/SQL is light years ahead of PG/SQL is what it sums up to and I don't imagine as nice as PG/SQL is catching up to Oracle any time soon.

I doubt you will find a side by side comparison, though I think this would be really nice. The effort to do so wouldn't probably be worth most people's time. Also I wouldn't re-write what is already out there.