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

Re: Please help with this explain analyse...

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bricklen Anderson <BAnderson(at)PresiNET(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Please help with this explain analyse...
Date: 2005-11-29 02:16:52
Message-ID: (view raw or flat)
Lists: pgsql-performance
I restored my db but haven't run the analyse... That was the problem.


"Merge Left Join  (cost=2273.54..2290.19 rows=228 width=816) (actual 
time=2098.257..2444.472 rows=8335 loops=1)"
"  Merge Cond: (("outer".rrnum)::text = "inner"."?column8?")"
"  ->  Merge Join  (cost=2131.25..2141.31 rows=228 width=744) (actual 
time=2037.953..2251.289 rows=8335 loops=1)"
"        Merge Cond: ("outer"."?column31?" = "inner"."?column77?")"
"        ->  Sort  (cost=1975.03..1975.60 rows=228 width=235) (actual 
time=1798.556..1811.828 rows=8335 loops=1)"
"              Sort Key: (cr.crrrnum)::text"
"              ->  Hash Join  (cost=1451.41..1966.10 rows=228 width=235) 
(actual time=267.751..515.396 rows=8335 loops=1)"
"                    Hash Cond: ("outer".crnum = "inner".cscrnum)"
"                    ->  Seq Scan on cr  (cost=0.00..489.77 rows=4529 
width=101) (actual time=0.077..97.615 rows=8335 loops=1)"
"                          Filter: (((crypnum)::text = 'M'::text) AND 
((crdate + '00:00:00'::interval) <= '2005-01-28 00:00:00'::timestamp 
without time zone))"
"                    ->  Hash  (cost=1449.70..1449.70 rows=684 
width=134) (actual time=267.568..267.568 rows=13587 loops=1)"
"                          ->  Nested Loop  (cost=20.59..1449.70 
rows=684 width=134) (actual time=33.099..178.524 rows=13587 loops=1)"
"                                ->  Seq Scan on gl  (cost=0.00..5.21 
rows=2 width=91) (actual time=0.021..0.357 rows=1 loops=1)"
"                                      Filter: (glsoctrl = 1)"
"                                ->  Bitmap Heap Scan on cs  
(cost=20.59..684.42 rows=3026 width=43) (actual time=33.047..115.151 
rows=13587 loops=1)"
"                                      Recheck Cond: ((cs.csglnum)::text 
= ("outer".glnum)::text)"
"                                      Filter: ('M'::text = 
"                                      ->  Bitmap Index Scan on 
cs_gl_fk  (cost=0.00..20.59 rows=3026 width=0) (actual 
time=32.475..32.475 rows=13587 loops=1)"
"                                            Index Cond: 
((cs.csglnum)::text = ("outer".glnum)::text)"
"        ->  Sort  (cost=156.22..159.65 rows=1372 width=509) (actual 
time=239.315..254.024 rows=8974 loops=1)"
"              Sort Key: (rr.rrnum)::text"
"              ->  Seq Scan on rr  (cost=0.00..84.72 rows=1372 
width=509) (actual time=0.055..33.564 rows=1372 loops=1)"
"  ->  Sort  (cost=142.29..144.55 rows=903 width=72) (actual 
time=60.246..74.813 rows=8932 loops=1)"
"        Sort Key: (yr.yrref)::text"
"        ->  Bitmap Heap Scan on yr  (cost=16.42..97.96 rows=903 
width=72) (actual time=8.513..13.587 rows=1154 loops=1)"
"              Recheck Cond: (((yryotype)::text = 'Client'::text) AND 
((yryonum)::text = 'Comptabilite.Recevable.Regroupement'::text))"
"              ->  Bitmap Index Scan on yr_idx1  (cost=0.00..16.42 
rows=903 width=0) (actual time=8.471..8.471 rows=1154 loops=1)"
"                    Index Cond: (((yryotype)::text = 'Client'::text) 
AND ((yryonum)::text = 'Comptabilite.Recevable.Regroupement'::text))"
"Total runtime: 2466.197 ms"

>Bricklen Anderson <BAnderson(at)PresiNET(dot)com> writes:
>>Your loops are what is causing the time spent.
>>eg. "actual time=0.127..17.379 rows=1154 loops=8335)" ==
>>8335*(17.379-0.127)/1000=>143 secs (if my math is correct).
>As for where the problem is, I think it's the horrid misestimate of the
>number of matching rows in cs_pk:
>>>"                    ->  Index Scan using cs_pk on cs  (cost=0.00..12.83 
>>>rows=2 width=144) (actual time=0.087..444.999 rows=13587 loops=1)"
>>>"                          Index Cond: (('M'::text = (cs.csypnum)::text) 
>>>AND ((cs.csglnum)::text = ("outer".glnum)::text))"
>Has that table been ANALYZEd recently?  How about the gl table?
>			regards, tom lane

In response to

pgsql-performance by date

Next:From: Brendan DuddridgeDate: 2005-11-29 03:19:41
Subject: Re: Hardware/OS recommendations for large databases (
Previous:From: Tom LaneDate: 2005-11-29 00:00:30
Subject: Re: Please help with this explain analyse...

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