Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: pgsql-performance@postgresql.org, "Mario Splivalo"
	<mario.splivalo@megafon.hr>
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr>
Date: Sun, 28 Nov 2010 19:56:17 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vmv9j3oneorkce@apollo13>
In-Reply-To: <4CF24103.8030209@megafon.hr>
User-Agent: Opera Mail/10.62 (Linux)


> 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)


CREATE TABLE foo (x INTEGER PRIMARY KEY); INSERT INTO foo SELECT * FROM  
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.