Skip site navigation (1) Skip section navigation (2)

Query plan - now what?

From: David Shadovitz <david(at)shadovitz(dot)com>
To: "'pgsql-performance(at)postgresql(dot)org'" <pgsql-performance(at)postgresql(dot)org>
Subject: Query plan - now what?
Date: 2003-12-12 08:18:12
Message-ID: (view raw or flat)
Lists: pgsql-performance
Well, now that I have the plan for my slow-running query, what do I do?  Where 
should I focus my attention?

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


pgsql-performance by date

Next:From: Shridhar DaithankarDate: 2003-12-12 09:14:05
Subject: Re: Query plan - now what?
Previous:From: Tomasz MyrtaDate: 2003-12-12 07:46:37
Subject: Re: Measuring execution time for sql called from PL/pgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group