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
