If it is 7.4 beta 5 or later, I would definitely go with A.

Adding indexes after the fact seems to be much quicker. Foreign keys use
the same algorithm prior to beta 5 regardless of timing. 

A primary key and unique index will have approx the same performance (a
check for NULL isn't very costly).

On Fri, 2003-10-31 at 11:02, Allen Landsidel wrote:
> Yet another question.. thanks to everyone responding to all these so far.. ;)
> 
> This one is basically.. given I have a big table already in COPY format, 
> about 28 million rows, all keys guaranteed to be unique, I'm trying to find 
> out which of the following will get the import finished the fastest:
> 
> a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), then 
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and 
> each fk needed.
> 
> b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE 
> UNIQUE INDEX on the PK.
> 
> c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on 
> needed columns, then run the COPY.
> 
> d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE 
> INDEX after table creation, then run the COPY.
> 
> My gut instinct tells me that in order, fastest to slowest, it's going to 
> be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and 
> Oracle.
> 
> If there isn't a significant difference between all of them, performance 
> wise, I think something is dreadfully wrong here.  Running "a", the ALTER 
> TABLE to add the PK ran for 17 hours and still wasn't finished.
> 
> The table without indexes or keys is:
> CREATE TABLE foo (
> id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
> master_id BIGINT NOT NULL,
> other_id INTEGER NOT NULL,
> status INTEGER NOT NULL,
> addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
> );
> 
> Details on machine and configuration are:
> 
> The machine is the same one I've mentioned before.. SMP AthlonMP 2800+ 
> (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a 
> quad-channel ICP-Vortex controller, 2GB physical memory.  Running FreeBSD 
> RELENG_4, relevant filesystems with softupdates enabled and mounted noatime.
> 
> kernel options are:
> maxusers        0
> 
> options         MAXDSIZ="(1536UL*1024*1024)" # maximum limit
> options         MAXSSIZ="(512UL*1024*1024)"  # maximum stack
> options         DFLDSIZ="(512UL*1024*1024)"  # default limit
> options         VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped 
> from default 200MB
> options         SYSVSHM                 #SYSV-style shared memory
> options         SYSVMSG                 #SYSV-style message queues
> options         SYSVSEM                 #SYSV-style semaphores
> options         SHMMAXPGS=262144
> options         SHMALL=262144
> options         SHMSEG=256
> options         SEMMNI=384
> options         SEMMNS=768
> options         SEMMNU=384
> options         SEMMAP=384
> 
> postgresql.conf settings are:
> 
> shared_buffers = 30000
> max_fsm_relations = 10000
> max_fsm_pages = 2000000
> max_locks_per_transaction = 64
> wal_buffers = 128
> sort_mem = 1310720 (1.2GB)
> vacuum_mem = 262144 (256MB)
> checkpoint_segments = 64
> checkpoint_timeout = 1200
> commit_delay = 20000
> commit_siblings = 2
> fsync=true
> random_page_cost = 1.7
> cpu_tuple_cost = 0.005
> cpu_index_tuple_cost = 0.005
> cpu_operator_cost = 0.0012
> 
> stats_start_collector = true
> stats_command_string = true
> stats_row_level = true
> stats_block_level = true
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 

Attachment: signature.asc
Description: This is a digitally signed message part

Reply via email to