Re: stored procedures and dynamic queries - Mailing list pgsql-general

From Richard Huxton
Subject Re: stored procedures and dynamic queries
Date
Msg-id 47550C80.9030700@archonet.com
Whole thread Raw
In response to Re: stored procedures and dynamic queries  (Ivan Sergio Borgonovo <mail@webthatworks.it>)
Responses Re: stored procedures and dynamic queries
List pgsql-general
Ivan Sergio Borgonovo wrote:
> On Mon, 03 Dec 2007 19:06:29 +0000
> Richard Huxton <dev@archonet.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

pgsql-general by date:

Previous
From: Gregory Stark
Date:
Subject: Re: limits
Next
From: Reg Me Please
Date:
Subject: "Suspending" indexes and constraint updates