Skip site navigation (1) Skip section navigation (2)

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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Eliot Gable <egable+pgsql-performance(at)gmail(dot)com>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL Function Language Performance: C vs PL/PGSQL
Date: 2010-05-28 12:22:22
Message-ID: AANLkTimClqP0JhBtIOHOcZLBn0O7sK-dEPk7Az8QmLMx@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Rob WultschDate: 2010-05-28 17:07:42
Subject: Re: performance of temporary vs. regular tables
Previous:From: Joachim WorringenDate: 2010-05-28 11:04:13
Subject: Re: performance of temporary vs. regular tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group