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 Aufflick e: [EMAIL PROTECTED] w: www.pumptheory.com (business) w: mark.aufflick.com (personal) p: +61 438 700 647
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])