From jinujosein@yahoo.com Mon Oct 20 02:58:08 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9CF98D1B4E6 for ; Mon, 20 Oct 2003 02:58:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20102-03 for ; Sun, 19 Oct 2003 23:57:41 -0300 (ADT) Received: from web14916.mail.yahoo.com (web14916.mail.yahoo.com [216.136.225.229]) by svr1.postgresql.org (Postfix) with SMTP id 21AAFD1B4E4 for ; Sun, 19 Oct 2003 23:57:37 -0300 (ADT) Message-ID: <20031020025741.43074.qmail@web14916.mail.yahoo.com> Received: from [202.9.168.185] by web14916.mail.yahoo.com via HTTP; Sun, 19 Oct 2003 19:57:41 PDT Date: Sun, 19 Oct 2003 19:57:41 -0700 (PDT) From: "George A.J" Subject: Which is faster SQL or PL/PGSQL To: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1629206070-1066618661=:42271" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_10_20, IN_REP_TO X-Spam-Level: X-Archive-Number: 200310/221 X-Sequence-Number: 15621 --0-1629206070-1066618661=:42271 Content-Type: text/plain; charset=us-ascii hi, i am converting an MSSQL database to Postgres. there is a lot of procedures to convert. which language is best for functions, SQL or plpgsql. which is faster . i am using postgres 7.3.2 jinujose --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search --0-1629206070-1066618661=:42271 Content-Type: text/html; charset=us-ascii

hi,

i am converting an MSSQL database to Postgres. there is a lot of procedures to convert.

which language is best for functions, SQL or plpgsql.

which is faster . i am using postgres 7.3.2

