Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AF71A63715B for ; Tue, 2 Feb 2010 16:22:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 81826-02-2 for ; Tue, 2 Feb 2010 20:21:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id BE07A63F8BB for ; Tue, 2 Feb 2010 15:28:00 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o12JRx0O029025; Tue, 2 Feb 2010 14:27:59 -0500 (EST) To: Mridula Mahadevan cc: "pgsql-performance@postgresql.org" Subject: Re: Queries within a function In-reply-to: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com> References: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com> Comments: In-reply-to Mridula Mahadevan message dated "Tue, 02 Feb 2010 10:17:15 -0800" Date: Tue, 02 Feb 2010 14:27:59 -0500 Message-ID: <29024.1265138879@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.465 tagged_above=-10 required=5 tests=AWL=0.134, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/26 X-Sequence-Number: 37447 Mridula Mahadevan writes: > I am running a bunch of queries within a function, creating some temp tables and populating them. When the data exceeds say, 100k the queries start getting really slow and timeout (30 min). when these are run outside of a transaction(in auto commit mode), they run in a few seconds. Any ideas on what may be going on and any postgresql.conf parameters etc that might help? I'll bet the function is caching query plans that stop being appropriate once the table grows in size. You might have to resort to using EXECUTE, although if you're on 8.4 DISCARD PLANS ought to help too. regards, tom lane