pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: David Shadovitz <david@shadovitz.com>
To: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Query plan - now what?
Date: Sat, 13 Dec 2003 23:21:36 -0800
Message-ID: <01C3C1CF.EC044640.david@shadovitz.com> (raw )
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);
view thread (6+ messages)
Message-ID: <01C3C1CF.EC044640.david@shadovitz.com>
Permalink: ../01C3C1CF.EC044640.david@shadovitz.com/
Also on: postgresql.org/message-id/01C3C1CF.EC044640.david@shadovitz.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david@shadovitz.com, tgl@sss.pgh.pa.us
Subject: Re: Query plan - now what?
In-Reply-To: <01C3C1CF.EC044640.david@shadovitz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox