A-Original-Client-UID: 188.129.74.222 splivalo
Message-ID: <4CF2A8A6.4010400@megafon.hr>
Date: Sun, 28 Nov 2010 20:08:22 +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: pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr> <op.vmv9j3oneorkce@apollo13>
In-Reply-To: <op.vmv9j3oneorkce@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11/28/2010 07:56 PM, Pierre C wrote:
>
>> When I remove foreign constraints (drones_history_fk__samples and
>> drones_history_fk__drones) (I leave the primary key on drones_history)
>> than that INSERT, even for 50k rows, takes no more than a second.
>>
>> So, my question is - is there anything I can do to make INSERTS with
>> PK faster? Or, since all the reference checking is done inside the
>> procedure for loading data, shall I abandon those constraints entirely?
>>
>> Mario
>
> Maybe... or not. Can you post details about :
>
> - the foreign keys
> - the tables that are referred to (including indexes)

I pasted DDL at the begining of my post. The only indexes tables have 
are the ones created because of PK constraints. Table drones has around 
100k rows. Table drones_history has around 30M rows. I'm not sure what 
additional info you'd want but I'll be more than happy to provide more 
relevant information.


> CREATE TABLE foo (x INTEGER PRIMARY KEY); I
> generate_series( 1,100000 );
> Temps : 766,182 ms
> test=> VACUUM ANALYZE foo;
> Temps : 71,938 ms
> test=> CREATE TABLE bar ( x INTEGER REFERENCES foo(x) );
> CREATE TABLE
> test=> INSERT INTO bar SELECT * FROM generate_series( 1,100000 );
> Temps : 2834,430 ms
>
> As you can see, 100.000 FK checks take less than 3 seconds on this very
> simple example. There is probably something that needs fixing.


Yes, when the FKyed table is small enough inserts are quite fast. But 
when they grow larger the whole system slows down.

I just repeated your test and I'm getting similar results - on my 
desktop. I'll try to assemble some code to recreate workload and see if 
I'll run into same problems.

	Mario