Re: Cool PL/PgSQL hack :)

> > > Kind people,
>
> > > Here's a little hack I put together that looks a lot like Perl's
> > > join() operator. Comments, suggestions for improvement, and
> > > sources for old-school pizzelle irons are all welcome :)
>
> > For the hyper performance sensitive, use := instead of SELECT INTO.
>
> Thanks!
>
> BTW, I'm doing a few similar things like a string_split that returns
> an array, and eventually--don't know quite how hard this is--similar
> operations with one-column SELECTs instead of arrays. Am I
> reinventing the wheel here?
>
> > That said, I'm slowly moving things over to compiled .so's which is
> > significantly faster, but this had me wondering: has anyone done any
> > comparative benchmarks of the various pl languages for PostgreSQL?
>
> Hmm...got source?

:) Non-public for the stuff that I'm working on.

But, for the sake of testing, here are a few tests:

/* pl/pgsql code BEGIN */
CREATE OR REPLACE FUNCTION t1() RETURNS INT AS '
DECLARE
v_i INT;
BEGIN
v_i := 1;
RETURN v_i;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION t2() RETURNS INT AS '
DECLARE
v_i INT;
BEGIN
SELECT 1 INTO v_i;
RETURN v_i;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION t3() RETURNS INT AS '/tmp/t3.so' LANGUAGE C;
/* End */

Where t3() is defined as:

/* Begin */
#include "executor/spi.h"
int t3(void);
int t3(void) {
int v_i;
v_i = 1;
return(v_i);
}
/* End */

I know i could just return 1. These tests are intentionally very
basic, but it's the assignment that counts (yes, I know that the C
version gets optimized to just return(1), but that's the advantage of
having a real compiler go to work for you). First call(1) is with
plpgsql.so preloaded. First call(2) doesn't have plpgsql.so preloaded
(new feature in pre-7.4). Anyway, here's the data:

Function | First call(1) | First call(2) | Subsequent calls
-----------+-----------------+-----------------+-------------------
t1() | 2.35msec | 3.34msec | 0.24msec
t2() | 2.46msec | 3.45msec | 0.28msec
t3() | 0.37msec | 0.75msec | 0.16msec

Not that interesting, but it basically goes to show what the overhead
is for calling a function in the various circumstances on my unloaded
pIII 600Mhz laptop. All times are the average of 50 calls to the
function and timing is provided by EXPLAIN ANALYZE. For the first
call tests, a new connection is fired up for every iteration.
Preloading didn't make any difference between whether or not the
libraries were preloaded or not. Here are a set of much more
interesting tests, however, with t(4..6):

CREATE OR REPLACE FUNCTION t4() RETURNS INT AS '
DECLARE
v_i INT;
ret INT;
BEGIN
FOR v_i IN 1..1000 LOOP
ret := v_i;
END LOOP;
RETURN ret;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION t5() RETURNS INT AS '
DECLARE
v_i INT;
ret INT;
BEGIN
FOR v_i IN 1..1000 LOOP
SELECT v_i INTO ret;
END LOOP;
RETURN ret;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION t6() RETURNS INT AS '/tmp/t6.so' LANGUAGE C;

/* Begin C/t6() */
#include "executor/spi.h"
int t6(void);
int t6(void) {
int v_i;
int ret;

for (v_i = 0; v_i < 1000; v_i++)
ret = v_i;
return(ret);
}
/* End C */

Function | First call(1) | First call(2) | Subsequent calls
-----------+-----------------+-----------------+-------------------
t4() | 6.16msec | 9.17msec | 3.54msec
t5() | 34.52msec | 41.18msec | 29.19msec
t6() | 0.37msec | 0.77msec | 0.16msec

And there the difference starts to show. t5() uses SELECT INTO
instead of :=. Every time SELECT INTO is used, it has to talk to the
backend and has to setup a plan for the query, though plans are cached
by pl/pgsql. Nothing quite touches C... it's unphased by the extra
1,000 iterations. Some food for thought. -sc

PS I know the C examples didn't connect to the SPI manager, but it
wasn't necessary for the tests given. Fetching rows is IO bound and
my laptop's HDD isn't what I'd call server quality so I stuck to
purely mathematical functions for now. pl/pgsql has a nasty habit of
copying data with ROWTYPE's/RECORD's, whereas in C you can use a fixed
buffer and just realloc() the space. *sigh* I wish pl/pgsql was
smarter in that regard and would only really copy if the function had
RETURN NEXT or returned a SETOF data.

--
Sean Chittenden