From: Jeff Boes <jboes@nexcerpt.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance on temp table inserts
Date: 20 May 2003 12:15:08 -0400
Message-ID: <1053447308.4970.50.camel@takin.private.nexcerpt.com> (raw)
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>
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
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-sql@postgresql.org
Cc: jboes@nexcerpt.com, tgl@sss.pgh.pa.us
Subject: Re: Performance on temp table inserts
In-Reply-To: <1053447308.4970.50.camel@takin.private.nexcerpt.com>
* 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