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

On Wed, May 26, 2010 at 12:41 PM, Eliot Gable
<egable+pgsql-performance(at)gmail(dot)com> wrote:
> Ah, that clears things up. Yes, the connections are more or less persistent.
> I have a connection manager which doles connections out to the worker
> threads and reclaims them when the workers are done with them. It
> dynamically adds new connections based on load. Each worker obtains a
> connection from the connection manager, performs a transaction which
> involves executing the function and pulling back the results from the
> cursors, then releases the connection back to the connection manager for
> other workers to use. So, this means that even when written in C, the SQL
> queries will be planned and cached on each connection after the first
> execution. So, I guess the question just becomes whether using SPI in C has
> any extra overhead verses using PL/PGSQL which might make it slower for
> performing queries. Since PostgreSQL is written in C, I assume there is no
> such additional overhead. I assume that the PL/PGSQL implementation at its
> heart also uses SPI to perform those executions. Is that a fair statement?

At best, if you are a ninja with the marginally documented backend
api, you will create code that goes about as fast as your pl/pgsql
function for 10 times the amount of input work, unless there are heavy
amounts of 'other than sql' code in your function. The reason to
write C in the backend is:

*) Interface w/3rd party libraries w/C linkage
*) Do things that are illegal in regular SQL (write files, etc)
*) Make custom types

Things like that. If your pl/pgsql function is running slow, it's
probably better to look at what's going on there.

merlin