X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B7173D1B4EB for ; Sat, 25 Oct 2003 01:16:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 07872-05 for ; Fri, 24 Oct 2003 22:15:46 -0300 (ADT) Received: from harrier.mail.pas.earthlink.net (harrier.mail.pas.earthlink.net [207.217.120.12]) by svr1.postgresql.org (Postfix) with ESMTP id 4B2E0D1B51A for ; Fri, 24 Oct 2003 22:15:38 -0300 (ADT) Received: from dpc6682163108.direcpc.com ([66.82.163.108] helo=earthlink.net) by harrier.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1ADD1k-0004Me-00 for pgsql-performance@postgresql.org; Fri, 24 Oct 2003 18:15:39 -0700 Message-ID: <3F99CEAF.9000709@earthlink.net> Date: Fri, 24 Oct 2003 21:15:27 -0400 From: Sean Shanny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-performance@postgresql.org Subject: Re: Performance Concern References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/644 X-Sequence-Number: 4392 John, One other thing I forgot to mention with solution 2. If you are going to be adding a fair number of records to the table on an ongoing basis you will want to drop indexes first and re-create them after the load is complete. I have tried it both ways and dropping is faster overall. --sean John Pagakis wrote: >Sean - >I believe auto-commit was off (not at the box right now). I'll play with >the commit interval; I know commits are expensive operations. > >Thanks for item 2. I was toying with the notion of pre-creating 100000 >bazes off-loading them and then seeing if the COPY would be any faster; you >saved me the effort of experimenting. Thanks for the benefit of your >experience. > >__________________________________________________________________ >John Pagakis >Email: ih8spam_thebfh@toolsmythe.com > > >"Oh, you hate your job? Why didn't you say so? > There's a support group for that. It's called > EVERYBODY, and they meet at the bar." > -- Drew Carey > >This signature generated by > ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. > www.spazmodicfrog.com > > >-----Original Message----- >From: Sean Shanny [mailto:shannyconsulting@earthlink.net] >Sent: Friday, October 24, 2003 11:31 AM >To: john@pagakis.com >Cc: pgsql-performance@postgresql.org >Subject: Re: [PERFORM] Performance Concern > > >John, > >Are you treating each insertion as a separate transaction? If so the >performance will suffer. I am doing the same thing in building a data >warehouse using PG. I have to load millions of records each night. I >do two different things: > >1) If I need to keep the insertions inside the java process I turn off >auto-commit and every n insertions (5000 seems to give me the best >performance for my setup) issue a commit. Make sure you do a final >commit in a finally block so you don't miss anything. > >2) Dump all the data to a file and then use a psql COPY >(columns) FROM 'file path' call to load it. Very fast. > >--sean > >John Pagakis wrote: > > > >>Greetings. >> >>I have a table that will require 100,000 rows initially. >> >>Assume the following (some of the field names have been changed for >>confidentiality reasons): >> >>CREATE TABLE baz ( >> baz_number CHAR(15) NOT NULL, >> customer_id CHAR(39), >> foobar_id INTEGER, >> is_cancelled BOOL DEFAULT false NOT NULL, >> create_user VARCHAR(60) NOT NULL, >> create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >> last_update_user VARCHAR(60) NOT NULL, >> last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >> CONSTRAINT PK_baz PRIMARY KEY (baz_number) >>); >> >>ALTER TABLE baz >> ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); >> >>ALTER TABLE baz >> ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); >> >> >>Using JDBC, it took approximately one hour to insert 100,000 records. I >>have an algorithm to generate a unique baz_number - it is a mixture of >> >> >alpha > > >>and numerics. >> >>There is a purchase table; one purchase can have many associated baz >>records, but the baz records will be pre-allocated - baz.customer_id allows >>null. The act of purchasing a baz will cause baz.customer_id to be >>populated from the customer_id (key) field in the purchase table. >> >>If it took an hour to insert 100,000 records, I can only imagine how much >>time it will take if one customer were to attempt to purchase all 100,000 >>baz. Certainly too long for a web page. >> >>I've not had to deal with this kind of volume in Postgres before; I have my >>suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) >>but I'd *LOVE* any thoughts. >> >>Would I be better off making the key an identity field and not indexing on >>baz_number? >> >>Thanks in advance for any help. >> >>__________________________________________________________________ >>John Pagakis >>Email: ih8spam_thebfh@toolsmythe.com >> >> >>"The best way to make your dreams come true is to wake up." >> -- Paul Valery >> >>This signature generated by >> ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. >> www.spazmodicfrog.com >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> >> >> >> >> > > > >