Обсуждение: Query plan - now what?

Well, now that I have the plan for my slow-running query, what do I do?  Where
should I focus my attention?
Thanks.
-David


Hash Join  (cost=16620.59..22331.88 rows=40133 width=266)  (actual
time=118773.28..580889.01 rows=57076 loops=1)
    ->  Hash Join  (cost=16619.49..21628.48 rows=40133 width=249) (actual
time=118771.29..535709.47 rows=57076 loops=1)
        ->  Hash Join  (cost=16618.41..20724.39 rows=40133 width=240)  (actual
time=118768.04..432327.82 rows=57076 loops=1)
            ->  Hash Join  (cost=16617.34..19920.66 rows=40133 width=223)  (actual
time=118764.67..340333.78 rows=57076 loops=l)
                ->  Hash Join  (cost=16616.14..19217.14 rows=4Ol33 width=214)  (actual
time=118761.38..258978.8l row=57076 loops=1)
                    ->  Merge Join  (cost=16615.07..18413.42 rows=40133 width=205)
                                    (actual time=118758.74..187180.55 rows=57076 loops=i)
                        ->  Index Scan using grf_grf_id_idx on giraffes  (cost=O.O0..1115.61
rows=53874 width=8)
                                                                         (actual
time=2.37..6802.38 rows=57077 loops=l)
                        ->  Sort  (cost=l66l5.07..16615.07 rows=18554 width=197)  (actual
time=118755.11..120261.06 rows=59416 loops=l)
                            ->  Hash Join  (cost=8126.08..14152.54 rows=18554 width=197)
                                           (actual time=50615.72..l09853.7l rows=16310 loops=1)
                                ->  Hash Join  (cost=8124.39..12690.30 rows=24907 width=179)
                                               (actual time=50607.36..86868.58 rows=iSBiS loops=1)
                                    ->  Hash Join  (cost=249.26..2375.23 rows=24907 width=131)
                                                   (actual time=23476.42..35107.80 rows=16310 loops=l)
                                        -> Nested Loop  (cost=248.2l..1938.31 rows=24907 width=118)
                                                        (actual time=23474.70..28155.13 rows=16310 loops=1)
                                            ->  Seq Scan on zebras  (cost=0.00..l.0l rows=l width=14)
                                                                    (actual time=O.64..0.72 rows=1 ioops=1)
                                            ->  Materialize  (cost=1688.23..l688.23 rows=24907 width=104)
                                                             (actual time=23473.77..23834.26 rows=16310 loops=l)
                                                    ->  Hash Join  (cost=248.21..1688.23 rows=24907 width=lO4)
                                                                  (actual time=1199.26..23059.92 rows=16310 loops=l)
                                                        ->  Seq Scan on frogs  (cost=0.00..755.07 rows=24907 width=83)
                                                                               (actual time=0.53..4629.58 rows=25702
loops=l)
                                                        ->  Hash  (cost=225.57..225.57 rows=9057 width=21)
                                                                  (actual time=1198.0l..1198.01 rows=0 loops=1)
                                                            ->  Seq Scan on tigers  (cost=0.00..225.57 rows=9057
width=21)
                                                                                    (actual time=0.39..892.67 rows=9927

loops=1)
                                        ->  Hash  (cost=l.O4..1.-4 rows=4 width=13)  (actual time=l.07..1.07
rows=0 loops=1)
                                            ->  Seq Scan on deers  (cost=0.0O..1.04 rows=4 width=13)
                                                                   (actual time=0.64..0.95 rows=4 loops=1)
                                    ->  Hash  (cost=4955.28..4955.28 rows=91528 width=48)
                                              (actual tlne=27O40.82..27040.82 rows=0 loops=1)
                                        ->  Seq Scan on warthogs  (cost=0.00..4955.28 rows=91528 width=48)
                                                                  (actual time=3.92..24031.27 rows=91528
loops=1)
                                ->  Hash  (cost=1.55..1.55 rows=55 width=18)  (actual time=7.l3..7.13
rows=0 loops=1)
                                    ->  Seq Scan on monkeys  (cost=0.00..l.55 rows=55 width=18)
                                                             (actual time=0.64..5.38 rows=55 loops=1)
                    ->  Hash  (cost=l.O5..1.05 rows=S width=9)  (actual time=1.16..l.l6 rows=0
loops=1)
                        ->  Seq Scan on worms  (cost=0.00..1.05 rows=S width=9)  (actual
time=0.65..1.00 rows=5 loops=1)
                ->  Hash  (cost=1.16..1.16 rows=16 width=9) (actual time=l.86..1.86 rows=0
loops=1)
                    ->  Seq Scan on lions  (cost=0.00..l.16 rows=16 width=9)  (actual
time=0.lO..1.36 rows=16 loops=1)
            ->  Hash    (cost=1.06..1.06 rows=6 width=17)  (actual time=1.35..1.35 rows=0
loops=1)
                ->  Seq Scan on dogs  (cost=0.00..1.06 rows=6 width=17)  (actual
time=0.65..1.16 rows=6 loops=l)
        ->  Hash            (cost=1.07..1.07 rows=3 width=9)  (actual time=1.23..1.23 rows=0
loops=1)
            ->  Seq Scan on parrots  (cost=0.00..1.07 rows=3 width=9)  (actual
time=0.69..1.13 rows=3 loops=1)
    ->  Hash    (cost=l.08..1.08 rows=8 width=17) (actual time=0.98..0.98 rows=0
loops=1)
        ->    Seq Scan on rhinos  (cost=0.00..1.08 rows=8 width=17)  (actual
time=0.10..0.73 rows=8 loops=1)

