From: Richard Huxton <dev@archonet.com>
To: Michael Pohl <pgsql@newtopia.com>
To: pgsql-sql@postgresql.org
Cc: George A.J <jinujosein@yahoo.com>
Subject: Re: Which is faster SQL or PL/PGSQL
Date: Mon, 20 Oct 2003 18:09:51 +0100
Message-ID: <200310201809.51830.dev@archonet.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0310201119240.10378-100000@server1.techplant.com>
References: <Pine.LNX.4.44.0310201119240.10378-100000@server1.techplant.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, pgsql@newtopia.com, jinujosein@yahoo.com
Subject: Re: Which is faster SQL or PL/PGSQL
In-Reply-To: <200310201809.51830.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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