jinujose

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search --0-1629206070-1066618661=:42271-- From cbbrowne@acm.org Mon Oct 20 04:13:58 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EC542D1B4E1 for ; Mon, 20 Oct 2003 04:13:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30276-08 for ; Mon, 20 Oct 2003 01:13:27 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 5BA06D1B4F2 for ; Mon, 20 Oct 2003 01:13:27 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id h9K4DRNu084054 for ; Mon, 20 Oct 2003 04:13:27 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h9K3qPR1080853 for pgsql-sql@postgresql.org; Mon, 20 Oct 2003 03:52:25 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Which is faster SQL or PL/PGSQL Date: Sun, 19 Oct 2003 23:48:38 -0400 Organization: cbbrowne Computing Inc Lines: 30 Message-ID: References: <20031020025741.43074.qmail@web14916.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:lzM4DB7OfQ27HcmDpzsRPF0uv+M= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/222 X-Sequence-Number: 15622 The world rejoiced as jinujosein@yahoo.com ("George A.J") wrote: > i am converting an MSSQL database to Postgres. there is a lot of procedures to convert. > > which language is best for functions, SQL or plpgsql. > > which is faster . i am using postgres 7.3.2 Hmm? This doesn't seem to make much more sense than the question of what colour a database should be ("Mauve has more RAM..."). SQL and pl/pgsql are quite distinct. If you have procedures that require programmed logic, with things like variables and loops, SQL generally cannot do that, and you will HAVE to use one of the embedded languages. If raw speed is at issue, it is quite likely that rewriting the procedures in C would lead to code that is faster still. But the real question is of what language you *need* to implement in. For certain sorts of simple procedures, SQL may suffice; as complexity grows, you will need to use one of the other languages, whether plpgsql, plperl, plpython, C, C++, and such, and the question won't be of speed; it will be of necessity. -- output = ("cbbrowne" "@" "cbbrowne.com") http://www.ntlug.org/~cbbrowne/linuxdistributions.html Never hit someone head on, always sideswipe. Never say, "Foo's last patch was brain-damaged", but rather, "While fixing the miscellaneous bugs in 243.xyz [foo's patch], I found...." -- from the Symbolics Guidelines for Sending Mail From josh@agliodbs.com Mon Oct 20 05:30:38 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 609A4D1B516 for ; Mon, 20 Oct 2003 05:30:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39732-09 for ; Mon, 20 Oct 2003 02:30:08 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 876D6D1B4E1 for ; Mon, 20 Oct 2003 02:30:07 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3793057; Sun, 19 Oct 2003 22:30:46 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "George A.J" , pgsql-sql@postgresql.org Subject: Re: Which is faster SQL or PL/PGSQL Date: Sun, 19 Oct 2003 22:29:40 -0700 User-Agent: KMail/1.4.3 References: <20031020025741.43074.qmail@web14916.mail.yahoo.com> In-Reply-To: <20031020025741.43074.qmail@web14916.mail.yahoo.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200310192229.40259.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/223 X-Sequence-Number: 15623 George, > i am converting an MSSQL database to Postgres. there is a lot of procedures > to convert. > > which language is best for functions, SQL or plpgsql. If you're porting from T-SQL, you should use PL/pgSQL. However, you will have to re-code many of your procedures by hand, as T-SQL and PL/pgSQL have substantially different syntax for control structures and cursors. See the porting articles on techdocs.postgresql.org > which is faster . i am using postgres 7.3.2 You should upgrade to the 7.3.4 if possible as it has some bug fixes missing in 7.3.2. -- Josh Berkus Aglio Database Solutions San Francisco From pgsql@newtopia.com Mon Oct 20 15:33:55 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3F17BD1B4F2 for ; Mon, 20 Oct 2003 15:33:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47910-03 for ; Mon, 20 Oct 2003 12:33:23 -0300 (ADT) Received: from server1.techplant.com (unknown [209.61.173.33]) by svr1.postgresql.org (Postfix) with ESMTP id E1203D1B4EC for ; Mon, 20 Oct 2003 12:33:22 -0300 (ADT) Received: from localhost (michael@localhost) by server1.techplant.com (8.11.6/8.11.6) with ESMTP id h9KFaON07445 for ; Mon, 20 Oct 2003 11:36:24 -0400 Date: Mon, 20 Oct 2003 11:36:24 -0400 (EDT) From: Michael Pohl X-X-Sender: michael@server1.techplant.com To: pgsql-sql@postgresql.org Subject: Re: Which is faster SQL or PL/PGSQL In-Reply-To: Message-ID: X-Mailkey: j9dfjk23j4dkq MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/238 X-Sequence-Number: 15638 On Sun, 19 Oct 2003, Christopher Browne wrote: > The world rejoiced as jinujosein@yahoo.com ("George A.J") wrote: > > i am converting an MSSQL database to Postgres. there is a lot of > > procedures to convert. > > > > which language is best for functions, SQL or plpgsql. > > > > which is faster . i am using postgres 7.3.2 > > Hmm? This doesn't seem to make much more sense than the question of > what colour a database should be ("Mauve has more RAM..."). Transact-SQL stored procedures pseudo-compile their execution plans the first time they are run, which can result in faster subsequent executions. I'm guessing the poster was wondering if plpgsql functions offered similar performance benefits vs. equivalent SQL. michael From dev@archonet.com Mon Oct 20 17:10:43 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7683FD1B515 for ; Mon, 20 Oct 2003 17:10:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 58937-10 for ; Mon, 20 Oct 2003 14:10:13 -0300 (ADT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id A736CD1B562 for ; Mon, 20 Oct 2003 14:09:56 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 1ABdXX-0006mz-0Z; Mon, 20 Oct 2003 18:09:55 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 5DF7D182E6; Mon, 20 Oct 2003 18:09:53 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id AD27317244; Mon, 20 Oct 2003 18:09:52 +0100 (BST) From: Richard Huxton To: Michael Pohl , pgsql-sql@postgresql.org Subject: Re: Which is faster SQL or PL/PGSQL Date: Mon, 20 Oct 2003 18:09:51 +0100 User-Agent: KMail/1.5 References: In-Reply-To: Cc: "George A.J" MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310201809.51830.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/239 X-Sequence-Number: 15639 On Monday 20 October 2003 16:36, Michael Pohl wrote: > On Sun, 19 Oct 2003, Christopher Browne wrote: > > The world rejoiced as jinujosein@yahoo.com ("George A.J") wrote: > > > i am converting an MSSQL database to Postgres. there is a lot of > > > procedures to convert. > > > > > > which language is best for functions, SQL or plpgsql. > > > > > > which is faster . i am using postgres 7.3.2 > > > > Hmm? This doesn't seem to make much more sense than the question of > > what colour a database should be ("Mauve has more RAM..."). > > Transact-SQL stored procedures pseudo-compile their execution plans the > first time they are run, which can result in faster subsequent executions. > I'm guessing the poster was wondering if plpgsql functions offered similar > performance benefits vs. equivalent SQL. To which the answer would have to be "yes and no". A plpgsql function is compiled on its first call, so any queries will have their plans built then, and you will gain on subsequent calls. However, since the plan will have to be built without knowledge of the actual values involved, you might not get as good a plan as you could with the actual values. For example: SELECT * FROM uk_towns WHERE town_name LIKE 'T%'; SELECT * FROM uk_towns WHERE town_name LIKE 'X%'; There's a good chance a seq-scan of the table is the best plan for the first query, but if you have an index on town_name you probably want to use it in the second case. So - gain by not re-planning on every call, but maybe lose because your plan is not so precise. Of course, any queries you build dynamically and run via EXECUTE will have to be planned each time. -- Richard Huxton Archonet Ltd From mail@joeconway.com Mon Oct 20 17:27:50 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 15978D1B523 for ; Mon, 20 Oct 2003 17:27:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 70691-03 for ; Mon, 20 Oct 2003 14:27:19 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id B7634D1B508 for ; Mon, 20 Oct 2003 14:27:17 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.5) with ESMTP-TLS id 437147; Mon, 20 Oct 2003 10:26:09 -0700 Message-ID: <3F941A43.1040802@joeconway.com> Date: Mon, 20 Oct 2003 10:24:19 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: Michael Pohl , pgsql-sql@postgresql.org, "George A.J" Subject: Re: Which is faster SQL or PL/PGSQL References: <200310201809.51830.dev@archonet.com> In-Reply-To: <200310201809.51830.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/243 X-Sequence-Number: 15643 Richard Huxton wrote: > So - gain by not re-planning on every call, but maybe lose because your plan > is not so precise. > > Of course, any queries you build dynamically and run via EXECUTE will have to > be planned each time. > This question gets even more complex in 7.4, where many simple SQL functions will get inlined, and library preloading is available to speed that first PL/pgSQL call. I think the best way to answer this question for any particular function is to try it both ways (if the function *can* be written as a SQL function) and see for yourself. And if you do this in 7.3, redo it when you upgrade to 7.4. HTH, Joe From dev@archonet.com Mon Oct 20 17:49:53 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7CEA8D1B52D for ; Mon, 20 Oct 2003 17:49:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 72445-05 for ; Mon, 20 Oct 2003 14:49:22 -0300 (ADT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id B614CD1B515 for ; Mon, 20 Oct 2003 14:49:21 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1ABe9h-000AET-0Y; Mon, 20 Oct 2003 18:49:21 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 54363182E9; Mon, 20 Oct 2003 18:49:20 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id B4E93182E5; Mon, 20 Oct 2003 18:49:19 +0100 (BST) From: Richard Huxton To: Joe Conway Subject: Re: Which is faster SQL or PL/PGSQL Date: Mon, 20 Oct 2003 18:49:19 +0100 User-Agent: KMail/1.5 Cc: Michael Pohl , pgsql-sql@postgresql.org, "George A.J" References: <200310201809.51830.dev@archonet.com> <3F941A43.1040802@joeconway.com> In-Reply-To: <3F941A43.1040802@joeconway.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310201849.19668.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/246 X-Sequence-Number: 15646 On Monday 20 October 2003 18:24, Joe Conway wrote: > Richard Huxton wrote: > > So - gain by not re-planning on every call, but maybe lose because your > > plan is not so precise. > > > > Of course, any queries you build dynamically and run via EXECUTE will > > have to be planned each time. > > This question gets even more complex in 7.4, where many simple SQL > functions will get inlined, and library preloading is available to speed > that first PL/pgSQL call. What will be the effects of inlining? Does it mean the planner merges the function's plan into the larger query? -- Richard Huxton Archonet Ltd From mail@joeconway.com Mon Oct 20 18:15:10 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9C097D1B51B for ; Mon, 20 Oct 2003 18:15:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 74115-06 for ; Mon, 20 Oct 2003 15:14:40 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id ED5F8D1B4F6 for ; Mon, 20 Oct 2003 15:14:37 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.5) with ESMTP-TLS id 437275; Mon, 20 Oct 2003 11:13:29 -0700 Message-ID: <3F942558.2000804@joeconway.com> Date: Mon, 20 Oct 2003 11:11:36 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard Huxton Cc: Michael Pohl , pgsql-sql@postgresql.org, "George A.J" Subject: Re: Which is faster SQL or PL/PGSQL References: <200310201809.51830.dev@archonet.com> <3F941A43.1040802@joeconway.com> <200310201849.19668.dev@archonet.com> In-Reply-To: <200310201849.19668.dev@archonet.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/249 X-Sequence-Number: 15649 Richard Huxton wrote: > On Monday 20 October 2003 18:24, Joe Conway wrote: >>This question gets even more complex in 7.4, where many simple SQL >>functions will get inlined, and library preloading is available to speed >>that first PL/pgSQL call. > > What will be the effects of inlining? Does it mean the planner merges the > function's plan into the larger query? > Yes, I believe so (well, actually the optimizer). An inlined SQL function ends up behaving like a macro that expands at run time and is therefore quite fast -- no function call overhead at all. Here is the comment from the source (src/backend/optimizer/util/clauses.c): /* * inline_function: try to expand a function call inline * * If the function is a sufficiently simple SQL-language function * (just "SELECT expression"), then we can inline it and avoid the * rather high per-call overhead of SQL functions. Furthermore, this * can expose opportunities for constant-folding within the function * expression. * * We have to beware of some special cases however. A directly or * indirectly recursive function would cause us to recurse forever, * so we keep track of which functions we are already expanding and * do not re-expand them. Also, if a parameter is used more than once * in the SQL-function body, we require it not to contain any volatile * functions (volatiles might deliver inconsistent answers) nor to be * unreasonably expensive to evaluate. The expensiveness check not only * prevents us from doing multiple evaluations of an expensive parameter * at runtime, but is a safety value to limit growth of an expression * due to repeated inlining. * * We must also beware of changing the volatility or strictness status * of functions by inlining them. * * Returns a simplified expression if successful, or NULL if cannot * simplify the function. */ Joe From josh@agliodbs.com Mon Oct 20 18:26:55 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B3C65D1B561 for ; Mon, 20 Oct 2003 18:26:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 82216-02 for ; Mon, 20 Oct 2003 15:26:25 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id A9D12D1B538 for ; Mon, 20 Oct 2003 15:26:14 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3795795; Mon, 20 Oct 2003 11:26:55 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Joe Conway , Richard Huxton Subject: Re: Which is faster SQL or PL/PGSQL Date: Mon, 20 Oct 2003 11:24:02 -0700 User-Agent: KMail/1.4.3 Cc: Michael Pohl , pgsql-sql@postgresql.org, "George A.J" References: <200310201849.19668.dev@archonet.com> <3F942558.2000804@joeconway.com> In-Reply-To: <3F942558.2000804@joeconway.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310201124.02090.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/250 X-Sequence-Number: 15650 Joe, > Yes, I believe so (well, actually the optimizer). An inlined SQL=20 > function ends up behaving like a macro that expands at run time and is=20 > therefore quite fast -- no function call overhead at all. ... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented. While we're on the topic, anyone know any good ways to speed up EXECUTE=20 statements in PL/pgSQL functions? --=20 -Josh Berkus Aglio Database Solutions San Francisco From mail@joeconway.com Mon Oct 20 18:58:14 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 92450D1B508 for ; Mon, 20 Oct 2003 18:58:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 82216-07 for ; Mon, 20 Oct 2003 15:57:44 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 65BD9D1B4E3 for ; Mon, 20 Oct 2003 15:57:42 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.5) with ESMTP-TLS id 437376; Mon, 20 Oct 2003 11:56:31 -0700 Message-ID: <3F942F72.6050709@joeconway.com> Date: Mon, 20 Oct 2003 11:54:42 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: josh@agliodbs.com Cc: Richard Huxton , Michael Pohl , pgsql-sql@postgresql.org, "George A.J" Subject: Re: Which is faster SQL or PL/PGSQL References: <200310201849.19668.dev@archonet.com> <3F942558.2000804@joeconway.com> <200310201124.02090.josh@agliodbs.com> In-Reply-To: <200310201124.02090.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/252 X-Sequence-Number: 15652 Josh Berkus wrote: >>Yes, I believe so (well, actually the optimizer). An inlined SQL >>function ends up behaving like a macro that expands at run time and is >>therefore quite fast -- no function call overhead at all. > > ... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented. Yeah, that's what my original post said ;-) > While we're on the topic, anyone know any good ways to speed up EXECUTE > statements in PL/pgSQL functions? Never tried it, but is it possible to use a prepared statement inside a PL/pgSQL function? In any case, you can in other PLs. And with library preloading (starting in 7.4), the first call to other PLs is similar to that of PL/pgSQL. See: http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php Joe From josh@agliodbs.com Mon Oct 20 19:03:16 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4898CD1B55E for ; Mon, 20 Oct 2003 19:03:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 85548-01 for ; Mon, 20 Oct 2003 16:02:46 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 8955BD1B4E3 for ; Mon, 20 Oct 2003 16:02:44 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3795992; Mon, 20 Oct 2003 12:03:25 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Joe Conway Subject: Re: Which is faster SQL or PL/PGSQL Date: Mon, 20 Oct 2003 12:00:31 -0700 User-Agent: KMail/1.4.3 Cc: pgsql-sql@postgresql.org References: <200310201124.02090.josh@agliodbs.com> <3F942F72.6050709@joeconway.com> In-Reply-To: <3F942F72.6050709@joeconway.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310201200.31857.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/253 X-Sequence-Number: 15653 Joe, > Never tried it, but is it possible to use a prepared statement inside a= =20 > PL/pgSQL function? In any case, you can in other PLs. And with library=20 > preloading (starting in 7.4), the first call to other PLs is similar to= =20 > that of PL/pgSQL. See: > http://archives.postgresql.org/pgsql-patches/2003-07/msg00239.php Not sure how useful it would be in my case; I'm using EXECUTE because I'm= =20 building a dynamic query based on user input, so the query plans would need= =20 to vary radically from run to run. --=20 -Josh Berkus Aglio Database Solutions San Francisco From tgl@sss.pgh.pa.us Mon Oct 20 19:50:43 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 52DD7D1B501 for ; Mon, 20 Oct 2003 19:50:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95800-01 for ; Mon, 20 Oct 2003 16:50:13 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 45B7CD1B508 for ; Mon, 20 Oct 2003 16:50:11 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9KJo0it013679; Mon, 20 Oct 2003 15:50:01 -0400 (EDT) To: Joe Conway Cc: josh@agliodbs.com, Richard Huxton , Michael Pohl , pgsql-sql@postgresql.org, "George A.J" Subject: Re: Which is faster SQL or PL/PGSQL In-reply-to: <3F942F72.6050709@joeconway.com> References: <200310201849.19668.dev@archonet.com> <3F942558.2000804@joeconway.com> <200310201124.02090.josh@agliodbs.com> <3F942F72.6050709@joeconway.com> Comments: In-reply-to Joe Conway message dated "Mon, 20 Oct 2003 11:54:42 -0700" Date: Mon, 20 Oct 2003 15:50:00 -0400 Message-ID: <13678.1066679400@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/255 X-Sequence-Number: 15655 Joe Conway writes: > Josh Berkus wrote: >> While we're on the topic, anyone know any good ways to speed up EXECUTE >> statements in PL/pgSQL functions? > Never tried it, but is it possible to use a prepared statement inside a > PL/pgSQL function? You could probably EXECUTE prepare and execute statements, but I don't see the point --- this would just be an extremely tedious way of duplicating plpgsql's normal query-plan-caching behavior. AFAICS, the whole point of EXECUTE in plpgsql is that it doesn't take any shortcuts, and so the answer to Josh's question can only be "don't use EXECUTE"... regards, tom lane From josh@agliodbs.com Mon Oct 20 21:01:36 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E0595D1B51B for ; Mon, 20 Oct 2003 21:01:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 01372-10 for ; Mon, 20 Oct 2003 18:01:07 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 9FB0BD1B508 for ; Mon, 20 Oct 2003 18:01:04 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3797639; Mon, 20 Oct 2003 14:01:31 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Tom Lane , Joe Conway Subject: Re: Which is faster SQL or PL/PGSQL Date: Mon, 20 Oct 2003 13:58:38 -0700 User-Agent: KMail/1.4.3 Cc: Richard Huxton , Michael Pohl , pgsql-sql@postgresql.org, "George A.J" References: <3F942F72.6050709@joeconway.com> <13678.1066679400@sss.pgh.pa.us> In-Reply-To: <13678.1066679400@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310201358.38318.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/258 X-Sequence-Number: 15658 Tom, > AFAICS, the whole point of EXECUTE in plpgsql is that it doesn't take > any shortcuts, and so the answer to Josh's question can only be "don't > use EXECUTE"... Yeah, that's what I thought, I was just hoping for some low-hanging fruit. --=20 -Josh Berkus Aglio Database Solutions San Francisco