Re: Which is faster SQL or PL/PGSQL

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