Re: PostgreSQL Function Language Performance: C vs PL/PGSQL

* Eliot Gable (egable+pgsql-performance@gmail.com) wrote:
> Would a query such as this obtain any performance improvement by being
> re-written using C?

I wouldn't expect the queries called by the pl/pgsql function to be much
faster if called through SPI from C instead.  I think the question you
need to answer is- how long does the pl/pgsql code take vs. the overall
time the function takes as a whole?  You could then consider that your
'max benefit' (or pretty close to it) which could be gained by rewriting
it in 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)?

Cases where a function is called over and over again, or there are loops
which go through tons of data, or there's alot of data processing to be
done.

> Are there specific cases where writing a function in C would be slower than
> writing the equivalent in PL/PGSQL?

Probably not- provided the C code is written correctly.  You can
certainly screw that up (eg: not preparing a query in C and having PG
replan it every time would probably chew up any advantage C has over
pl/pgsql, in a simple function).

> 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.

Realize that C functions have alot of other issues associated with them-
typically they're much larger foot-guns, for one, for another, C is an
untrusted language because it can do all kinds of bad things.  So you
have to be a superuser to create them.

> 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?

Actually performing a SELECT through SPI vs. calling it from pl/pgsql
probably won't result in that much difference, presuming most of the
time there is in the actual query itself.  Assignments, basic math,
control logic, etc, will all be faster in C.  You need to figure out if
that work is taking enough time to justify the switch though.

> 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?

You might be able to do that when the module is loaded, but I'm not 100%
sure..  Depends on if you can start using SPI in _PG_init..  I think
there was some discussion about that recently but I'm not sure what the
answer was.

> 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?

Uhh, I'd guess 'no' to that one.

       Thanks,

               Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkv9Sd8ACgkQrzgMPqB3kihj/gCdEIA8DhnvZX4Hz3tof6yzLscS
Lf8An2Xp8R/KXnkmp8uWg+84Cz7Pp7R3
=AX4g
-----END PGP SIGNATURE-----