Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 1A317633EF5
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  3 Feb 2010 12:12:13 -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 44797-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  3 Feb 2010 16:11:53 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.27])
	by mail.postgresql.org (Postfix) with ESMTP id 42ABE6333DE
	for <pgsql-performance@postgresql.org>;
	Wed,  3 Feb 2010 12:10:48 -0400 (AST)
Received: by ey-out-2122.google.com with SMTP id 9so286488eyd.33
	for <pgsql-performance@postgresql.org>;
	Wed, 03 Feb 2010 08:10:47 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=5H0/HVjVRbhUmjDrksxdLupBwSXwOTvGJcQRJMiUL30=;
	b=dljQDJcUV9VBLC2VAA4RpkympBsIsO4z2jPrsxToOIeFo6TjD4Ni2gLQnYKy+0xKYY
	tGLk5wYdGlai+s2e3EvtAzQLsfHzHTUYpHveAwAOdYNsHEbaUEGiSCsbKMNAoY5sErMe
	lR/tUDFTWfugbJoIabm+YpufSpJ9okrydL/lg=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=BkGYrRcWIiLYGabb1w4fZVR5zmMleanXjEu9aCIHXun5yYF2a26ITD39doQomypyYZ
	NYxJvjOWHARtr2WhROG3T0cB2IwPQV5ztHBMlkt2dIgFOR935Rstr0WRidH7hz0Nh21p
	4I02ZDe+ZltZHAKc+Smo9qA4t7GU7JOpCON+A=
MIME-Version: 1.0
Received: by 10.213.77.77 with SMTP id f13mr7216744ebk.66.1265213447008; Wed,
	03 Feb 2010 08:10:47 -0800 (PST)
In-Reply-To: 
 <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com>
References: 
 <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com>
Date: Wed, 3 Feb 2010 18:10:46 +0200
Message-ID: <331e40661002030810p445dd862i3eb9f3c025943d1d@mail.gmail.com>
Subject: Re: Queries within a function
From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com>
To: Mridula Mahadevan <mmahadevan@stratify.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary=00c09f7f9f2912f45e047eb476ff
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.391 tagged_above=-10 required=5 tests=AWL=1.207,
	BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201002/61
X-Sequence-Number: 37482

--00c09f7f9f2912f45e047eb476ff
Content-Type: text/plain; charset=ISO-8859-1

2010/2/2 Mridula Mahadevan <mmahadevan@stratify.com>

>  Hi,
>
>  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?
>
> Thanks
>
Have you tried to analyze temp tables after you've populated them? Because
AFAIK it won't do it automatically for tables created, filled and then used
 in same transaction.

--00c09f7f9f2912f45e047eb476ff
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">2010/2/2 Mridula Mahadevan <span dir=3D"=
ltr">&lt;<a href=3D"mailto:mmahadevan@stratify.com">mmahadevan@stratify.com=
</a>&gt;</span><br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 =
.8ex;border-left:1px #ccc solid;padding-left:1ex;">









<div lang=3D"EN-US" link=3D"blue" vlink=3D"purple">

<div>

<p class=3D"MsoNormal">Hi,</p>

<p class=3D"MsoNormal">=A0I am running a bunch of queries within a function=
, creating
some temp tables and populating them. When the data exceeds say, 100k the q=
ueries
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 w=
hat
may be going on and any postgresql.conf parameters etc that might help?</p>

<p class=3D"MsoNormal">Thanks</p>

</div>

</div>


</blockquote></div>Have you tried to analyze temp tables after you&#39;ve p=
opulated them? Because AFAIK it won&#39;t do it automatically for tables cr=
eated, filled and then used =A0in same transaction.

--00c09f7f9f2912f45e047eb476ff--