PostgreSQL Function Language Performance: C vs PL/PGSQL

From: Eliot Gable <egable+pgsql-performance(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL Function Language Performance: C vs PL/PGSQL
Date: 2010-05-26 16:06:26
Message-ID: AANLkTinPXHz0GMP6J6eWksip54AiCImJVNO4Yf4vzFm4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephen Frost 2010-05-26 16:18:39 Re: PostgreSQL Function Language Performance: C vs PL/PGSQL
Previous Message Joachim Worringen 2010-05-26 16:03:15 Re: performance of temporary vs. regular tables