Skip site navigation (1) Skip section navigation (2)

Re: Performance Concern

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: john(at)pagakis(dot)com
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance Concern
Date: 2003-10-24 18:22:42
Message-ID: 1067019762.81651.64.camel@jester (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 2003-10-23 at 08:21, 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.

Using an int for identification is certainly suggested, however it
sounds like you may be short a few indexes on the foreign key'd fields.

EXPLAIN ANALYZE output is always nice..

In response to

Responses

pgsql-performance by date

Next:From: Sean ShannyDate: 2003-10-24 18:30:55
Subject: Re: Performance Concern
Previous:From: Vivek KheraDate: 2003-10-24 16:47:19
Subject: Re: My own performance/tuning q&a

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group