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 D2971D1B93D for ; Fri, 31 Oct 2003 18:27:21 +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 48317-10 for ; Fri, 31 Oct 2003 14:26:51 -0400 (AST) Received: from mta8.srv.hcvlny.cv.net (mta8.srv.hcvlny.cv.net [167.206.5.75]) by svr1.postgresql.org (Postfix) with ESMTP id 72094D1B58F for ; Fri, 31 Oct 2003 14:26:12 -0400 (AST) Received: from megalomaniac.biosys.net (ool-43529ac2.dyn.optonline.net [67.82.154.194]) by mta8.srv.hcvlny.cv.net (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HNM0038FWJNZU@mta8.srv.hcvlny.cv.net> for pgsql-performance@postgresql.org; Fri, 31 Oct 2003 13:26:12 -0500 (EST) Date: Fri, 31 Oct 2003 13:28:44 -0500 From: Allen Landsidel Subject: Re: index creation order? In-reply-to: <1067617409.17097.7.camel@jester> X-Sender: bsdasym@pop.hotpop.com To: Rod Taylor Cc: Postgresql Performance Message-id: <6.0.0.22.0.20031031132741.02405968@pop.hotpop.com> MIME-version: 1.0 X-Mailer: QUALCOMM Windows Eudora Version 6.0.0.22 Content-type: text/plain; charset=us-ascii; format=flowed Content-transfer-encoding: 7BIT References: <6.0.0.22.0.20031031104214.024853b0@pop.hotpop.com> <1067617409.17097.7.camel@jester> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/802 X-Sequence-Number: 4550 Nope, still 7.3.4 here.. I am very excited about 7.4 though.. almost as excited as I am about FreeBSD 5.x going -STABLE.. it's a close race between the two.. I'll keep this in mind for when I update though, thanks. At 11:23 10/31/2003, Rod Taylor wrote: >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 majordomo@postgresql.org) > >