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