Message-ID: <4CF3522B.2050203@catalyst.net.nz>
Date: Mon, 29 Nov 2010 20:11:39 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr>
In-Reply-To: <4CF24103.8030209@megafon.hr>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 29/11/10 00:46, Mario Splivalo wrote:
>
> 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 created indexes on drones_history(sample_id) and  
drones_history(drone_id)? That would probably help speed up your INSERT 
quite a bit if you have not done so.

Also it would be worthwhile for you to post the output of:

EXPLAIN ANALYZE INSERT INTO drones_history (sample_id, drone_id, 
drone_log_notice, drone_temperature, drone_pressure)
SELECT * FROM tmpUpdate;

to the list, so we can see what is taking the time.

Cheers

Mark