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

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(at)snowman(dot)net> wrote:

> * Eliot Gable (egable+pgsql-performance(at)gmail(dot)com<egable%2Bpgsql-performance(at)gmail(dot)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-----
>
>

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