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 0E67CD1B8B4 for ; Fri, 31 Oct 2003 16:24:00 +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 19042-10 for ; Fri, 31 Oct 2003 12:23:28 -0400 (AST) Received: from mail.hive.nj2.inquent.com (mc.carriermail.com [205.178.180.9]) by svr1.postgresql.org (Postfix) with SMTP id 7734CD1B56A for ; Fri, 31 Oct 2003 12:23:27 -0400 (AST) Received: (qmail 2692 invoked from network); 31 Oct 2003 16:23:49 -0000 Received: from unknown (HELO ?10.0.2.5?) (216.208.117.7) by 205.178.180.9 with SMTP; 31 Oct 2003 16:23:49 -0000 Subject: Re: index creation order? From: Rod Taylor To: Allen Landsidel Cc: Postgresql Performance In-Reply-To: <6.0.0.22.0.20031031104214.024853b0@pop.hotpop.com> References: <6.0.0.22.0.20031031104214.024853b0@pop.hotpop.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-9KKvaj9OInRImQz+c2Ho" Message-Id: <1067617409.17097.7.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Fri, 31 Oct 2003 11:23:30 -0500 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/795 X-Sequence-Number: 4543 --=-9KKvaj9OInRImQz+c2Ho Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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.=20 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.= . ;) >=20 > This one is basically.. given I have a big table already in COPY format,= =20 > about 28 million rows, all keys guaranteed to be unique, I'm trying to fi= nd=20 > out which of the following will get the import finished the fastest: >=20 > a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), th= en=20 > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk an= d=20 > each fk needed. >=20 > b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE= =20 > UNIQUE INDEX on the PK. >=20 > c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on= =20 > needed columns, then run the COPY. >=20 > d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE=20 > INDEX after table creation, then run the COPY. >=20 > My gut instinct tells me that in order, fastest to slowest, it's going to= =20 > be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and= =20 > Oracle. >=20 > If there isn't a significant difference between all of them, performance= =20 > wise, I think something is dreadfully wrong here. Running "a", the ALTER= =20 > TABLE to add the PK ran for 17 hours and still wasn't finished. >=20 > 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() > ); >=20 > Details on machine and configuration are: >=20 > The machine is the same one I've mentioned before.. SMP AthlonMP 2800+=20 > (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a=20 > quad-channel ICP-Vortex controller, 2GB physical memory. Running FreeBSD= =20 > RELENG_4, relevant filesystems with softupdates enabled and mounted noati= me. >=20 > kernel options are: > maxusers 0 >=20 > options MAXDSIZ=3D"(1536UL*1024*1024)" # maximum limit > options MAXSSIZ=3D"(512UL*1024*1024)" # maximum stack > options DFLDSIZ=3D"(512UL*1024*1024)" # default limit > options VM_BCACHE_SIZE_MAX=3D"(384UL*1024*1024)" # cache size upp= ed=20 > from default 200MB > options SYSVSHM #SYSV-style shared memory > options SYSVMSG #SYSV-style message queues > options SYSVSEM #SYSV-style semaphores > options SHMMAXPGS=3D262144 > options SHMALL=3D262144 > options SHMSEG=3D256 > options SEMMNI=3D384 > options SEMMNS=3D768 > options SEMMNU=3D384 > options SEMMAP=3D384 >=20 > postgresql.conf settings are: >=20 > shared_buffers =3D 30000 > max_fsm_relations =3D 10000 > max_fsm_pages =3D 2000000 > max_locks_per_transaction =3D 64 > wal_buffers =3D 128 > sort_mem =3D 1310720 (1.2GB) > vacuum_mem =3D 262144 (256MB) > checkpoint_segments =3D 64 > checkpoint_timeout =3D 1200 > commit_delay =3D 20000 > commit_siblings =3D 2 > fsync=3Dtrue > random_page_cost =3D 1.7 > cpu_tuple_cost =3D 0.005 > cpu_index_tuple_cost =3D 0.005 > cpu_operator_cost =3D 0.0012 >=20 > stats_start_collector =3D true > stats_command_string =3D true > stats_row_level =3D true > stats_block_level =3D true >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >=20 --=-9KKvaj9OInRImQz+c2Ho Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQA/ooyB6DETLow6vwwRAv/9AJ9Zma6DsVEj3wYqtVqpZOZ5z8qhoACcCuGr kRlIUuL8WSYcTO8xA6MVa5I= =25jG -----END PGP SIGNATURE----- --=-9KKvaj9OInRImQz+c2Ho--