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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
