From: Mark Aufflick <mark@pumptheory.com>
To: pgsql-performance@postgresql.org
Subject: PREPAREing statements versus compiling PLPGSQL
Date: Tue, 08 Jun 2004 01:52:14 +1000
Message-ID: <40C48F2E.5090704@pumptheory.com> (raw)
Hi all,
I am optimizing some code that does a lot of iterative selects and
inserts within loops. Because of the exception handling limitations in
postgres and with no ability to twiddle autocommit, just about every
operation is standalone.
over 5000 odd lines this gets very slow (5-10 minutes including processing).
In seeking to speed it up I am PREPARing the most common inserts and
selects. I have a few operations already inside plpgsql functions.
EXECUTE means something different within a plpgsql funtion, so I am
wondering if there is a way to execute a pre-prepared query inside a
function.
Or is this even necessary - are queries within plpgsql functions
automatically prepared when the function is first compiled? On a similar
note, is there any benefit in PREPAREing a select from a plpgsql function?
Or does anyone have any smart ways to turn off autocommit? (I have
already played with commit_delay and commit_siblings).
My empirical testing has proven inconclusive (other than turning off
fsync which makes a huge difference, but not possible on the live
system, or using a fat copmaq raid card).
Thanks for any help,
Mark.
--
Mark Aufflick
e: mark@pumptheory.com
w: www.pumptheory.com (business)
w: mark.aufflick.com (personal)
p: +61 438 700 647
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-performance@postgresql.org
Cc: mark@pumptheory.com
Subject: Re: PREPAREing statements versus compiling PLPGSQL
In-Reply-To: <40C48F2E.5090704@pumptheory.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