Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Mario Splivalo" <mario.splivalo@megafon.hr>
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>
	<4CF5FEA9.3000900@megafon.hr>
Date: Wed, 01 Dec 2010 09:23:23 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vm0z89fgeorkce@apollo13>
In-Reply-To: <4CF5FEA9.3000900@megafon.hr>
User-Agent: Opera Mail/10.62 (Linux)


> Just once.

OK, another potential problem eliminated, it gets strange...

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

No, it doesn't really matter.

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

If you CLUSTER a table, it is entirely rebuilt so if your disk free space  
isn't heavily fragmented, you can hope the table and indexes will get  
allocated in a nice contiguous segment.