I have been Googling for answers on this for a while, and have not been able to find anything satisfactory.
Imagine that you have a stored procedure which is currently written using PL/PGSQL. This stored procedure performs lots of long, complex SQL queries (95% SELECT statements, 5% INSERT or UPDATE) and these queries are interspersed with some minor math and some control logic, along with some logging through the use of RAISE. Each logging statement is inside an IF/THEN which just checks a boolean flag to determine if logging is turned on. The function returns a set of cursors to several different result sets. The function is 50%-60% SQL queries and the rest is logging, control logic, and little bit of math.
Would a query such as this obtain any performance improvement by being re-written using C?
Are there specific cases where writing a function in C would be highly desirable verses using PL/PGSQL (aside from simply gaining access to functionality not present in PL/PGSQL)?
Are there specific cases where writing a function in C would be slower than writing the equivalent in PL/PGSQL?
Basically, I am looking for some guidelines based primarily on performance of when I should use C to write a function verses using PL/PGSQL.
Can anybody quantify any of the performance differences between doing a particular task in C verses doing the same thing in PL/PGSQL? For example, performing a SELECT query or executing a certain number of lines of control logic (primarily IF/THEN, but an occasional loop included)? How about assignments or basic math like addition/subtraction/multiplication/division?
When executing SQL queries inside a C-based function, is there any way to have all of the SQL queries pre-planned through the compilation process, definition of the function, and loading of the .so file similar to PL/PGSQL? Would I get better performance writing each SQL query as a stored procedure and then call these stored procedures from within a C-based function which does the logging, math, control logic, and builds the result sets and cursors?
Thanks in advance for any answers anyone can provide to these questions.