pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Pagakis <john@pagakis.com>
To: pgsql-performance@postgresql.org
Subject: Performance Concern
Date: Thu, 23 Oct 2003 05:21:03 -0700
Message-ID: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com> (raw)

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




view thread (16+ messages)  latest in thread

Message-ID: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com>
Permalink:  ../KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com/
Also on:    postgresql.org/message-id/KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.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: john@pagakis.com
  Subject: Re: Performance Concern
  In-Reply-To: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com>
  References: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.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