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

[Eliot Gable <egable+pgsql-performance@xxxxxxxxx>]

Thanks for the quick follow-up. So, you are saying that if I can do SPI in _PG_init, then I could prepare all my queries there and they would be prepared once for the entire function when it is loaded? That would certainly achieve what I want. Does anybody know whether I can do SPI in _PG_init?

The function gets called a lot, but not in the same transaction. It is only called once per transaction. 

On Wed, May 26, 2010 at 12:18 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:

* Eliot Gable (egable+pgsql-performance@xxxxxxxxx) 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-----

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero