X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 504F24758E6 for ; Fri, 21 Feb 2003 14:25:43 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 43938-05 for ; Fri, 21 Feb 2003 14:25:32 -0500 (EST) Received: from maverick.digital-ics.com (maverick.digital-ics.com [63.239.101.196]) by postgresql.org (Postfix) with ESMTP id B72DF475BEC for ; Fri, 21 Feb 2003 14:18:15 -0500 (EST) Received: from digital-ics.com (gremlin.ics.digital-ics.com [192.168.2.52]) by maverick.digital-ics.com (8.12.6/8.12.6) with ESMTP id h1LJIDhm002069; Fri, 21 Feb 2003 14:18:13 -0500 (EST) Message-ID: <3E567B74.5030807@digital-ics.com> Date: Fri, 21 Feb 2003 14:18:12 -0500 From: Kevin White User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "scott.marlowe" Cc: pgsql-performance@postgresql.org Subject: Re: Really bad insert performance: what did I do wrong? References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/160 X-Sequence-Number: 1216 > 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