Message-ID: <4C06547E.2010104@iathh.de>
Date: Wed, 02 Jun 2010 14:54:22 +0200
From: Joachim Worringen <joachim.worringen@iathh.de>
Organization: IAT GmbH
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; de;
	rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: 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>
	<op.vdn3kgtheorkce@immo.peufeu.com>
In-Reply-To: <op.vdn3kgtheorkce@immo.peufeu.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Am 02.06.2010 12:03, schrieb Pierre C:
> 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 ?

Yes, the "other" reason is that I am not issueing a single SQL command, 
but import data from plain ASCII files through the Pyhton-based 
framework into the database.

The difference between your measurement and my measurent is the upper 
potential of improvement for my system (which has, on the other hand, 
the advantage of being a bit more powerful and flexible than a single 
SQL statement....;-) )

  Joachim