Thread: Which is faster SQL or PL/PGSQL
<p>hi,<p>i am converting an MSSQL database to Postgres. there is a lot of procedures to convert.<p>which language is bestfor functions, SQL or plpgsql. <p>which is faster . i am using postgres 7.3.2<p>jinujose<p><hr size="1" /> Do you Yahoo!?<br/><a href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">The New Yahoo!Shopping</a> - with improved product search
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
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
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
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
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
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
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 haveto 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 parameteris used more than once * in the SQL-function body, we require it not to contain any volatile * functions (volatilesmight deliver inconsistent answers) nor to be * unreasonably expensive to evaluate. The expensiveness check notonly * prevents us from doing multiple evaluations of an expensive parameter * at runtime, but is a safety value to limitgrowth of an expression * due to repeated inlining. * * We must also beware of changing the volatility or strictnessstatus * of functions by inlining them. * * Returns a simplified expression if successful, or NULL if cannot *simplify the function. */ Joe
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 BerkusAglio Database SolutionsSan Francisco
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
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 BerkusAglio Database SolutionsSan Francisco
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
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 BerkusAglio Database SolutionsSan Francisco