Performance: sql functions v. plpgsql v. plperl

Last night, I was doing some amateurish benchmarking and found that,
contrary to my (admittedly uninformed) expectation, sql functions seem
*slower* than plsql functions. Even for very simple things, like

'SELECT CASE WHEN $1 RETURN $1 ELSE $2 END'

were slower than the plsql

begin
  if $1 then
    return $1;
  else
    return $2;
  end if;
end;

by about 15%.

However, my benchmarking was the type usually dreaded: a single person,
playing around in a scripting language, running the test one a time
seqeuentially, and just timing the results.

Is there any real data on this?



I also tried plperl v plpgsql, and found that, probably not surprisingly,
there was about a 15% advantage to plpgsql. Of course, many things can be
written much simpler in perl (such as string handling functions). Even so,
though, a find-the-first-letter-of-all-significant-words function written
about 1.5 years ago in plpgsql (a pretty awful, nested, letter-by-letter
parser) was only about twice as slow as the perl
split() replacement. Looks like our little plpgsql is quite a speed demon.

(as always, IANAPB [ I am not a professional benchmarker ], YMMV)


--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington