pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin White <kwhite@digital-ics.com>
To: scott.marlowe <scott.marlowe@ihs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Really bad insert performance: what did I do wrong?
Date: Fri, 21 Feb 2003 14:18:12 -0500
Message-ID: <3E567B74.5030807@digital-ics.com> (raw)
In-Reply-To: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com>

> 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




view thread (11+ messages)  latest in thread

Message-ID: <3E567B74.5030807@digital-ics.com>
Permalink:  ../3E567B74.5030807@digital-ics.com/
Also on:    postgresql.org/message-id/3E567B74.5030807@digital-ics.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kwhite@digital-ics.com, scott.marlowe@ihs.com
  Subject: Re: Really bad insert performance: what did I do wrong?
  In-Reply-To: <3E567B74.5030807@digital-ics.com>
  References: <Pine.LNX.4.33.0302211049090.17876-100000@css120.ihs.com> <3E567B74.5030807@digital-ics.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox