Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Joachim Worringen" <joachim.worringen@iathh.de>,
	pgsql-performance@postgresql.org
Subject: Re: performance of temporary vs. regular tables
References: <4BFB837C.6010005@iathh.de>
	<AANLkTik4_exB6U3GlfLMNIgPSk0G3RVQ8fWBTzwekrke@mail.gmail.com>
	<4BFB91A8.20805@iathh.de> <201005251241.59605.andres@anarazel.de>
	<4BFD4643.9010901@iathh.de> <4BFFA32D.8010009@iathh.de>
Date: Wed, 02 Jun 2010 12:03:42 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vdn3kgtheorkce@immo.peufeu.com>
In-Reply-To: <4BFFA32D.8010009@iathh.de>
User-Agent: Opera Mail/10.10 (Linux)


> As promised, I did a tiny benchmark - basically, 8 empty tables are  
> filled with 100k rows each within 8 transactions (somewhat typically for  
> my application). The test machine has 4 cores, 64G RAM and RAID1 10k  
> drives for data.
>
> # INSERTs into a TEMPORARY table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real    3m18.242s
> user    1m59.074s
> sys     1m51.001s
>
> # INSERTs into a standard table:
> [joachim@testsrv scaling]$ time pb query -d scaling_qry_1.xml
>
> real    3m35.090s
> user    2m5.295s
> sys     2m2.307s
>
> Thus, there is a slight hit of about 10% (which may even be within  
> meausrement variations) - your milage will vary.

Usually WAL causes a much larger performance hit than this.

Since the following command :

CREATE TABLE tmp AS SELECT n FROM generate_series(1,1000000) AS n

which inserts 1M rows takes 1.6 seconds on my desktop, your 800k rows  
INSERT taking more than 3 minutes is a bit suspicious unless :

- you got huge fields that need TOASTing ; in this case TOAST compression  
will eat a lot of CPU and you're benchmarking TOAST, not the rest of the  
system
- you got some non-indexed foreign key
- some other reason ?