Total runtime:  58l341.00 msec

David Shadovitz wrote:

> Well, now that I have the plan for my slow-running query, what do I do?  Where
> should I focus my attention?

Briefly looking over the plan and seeing the estimated v/s actual row mismatch,I
can suggest you following.

1. Vacuum(full) the database. Probably you have already done it.
2. Raise statistics_target to 500 or more and reanalyze the table(s) in question.
3. Set enable_hash_join to false, before running the query and see if it helps.

  HTH

  Shridhar

David Shadovitz <david@shadovitz.com> writes:
> Well, now that I have the plan for my slow-running query, what do I
> do?

This is not very informative when you didn't show us the query nor
the table schemas (column datatypes and the existence of indexes
are the important parts).  I have a feeling that you might be well
advised to fold the multiple tables into one "animals" table, but
there's not enough info here to make that recommendation for sure.

BTW, what did you do with this, print and OCR it?  It's full of the
most bizarre typos ... mostly "l" for "1", but others too ...

            regards, tom lane

> This is not very informative when you didn't show us the query nor
> the table schemas..

> BTW, what did you do with this, print and OCR it?

Tom,

I work in a classified environment, so I had to sanitize the query plan, print
it, and OCR it.  I spent a lot of time fixing typos, but I guess at midnight my
eyes missed some.  This hassle is why I posted neither the query nor the
schema.  The database is normalized, though, but my use of animal names of
couse masks this.

If you think that you or anyone else would invest the time, I could post more
info.

I will also try Shridhar's suggestions on statistics_target and
enable_hash_join.

Thanks.
-David

David Shadovitz <david@shadovitz.com> writes:
> If you think that you or anyone else would invest the time, I could post more
> info.

I doubt you will get any useful help if you don't post more info.

> I will also try Shridhar's suggestions on statistics_target and
> enable_hash_join.

It seemed to me that the row estimates were not so far off that I would
call it a statistical failure; you can try increasing the stats target
but I'm not hopeful about that.  My guess is that you will have to look
to revising either the query or the whole database structure (ie,
merging tables).  We'll need the info I asked for before we can make
any recommendations, though.

            regards, tom lane

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