A-Original-Client-UID: 188.129.73.52 splivalo
Message-ID: <4CF39CF4.2050506@megafon.hr>
Date: Mon, 29 Nov 2010 13:30:44 +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: mark.kirkwood@catalyst.net.nz
CC: pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr> <4CF3522B.2050203@catalyst.net.nz>
In-Reply-To: <4CF3522B.2050203@catalyst.net.nz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11/29/2010 08:11 AM, Mark Kirkwood wrote:
> 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.

Yes, since (sample_id, drone_id) is primary key, postgres created 
composite index on those columns. Are you suggesting I add two more 
indexes, one for drone_id and one for sample_id?

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

Is there a way to do so inside plpgsql function?

I can recreate the whole process within psql and then post the explain 
analyze, it would just take me some time to do so. I'll post as soon as 
I'm done.

	Mario