Message-ID: <4CF5FEA9.3000900@megafon.hr>
Date: Wed, 01 Dec 2010 08:52:09 +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 Thunderbird/3.0.10
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: Mladen Gogala <mladen.gogala@vmsinfo.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr> <4CF5259C.8000306@vmsinfo.com>
	<4CF59017.1090705@megafon.hr> <op.vm0fb7z4eorkce@apollo13>
In-Reply-To: <op.vm0fb7z4eorkce@apollo13>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 12/01/2010 01:51 AM, Pierre C wrote:
> 
>> Now I tried removing the constraints from the history table (including
>> the PK) and the inserts were fast. After few 'rounds' of inserts I
>> added constraints back, and several round after that were fast again.
>> But then all the same. Insert of some 11k rows took 4 seconds (with
>> all constraints) and now the last one of only 4k rows took one minute.
>> I did vacuum after each insert.
>>
>>
>>     Mario
> 
> Hm, so for each line of drones_history you insert, you also update the
> correspoding drones table to reflect the latest data, right ?

Yes.

> How many times is the same row in "drones" updated ? ie, if you insert N
> rows in drones_nistory, how may drone_id's do you have ?

Just once.

If I have 5000 lines in CSV file (that I load into 'temporary' table
using COPY) i can be sure that drone_id there is PK. That is because CSV
file contains measurements from all the drones, one measurement per
drone. I usualy have around 100 new drones, so I insert those to drones
and to drones_history. Then I first insert into drones_history and then
update those rows in drones. Should I try doing the other way around?

Although, I think I'm having some disk-related problems because when
inserting to the tables my IO troughput is pretty low. For instance,
when I drop constraints and then recreate them that takes around 15-30
seconds (on a 25M rows table) - disk io is steady, around 60 MB/s in
read and write.

It just could be that the ext3 partition is so fragmented. I'll try
later this week on a new set of disks and ext4 filesystem to see how it
goes.

	Mario