pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhich is faster SQL or PL/PGSQL
13+ messages / 7 participants
[nested] [flat]
* Which is faster SQL or PL/PGSQL
@ 2003-10-20 02:57 George A.J <jinujosein@yahoo.com>
0 siblings, 2 replies; 13+ messages in thread
From: George A.J @ 2003-10-20 02:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 03:48 Christopher Browne <cbbrowne@acm.org>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 1 reply; 13+ messages in thread
From: Christopher Browne @ 2003-10-20 03:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 05:29 Josh Berkus <josh@agliodbs.com>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2003-10-20 05:29 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 15:36 Michael Pohl <pgsql@newtopia.com>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Pohl @ 2003-10-20 15:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 17:09 Richard Huxton <dev@archonet.com>
parent: Michael Pohl <pgsql@newtopia.com>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2003-10-20 17:09 UTC (permalink / raw)
To: Michael Pohl <pgsql@newtopia.com>; pgsql-sql; +Cc: George A.J <jinujosein@yahoo.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 17:24 Joe Conway <mail@joeconway.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Joe Conway @ 2003-10-20 17:24 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 17:49 Richard Huxton <dev@archonet.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2003-10-20 17:49 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 18:11 Joe Conway <mail@joeconway.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Joe Conway @ 2003-10-20 18:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 18:24 Josh Berkus <josh@agliodbs.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 13+ messages in thread
From: Josh Berkus @ 2003-10-20 18:24 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; Richard Huxton <dev@archonet.com>; +Cc: Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
Joe,
> 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.
While we're on the topic, anyone know any good ways to speed up EXECUTE
statements in PL/pgSQL functions?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 18:54 Joe Conway <mail@joeconway.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 13+ messages in thread
From: Joe Conway @ 2003-10-20 18:54 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Richard Huxton <dev@archonet.com>; Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 19:00 Josh Berkus <josh@agliodbs.com>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2003-10-20 19:00 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
Joe,
> 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
Not sure how useful it would be in my case; I'm using EXECUTE because I'm
building a dynamic query based on user input, so the query plans would need
to vary radically from run to run.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 19:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-10-20 19:50 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: josh@agliodbs.com, Richard Huxton <dev@archonet.com>; Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
Joe Conway <mail@joeconway.com> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Which is faster SQL or PL/PGSQL
@ 2003-10-20 20:58 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Josh Berkus @ 2003-10-20 20:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Joe Conway <mail@joeconway.com>; +Cc: Richard Huxton <dev@archonet.com>; Michael Pohl <pgsql@newtopia.com>; pgsql-sql@postgresql.org, "George A.J" <jinujosein@yahoo.com>
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.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-10-20 20:58 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-20 02:57 Which is faster SQL or PL/PGSQL George A.J <jinujosein@yahoo.com>
2003-10-20 03:48 ` Christopher Browne <cbbrowne@acm.org>
2003-10-20 15:36 ` Michael Pohl <pgsql@newtopia.com>
2003-10-20 17:09 ` Richard Huxton <dev@archonet.com>
2003-10-20 17:24 ` Joe Conway <mail@joeconway.com>
2003-10-20 17:49 ` Richard Huxton <dev@archonet.com>
2003-10-20 18:11 ` Joe Conway <mail@joeconway.com>
2003-10-20 18:24 ` Josh Berkus <josh@agliodbs.com>
2003-10-20 18:54 ` Joe Conway <mail@joeconway.com>
2003-10-20 19:00 ` Josh Berkus <josh@agliodbs.com>
2003-10-20 19:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-20 20:58 ` Josh Berkus <josh@agliodbs.com>
2003-10-20 05:29 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox