Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com [216.136.174.115]) by postgresql.org (Postfix) with SMTP id 9AD43475CA5 for ; Fri, 12 Apr 2002 14:20:32 -0400 (EDT) Received: from psc.progress.com (HELO saturn.janwieck.net) (janwieck@192.233.92.200 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 12 Apr 2002 18:20:32 -0000 Received: (from wieck@localhost) by saturn.janwieck.net (8.11.2/8.11.2) id g3CIGB907316; Fri, 12 Apr 2002 14:16:11 -0400 From: Jan Wieck Message-Id: <200204121816.g3CIGB907316@saturn.janwieck.net> Subject: Re: Functions in Postgres In-Reply-To: <28057.1018631469@sss.pgh.pa.us> from Tom Lane at "Apr 12, 2002 01:11:09 pm" To: Tom Lane Date: Fri, 12 Apr 2002 14:16:11 -0400 (EDT) Cc: Peter Eisentraut , Gordon Clarke , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/158 X-Sequence-Number: 7422 Tom Lane wrote: > Peter Eisentraut 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@Yahoo.com #