X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8CBD8D1B1DC for ; Mon, 7 Jun 2004 12:52:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75118-03 for ; Mon, 7 Jun 2004 15:52:47 +0000 (GMT) Received: from mail005.syd.optusnet.com.au (mail005.syd.optusnet.com.au [211.29.132.54]) by svr1.postgresql.org (Postfix) with ESMTP id ADA3ED1B16B for ; Mon, 7 Jun 2004 12:52:44 -0300 (ADT) Received: from wine.pumptheory.com (d198-142-112-197.dsl.nsw.optusnet.com.au [198.142.112.197]) by mail005.syd.optusnet.com.au (8.11.6p2/8.11.6) with SMTP id i57Fqfw03861 for ; Tue, 8 Jun 2004 01:52:42 +1000 Received: (qmail 5695 invoked from network); 7 Jun 2004 15:52:15 -0000 Received: from localhost (HELO pumptheory.com) (127.0.0.1) by wine.pumptheory.com with SMTP; 7 Jun 2004 15:52:15 -0000 Message-ID: <40C48F2E.5090704@pumptheory.com> Date: Tue, 08 Jun 2004 01:52:14 +1000 From: Mark Aufflick User-Agent: Mozilla Thunderbird 0.5 (X11/20040208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: PREPAREing statements versus compiling PLPGSQL Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/62 X-Sequence-Number: 7120 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