Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E59296503F0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  5 Nov 2008 19:16:22 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 86253-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  5 Nov 2008 19:16:22 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 559A26503E5
	for <pgsql-performance@postgresql.org>;
	Wed,  5 Nov 2008 19:16:22 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Wed, 05 Nov 2008 17:16:21 -0600
Message-Id: <4911D4DB.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.3 
Date: Wed, 05 Nov 2008 17:16:11 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,
	"Kevin Grittner" <Kgrittn.CCAP.Courts@wicourts.gov>
Subject: Re: Create and drop temp table in 8.3.4
References: <4911957A.EE98.0025.0@wicourts.gov>
In-Reply-To: <4911957A.EE98.0025.0@wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Archive-Number: 200811/17
X-Sequence-Number: 31748

>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: 
> I'm going to get the latest snapshot to see if the issue has changed
> for 8.4devel
 
In testing under today's snapshot, it seemed to take 150,000 writes to
create and drop 1,000 temporary tables within a database transaction. 
The numbers for the various versions might be within the sampling
noise, since the testing involved manual steps and required saturating
the queues in PostgreSQL, the OS, and the RAID controller to get
meaningful numbers.  It seems like the complaints of slowness result
primarily from these writes saturating the bandwidth when a query
generates a temporary table in a loop, with the increased impact in
later releases resulting from it getting through the loop faster.
 
I've started a thread on the hackers' list to discuss a possible
PostgreSQL enhancement to help such workloads.  In the meantime, I
think I know which knobs to try turning to mitigate the issue, and
I'll suggest rewrites to some of these queries, to avoid the temporary
tables.
 
If I find a particular tweak to the background writer or some such is
particularly beneficial, I'll post again.
 
-Kevin