pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




Message-ID: <40C48F2E.5090704@pumptheory.com>
Permalink:  ../40C48F2E.5090704@pumptheory.com/
Also on:    postgresql.org/message-id/40C48F2E.5090704@pumptheory.com

 · 

reply

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