Subject: Re: Performance on temp table inserts
From: Jeff Boes <jboes@nexcerpt.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <29494.1053446676@sss.pgh.pa.us>
References: <baba9i$2vi8$1@news.hub.org> <26320.1053406306@sss.pgh.pa.us>
	<1053434229.4973.1.camel@takin.private.nexcerpt.com>
	<29494.1053446676@sss.pgh.pa.us>
Content-Type: text/plain
Organization: Nexcerpt, Inc.
Message-Id: <1053447308.4970.50.camel@takin.private.nexcerpt.com>
Mime-Version: 1.0
Date: 20 May 2003 12:15:08 -0400
Content-Transfer-Encoding: 7bit

On Tue, 2003-05-20 at 12:04, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> >>> What factors affect the performance of a temp table insert?
> >> 
> >> PG version would have a lot to do with that, considering how we've
> >> whacked around the temp-table implementation in the last few releases.
> 
> > PG version is 7.2.4.
> 
> IIRC, temp tables are really indistinguishable from regular tables in
> 7.2, as far as performance goes.  The bottleneck for the bare insert
> itself would probably be WAL --- are you sure all the WAL settings are
> the same on the two boxes?  If you have indexes, foreign keys, triggers,
> rules, etc on the table then of course you have other overhead to worry
> about ... but that seems somewhat unlikely for a temp table.

wal_files = 2
wal_buffers = 32

on both machines. There isn't an index on the temp table.

> 
> Also, is the temp table being filled in the same transaction that
> created it, or in separate transaction(s)?  That makes a difference in
> 7.2 and before (but not in 7.3).
> 
> 			regards, tom lane

The inserts are being processed in DBI using 'AutoCommit=1', thus they
are in a separate transaction. But it's the same on both boxes.

-- 
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise