Re: Performance on temp table inserts

On Tue, 2003-05-20 at 12:04, Tom Lane wrote:
> Jeff Boes <jboes(at)nexcerpt(dot)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