Skip site navigation (1) Skip section navigation (2)

Re: Really bad insert performance: what did I do wrong?

From: Kevin White <kwhite(at)digital-ics(dot)com>
To: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: 2003-02-21 19:18:12
Message-ID: 3E567B74.5030807@digital-ics.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 1:  sun's performance on IDE hardware is abysmal.  

OK, good to know.  This box won't always be the production server: an 
x86 Dell server with Linux will happen...thanks for the info.

> 2:  Postgresql under Solaris on Sparc is about 1/2 as fast as Postgresql 
> under Linux on Sparc, all other things being equal.  On 32 bith Sparc the 
> chasm widens even more.

Wow...

> 3:  Inserting ALL 700,000 rows in one transaction is probably not optimal. 
> Try putting a test in every 1,000 or 10,000 rows to toss a "commit;begin;" 
> pair at the database while loading.  

My original test was 700,000 at once...for today's, I realized that was, 
um, dumb :) so I fixed it...it commits every 1,000 now...

> 4:  If you want to make sure you don't insert any duplicates, it's 
> probably faster to use a unique multi-column key on all your columns 

The problem isn't inserting the dupes, but at times I need to update the 
data, rather than load a new batch of it...and the rows have a "rank" 
(by price)...so when one group of say 5 gets an updated row, it could 
change the rank of the other 4 so all 5 need updated...so I have to do 
the select first to find the other values so I can calculate the rank.

In THIS specific case, with the table empty, I don't need to do that, 
but the code had been changed to do it, because normally, my table won't 
be empty.  This is just the initial setup of a new server...

Thanks for all the help...

It looks like the load finished...I might try turning the sync off.

Kevin


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2003-02-22 02:20:39
Subject: Re: slow query
Previous:From: scott.marloweDate: 2003-02-21 17:55:41
Subject: Re: Really bad insert performance: what did I do wrong?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group