> 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.
> 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.
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2003-02-22 02:20:39|
|Subject: Re: slow query |
|Previous:||From: scott.marlowe||Date: 2003-02-21 17:55:41|
|Subject: Re: Really bad insert performance: what did I do wrong?|