pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery plan - now what?
6+ messages / 3 participants
[nested] [flat]
* Query plan - now what?
@ 2003-12-12 08:18 David Shadovitz <david@shadovitz.com>
2003-12-12 09:14 ` Re: Query plan - now what? Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-12-12 15:33 ` Re: Query plan - now what? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: David Shadovitz @ 2003-12-12 08:18 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan - now what?
2003-12-12 08:18 Query plan - now what? David Shadovitz <david@shadovitz.com>
@ 2003-12-12 09:14 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 6+ messages in thread
From: Shridhar Daithankar @ 2003-12-12 09:14 UTC (permalink / raw)
To: david@shadovitz.com <david@shadovitz.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan - now what?
2003-12-12 08:18 Query plan - now what? David Shadovitz <david@shadovitz.com>
@ 2003-12-12 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-12-12 15:33 UTC (permalink / raw)
To: david@shadovitz.com <david@shadovitz.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan - now what?
@ 2003-12-12 15:46 David Shadovitz <david@shadovitz.com>
2003-12-12 16:52 ` Re: Query plan - now what? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David Shadovitz @ 2003-12-12 15:46 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan - now what?
2003-12-12 15:46 Re: Query plan - now what? David Shadovitz <david@shadovitz.com>
@ 2003-12-12 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-12-12 16:52 UTC (permalink / raw)
To: david@shadovitz.com <david@shadovitz.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query plan - now what?
@ 2003-12-14 07:21 David Shadovitz <david@shadovitz.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Shadovitz @ 2003-12-14 07:21 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
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);
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-12-14 07:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-12 08:18 Query plan - now what? David Shadovitz <david@shadovitz.com>
2003-12-12 09:14 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-12-12 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-12 15:46 Re: Query plan - now what? David Shadovitz <david@shadovitz.com>
2003-12-12 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-14 07:21 Re: Query plan - now what? David Shadovitz <david@shadovitz.com>
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