A-Original-Client-UID: 188.129.75.173 splivalo
Message-ID: <4CF75A98.3080709@megafon.hr>
Date: Thu, 02 Dec 2010 09:36:40 +0100
From: Mario Splivalo <mario.splivalo@megafon.hr>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.15) Gecko/20101027 Lightning/1.0b1 Thunderbird/3.0.10
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: mark.kirkwood@catalyst.net.nz, pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr> <4CF3522B.2050203@catalyst.net.nz>
	<4CF39CF4.2050506@megafon.hr> <op.vmxyi50oeorkce@apollo13>
	<4CF587B0.5020003@megafon.hr> <op.vm0055s0eorkce@apollo13>
In-Reply-To: <op.vm0055s0eorkce@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 12/01/2010 09:43 AM, Pierre C wrote:
>
> Note that in both cases postgres reports that the FK checks take 92-120
> milliseconds... which is a normal time for about 4000 rows.
> Inserting 4000 lines with just a few fields like you got should take
> quite much less than 1 s...
>
> Where the rest of the time goes, I have no idea. Disk thrashing ? Locks
> ? Gremlins ?
>
> - try it on a fresh copy of all your tables (CREATE TABLE, INSERT INTO
> SELECT)
> - try to put the WAL on a separate physical disk (or do a check with
> fsync=off)
> - try it on another computer
> - try it on another harddisk
> - run oprofile on a debug compile of postgres
> - it could even be the process title updates (I don't think so but...)
> - try a ramdisk tablespace

I'm allready running it with fsync=off. The funny thing is, as I add new 
realm it runs fine until the history table grows around 5M rows. After 
that the slowdown is huge.

I'm trying this on new hardware this weekend, I'll post here the results.

	Mario