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 6E84FD1B510 for ; Thu, 23 Oct 2003 12:18: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 44627-02 for ; Thu, 23 Oct 2003 09:17:44 -0300 (ADT) Received: from fed1mtao07.cox.net (fed1mtao07.cox.net [68.6.19.124]) by svr1.postgresql.org (Postfix) with ESMTP id C29E1D1B4F2 for ; Thu, 23 Oct 2003 09:17:25 -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 <20031023121725.YNHM13061.fed1mtao07.cox.net@toolsmythes01> for ; Thu, 23 Oct 2003 08:17:25 -0400 Reply-To: From: "John Pagakis" To: Subject: Performance Concern Date: Thu, 23 Oct 2003 05:21:03 -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 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=2.5 tagged_above=0.0 required=5.0 tests=BANG_QUOTE, MSGID_GOOD_EXCHANGE X-Spam-Level: ** X-Archive-Number: 200310/623 X-Sequence-Number: 4371 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