Re: Functions in Postgres

Tom Lane wrote:
> 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

In PL/Tcl, it's the programmer who has to explicitly ask for
query plan caching. It's definitely possible and has been all
the time.

> 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

PL/pgSQL does turn every expression into a SELECT, but then
it analyzes the targetlist a little and if it's just a few
nested function calls with parameters or such, it creates a
suitable executor context and calls ExecEvalExpr() directly,
without going through SPI. Since Tcl's interface is purely
string based, everything goes first from internal to string,
then from string to Tcl's internal and all the way back
again. Not easy to estimate who will win here.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #