Re: Functions in Postgres

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Gordon Clarke writes:
>> I was asking to see if there was any speed penalty with coding a fn in SQL
>> as opposed to PL/pgsql. It appears that there is and from what you've said
>> PL/pgsql is slightly faster than PL/TCL and probably PL/Perl. Am I correct
>> in drawing that conclusion?

> Yes, that seems about right.

plpgsql should be noticeably faster for issuing repetitive SQL queries,
since it caches query plans and the other two do not. However, I expect
the reverse is true for simple calculations (arithmetic, text-mashing,
etc). plpgsql turns *every* expression into an SQL SELECT query; even
with caching of query plans, I really doubt that our SQL engine can do
expressions faster than Tcl or Perl can. (Note that both of those do
some amount of pre-parsing of function text themselves, so that the
caching of query plans in itself gives no advantage to plpgsql for
expressions.) And of course there are many areas where either Tcl or
Perl will win hands down for functionality, for example pattern match
or text substitution tasks.

AFAIK no one has done any actual comparisons to prove or disprove these
theories, though. It'd be interesting to code up equivalent functions
in all three PL languages and do some benchmarking. Any volunteers out
there?

regards, tom lane