Re: Stored Procedure performance / elegance question

On 8 Sep 2006 14:39:54 -0700, Karen Hill <karen_hill22(at)yahoo(dot)com> wrote:
> "Merlin Moncure" wrote:
> > you could of course do:
> > FOR rec IN SELECT * FROM test WHERE $1 is null or mydate > $1 loop [...]
> > or some such.
>
> This was a simple example. In reality, the structure of the query is
> altered, but there are about 4 different query possibilities in the
> real problem depending on which values are null or not. My question
> was is it worth it to use Execute and suffer possible performance
> issues of having the planner make a new plan every time the Execute
> command was run?

you can extend the above to an aribtrary number of parameters:
select * from t where (in_a is null or in_a > a) and (in_b is null or
in_b > b) [...]

the server is very smart at optimizing the above.

however there are some cases where this is inconvenient, for example a
parameter that adds a group by clause. in this case 'execute' is
sometimes better.

The plan generation is only relevant if the function is quick and
generated frequently, or has extremely complex plans (not likely).
There is an interesting recent thread in -hackers discussing plan
generation you might want to check out.

Generally, I don't like dynamic sql because it is more complex to
write and can get very tedius, especially with large queries with lots
of strings. The major time saver with plpgsql, IMO, is that your
queries are first class and dont have to be run through a separate
parser.

> Don't refcursors consume a lot of database server resources? I wish to
> avoid that so in practice I use LIMIT and OFFSET to control results.

refcursors are almost always better so long as you remember they can
only be held open for the duration of a transaction. OFFSET on the
other hand is notoriously inefficient as the offsets grow large. It
is fine for small browses however. However, you can just do relative
querying to browse large sets which is probably the fastest way of
all.

merlin