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

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?

On Wed, May 26, 2010 at 12:32 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:
> > 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?
>
> Unless you're using EXECUTE in your pl/pgsql, the queries in your
> pl/pgsql function are already getting prepared on the first call of the
> function for a given backend connection.. If you're using EXECUTE in
> pl/gpsql then your problem might be planning time. Moving that to C
> isn't going to change things as much as you might hope if you still have
> to plan the query every time you call it..
>
> > The function gets called a lot, but not in the same transaction. It is
> only
> > called once per transaction.
>
> That's not really relevant.. Is it called alot from the same
> backend/database connection? If so, and if you're using regular SELECT
> statements and the like (not EXECUTE), then they're getting prepared the
> first time they're used and that is kept across transactions.
>
> Thanks,
>
> Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkv9TTMACgkQrzgMPqB3kijiNQCfY/wTud+VZ4Z53Lw8cNY/N9ZD
> 0R4AnA4diz1aptFGYXh3j8N9/k96C7/S
> =6oz+
> -----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