From: Jeff Boes <jboes@nexcerpt.com>
Subject: Performance on temp table inserts
Date: Mon, 19 May 2003 15:11:48 -0400
Organization: Nexcerpt, Inc.
Lines: 26
Message-ID: <baba9i$2vi8$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.11.4 (Unix)
To: pgsql-sql@postgresql.org

What factors affect the performance of a temp table insert?  On nearly
identical boxes, I have recorded these kinds of statistics:

"INSERT INTO temp_link_checksums VALUES (?)","25957","0.009","245.546"

(That's the statement text, the number of executions, the average, and
the total wallclock time.)

"INSERT INTO temp_link_checksums VALUES (?)","76962","0.041","3184.835"

Box #1 is our "production" server, and actually has more going on during
the day than #2 which is the "test" server.  #1 has "shared_buffers" set
to 131072; #2 has it at 250000. Hardware, O/S and PostgreSQL versions are
all identical.  Software executing the query (Perl code using DBI) is the
same.

I don't have stats on std-dev for these results; I suppose there could be
an outlying value in the #2 box that dragged down the average. But before
I re-instrument my statistics-gathering code, I thought I'd check to see
if anyone knows how temp tables and shared memory might interact.

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