Re: stored procedures and dynamic queries

Ivan Sergio Borgonovo wrote:
> On Mon, 03 Dec 2007 19:06:29 +0000
> Richard Huxton <dev(at)archonet(dot)com> wrote:
>
>> Ivan Sergio Borgonovo wrote:
>>> Any general rule about dynamically generated queries in stored
>>> procedures vs. performances?
>> It's the same decision as any with any prepared plan vs
>> plan-each-time trade-off.
>
> Should I guess this trade off on aruspices or is it possible to gain
> a little bit of culture or it's something based on heuristic and
> experience?

Unless it's an obvious decision (millions of small identical queries vs.
occasional large complex ones) then you'll have to test. That's going to
be true of any decision like this on any system.

> If the second, any good reading?
> How should I take into account parameters like:
> - is it a insert/update vs select query
> - is it performed on a large table?
> - is it performed frequently? frequently with same parameters?
> frequently with different parameters?
> - does the table is growing?
> - are there frequent delete?

Read the manuals, particularly the sections on MVCC, also the planner
and EXPLAIN. Read up on the WAL and checkpoints.

You might want to skim through the archives on the performance list. You
might find some of the community-related resources useful too. Some of
the following might be a little out-of-date, so check:

http://www.westnet.com/~gsmith/content/postgresql/
http://www.powerpostgresql.com/PerfList
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

HTH
--
Richard Huxton
Archonet Ltd