Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 87CCC633828 for ; Wed, 3 Feb 2010 00:24:21 -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 85688-03 for ; Wed, 3 Feb 2010 04:24:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from renaissance-it.com (mail.renaissance-it.com [164.164.87.55]) by mail.postgresql.org (Postfix) with ESMTP id 031546335E5 for ; Wed, 3 Feb 2010 00:24:09 -0400 (AST) Received: (qmail 22175 invoked by uid 109); 3 Feb 2010 04:26:29 -0000 Received: from 192.168.1.6 by Mailsrv (envelope-from , uid 112) with qmail-scanner-1.25st (spamassassin: 3.1.7-deb. perlscan: 1.25st. Clear:RC:1(192.168.1.6):. Processed in 0.04129 secs); 03 Feb 2010 04:26:29 -0000 Received: from unknown (HELO ramasubramanian) (ramasubramanian.g@[192.168.1.6]) (envelope-sender ) by renaissance-it.com (qmail-ldap-1.03) with SMTP for ; 3 Feb 2010 04:26:29 -0000 Message-ID: From: "ramasubramanian" To: "Mridula Mahadevan" Cc: References: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com> <29024.1265138879@sss.pgh.pa.us> <0A59BA5B590B7E4A8D441196A9F17E904C68825D30@corpmail11.calpurnia.com> Subject: Re: Queries within a function Date: Wed, 3 Feb 2010 09:54:47 +0530 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.572 tagged_above=-10 required=5 tests=AWL=0.537, BAYES_05=-1.11, STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 201002/46 X-Sequence-Number: 37467 Hi, Try using dynamic sql. Query will be faster in a function regards Ram ----- Original Message ----- From: "Mridula Mahadevan" To: "Tom Lane" Cc: Sent: Wednesday, February 03, 2010 2:23 AM Subject: Re: [PERFORM] Queries within a function Tom, I cannot run execute because all these are temp tables with drop on auto commit within a function. This should have something to do with running it in a transaction, when I run them in autocommit mode (without a drop on autocommit) the queries return in a few seconds. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, February 02, 2010 11:28 AM To: Mridula Mahadevan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Queries within a function 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance