Re: SELECT INTO large FKyed table is slow

On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote:
> The database for monitoring certain drone statuses is quite simple:
> 

> This is the slow part:
> INSERT INTO drones_history (sample_id, drone_id, drone_log_notice, 
> drone_temperature, drone_pressure)
> SELECT * FROM tmpUpdate;
> 
> For 100 rows this takes around 2 seconds. For 1000 rows this takes 
> around 40 seconds. For 5000 rows this takes around 5 minutes.
> For 50k rows this takes around 30 minutes! Now this is where I start lag 
> because I get new CSV every 10 minutes or so.

Have you considered making the foreign key check deferrable?

JD

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt