Re: Query plan - now what? - Mailing list pgsql-performance

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);