From: ramasubramanian <ramasubramanian.g@renaissance-it.com>
To: Mridula Mahadevan <mmahadevan@stratify.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Queries within a function
Date: Wed, 3 Feb 2010 09:54:47 +0530
Message-ID: <FA1CD592A1C54FFEAB399559CF12CC30@ramasubramanian> (raw)
References: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com>
<29024.1265138879@sss.pgh.pa.us>
<0A59BA5B590B7E4A8D441196A9F17E904C68825D30@corpmail11.calpurnia.com>
Hi,
Try using dynamic sql. Query will be faster in a function
regards
Ram
----- Original Message -----
From: "Mridula Mahadevan" <mmahadevan@stratify.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
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 <mmahadevan@stratify.com> 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
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: ramasubramanian.g@renaissance-it.com, mmahadevan@stratify.com
Subject: Re: Queries within a function
In-Reply-To: <FA1CD592A1C54FFEAB399559CF12CC30@ramasubramanian>
* 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