X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9DF6BD1D8EB for ; Sun, 14 Dec 2003 07:24:01 +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 86498-06 for ; Sun, 14 Dec 2003 03:23:45 -0400 (AST) Received: from flamingo.mail.pas.earthlink.net (flamingo.mail.pas.earthlink.net [207.217.120.232]) by svr1.postgresql.org (Postfix) with ESMTP id 553DDD1BB74 for ; Sun, 14 Dec 2003 03:23:32 -0400 (AST) Received: from hsa070.pool014.at101.earthlink.net ([216.249.85.70] helo=shadovitzcmptr) by flamingo.mail.pas.earthlink.net with smtp (Exim 3.33 #1) id 1AVQbM-0004Yv-00; Sat, 13 Dec 2003 23:23:40 -0800 Received: by localhost with Microsoft MAPI; Sat, 13 Dec 2003 23:22:05 -0800 Message-ID: <01C3C1CF.EC044640.david@shadovitz.com> From: David Shadovitz Reply-To: "david@shadovitz.com" To: "'pgsql-performance@postgresql.org'" Cc: 'Tom Lane' Subject: Re: Query plan - now what? Date: Sat, 13 Dec 2003 23:21:36 -0800 X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=BAYES_30, RCVD_IN_OSIRUSOFT_COM, UPPERCASE_25_50 X-Spam-Level: * X-Archive-Number: 200312/217 X-Sequence-Number: 5077 Here are my query and schema. The ERD is at http://dshadovi.f2o.org/pg_erd.jpg (sorry about its resolution). -David SELECT zbr.zebra_name , dog.dog_name , mnk.monkey_name , wrm.abbreviation || ptr.abbreviation as abbrev2 , whg.warthog_num , whg.color , rhn.rhino_name , der.deer_name , lin.designator , frg.frog_id , frg.sound_id , tgr.tiger_name , frg.leg_length , frg.jump_distance FROM frogs frg , deers der , warthogs whg , rhinos rhn , zebras zbr , dogs dog , monkeys mnk , worms wrm , parrots prt , giraffes grf , lions lin , tigers tgr WHERE 1 = 1 AND frg.deer_id = der.deer_id AND whg.whg_id = frg.frg_id AND frg.rhino_id = rhn.rhino_id AND zbr.zebra_id = dog.zebra_id AND dog.dog_id = mky.dog_id AND mky.dog_id = whg.dog_id AND mky.monkey_num = whg.monkey_num AND whg.worm_id = wrm.worm_id AND whg.parrot_id = prt.parrot_id AND prt.beak = 'L' AND frg.frog_id = grf.frog_id AND grf.lion_id = lin.lion_id AND frg.tiger_id = tgr.tiger_id ; CREATE TABLE zebras ( zebra_id INTEGER NOT NULL, zebra_name VARCHAR(25), PRIMARY KEY (zebra_id), UNIQUE (zebra_name)); CREATE TABLE dogs ( zebra_id INTEGER NOT NULL, dog_id INTEGER NOT NULL, dog_name VARCHAR(25), FOREIGN KEY (zebra_id) REFERENCES zebras (zebra_id), PRIMARY KEY (dog_id), UNIQUE (dog_name, dog_num)); CREATE TABLE monkeys ( dog_id INTEGER NOT NULL, monkey_num INTEGER, monkey_name VARCHAR(25), PRIMARY KEY (dog_id, monkey_num), FOREIGN_KEY (dog_id) REFERENCES dogs (dog_id)); CREATE INDEX mnk_dog_id_idx ON monkeys (dog_id); CREAIE INDEX mnk_mnk_num_idx ON monkeys (monkey_num); CREATE TABLE warthogs ( warthog_id INTEGER NOT NULL, warthog_num INTEGER, color VARCHAR(25) NOT NULL, dog_id INTEGER NOT NULL, monkey_num INTEGER NOT NULL, parrot_id INTEGER, beak CHAR(l), worm_id INTEGER, PRIMARY KEY (warthog_id), FOREIGN KEY (parrot_id, beak) REFERENCES parrots (parrot_id, beak) FOREIGN KEY (dog_id, monkey_num) REFERENCES monkeys (dog_id, monkey_nun) FOREIGN KEY (worm_id) REFERENCES worms (worm_id)); CREATE UNIQUE INDEX whg_whg_id_idx ON warthogs (warthog_id) CREATE INDEX whg_dog_id_idx ON warthogs (dog_id); CREATE INDEX whg_mnk_num_idx ON warthogs (monkey_num) CREATE INDEX whg_wrm_id_idx ON warthogs (worm_id); CREATE INDEX IDX_warthogs_1 ON warthogs (monkey_num, dog_id) CREATE INDEX lOX warthogs_2 ON warthogs (beak, parrot_id); CREATE TABLE worms ( worm_id INTEGER NOT NULL, abbreviation CHAR(l), PRIMARY KEY worm_id)); CREATE TABLE parrots ( parrot_id INTEGER NOT NULL, beak CHAR(1) NOT NULL, abbreviation CHAR(1), PRIMARY KEY (parrot_id, beak)); CREATE INDEX prt_prt_id_idx ON parrots (parrot_id) CREATE INDEX prt_beak_idx ON parrots (beak): CREATE TABLE deers ( deer_id INTEGER NOT NULL, deer_name VARCHAR(40), PRIMARY KEY (deer_id)); CREATE UNIQUE INDEX der_der_id_unq_idx ON deers (deer_id); CREATE TABLE rhinos ( rhino_id INTEGER NOT NULL, rhino_name VARCHAR(255), CONSTRAINT rhn_rhn_name_unique UNIQUE, CONSTRAINT PK_rhn PRIMARY KEY (rhino_id)); CREATE UNIQUE INDEX rhn_rhn_id_unq_idx ON rhinos (rhino_id); CREATE TABLE tigers ( tiger_id INTEGER NOT NULL, tiger_name VARCHAR(255), PRIMARY KEY (tiger_id)); CREATE UNIQUE INDEX tgr_tgr_id_unq_idx ON tigers (tiger_id); CREATE TABLE frogs ( frog_id INTEGER NOT NULL, warthog_id INTEGER NOT NULL, rhino_id INTEGER NOT NULL, deer_id INTEGER NOT NULL, sound_id INTEGER, tiger_id INTEGER, leg_length VARCHAR(255), jump_distance VARCHAR(lOO), PRIMARY KEY (frog_id)); ALTER TABLE frogs ADD FOREIGN KEY (warthog_id) REFERENCES warthogs (warthog_id), ALTER TABLE frogs ADD FOREIGN KEY (rhino_id) REFERENCES rhinos (rhino_id); ALTER TABLE frogs ADD FOREIGN KEY (deer id) REFERENCES deers (deer_id) ALTER TABLE frogs ADD FOREIGN KEY (sound_id) REFERENCES sounds (sound id); ALTER TABLE frogs ADD FOREIGN KEY (tiger_id) REFERENCES tigers (tiger_id); CREATE UNIQUE INDEX frg_frg_id_unq_idx ON frogs (frog_id); CREATE UNIQUE INDEX frg_w_r_d_t_unq_idx ON frogs (warthog_id, rhino_id, deer_id, tiger_id); CREATE INDEX frg_whg_id_idx ON frogs (warthog_id); CREATE INDEX frg rhn_id_idx ON frogs (rhino_id); CREATE INDEX frg_der_id_idx ON frogs (deer_id); CREATE INDEX frg_snd_id_idx ON frogs (sound_id); CREATE INDEX frg_tgr_id_idx ON frogs (tiger_id); CREATE TABLE lions ( lion_id INTEGER NOT NULL, deer_id INTEGER, PRIMARY KEY (lion_id)); CREATE UNIQUE INDEX lin_lin_id_unq_idx ON lions (lion_id); CREATE TABLE frogs_lions ( frog_id INTEGER NOT NULL, lion_id INTEGER NOT NULL, PRIMARY KEY (frog_id, lion_id)); ALTER TABLE frogs_lions ADD FOREIGN KEY (lion_id) REFERENCES lions (lion_id); ALTER TABLE frogs_lions ADD FOREIGN KEY (frog id) REFERENCES frogs (frog_id); CREATE UNIQUE INDEX frg_lin_frg_id_lin_id_unq_idx ON frogs_lions (frog_id, lion_id); CREATE INDEX frg_lin_lin_id_idx ON frogs_lions (lion_id); CREATE INDEX frg_lin_frg_id_idx ON frogs_lions (frog_id);