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 E215CD1B51B for ; Sat, 25 Oct 2003 00:25:15 +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 98665-07 for ; Fri, 24 Oct 2003 21:24:47 -0300 (ADT) Received: from fed1mtao07.cox.net (fed1mtao07.cox.net [68.6.19.124]) by svr1.postgresql.org (Postfix) with ESMTP id 80CA5D1B51A for ; Fri, 24 Oct 2003 21:24:38 -0300 (ADT) Received: from toolsmythes01 ([68.98.9.240]) by fed1mtao07.cox.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with SMTP id <20031025002434.CNHA3394.fed1mtao07.cox.net@toolsmythes01>; Fri, 24 Oct 2003 20:24:34 -0400 Reply-To: From: "John Pagakis" To: "Sean Shanny" Cc: Subject: Re: Performance Concern Date: Fri, 24 Oct 2003 17:28:06 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-Reply-To: <3F996FDF.1040307@earthlink.net> Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=BANG_QUOTE, BAYES_30, EMAIL_ATTRIBUTION, HTML_TAG_BALANCE_TABLE, IN_REP_TO, MSGID_GOOD_EXCHANGE, ORIGINAL_MESSAGE, QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES X-Spam-Level: X-Archive-Number: 200310/650 X-Sequence-Number: 4398 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 > > >