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

Please help with this explain analyse...

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Please help with this explain analyse...
Date: 2005-11-28 23:40:59
Message-ID: 438B958B.3010602@siunik.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

  I don't understand why this request take so long.  Maybe I read the 
analyse correctly but It seem that the first line(Nested Loop Left Join  
...) take all the time.  But I don't understand where the performance 
problem is ???  All the time is passed in the first line ...

Thanks for your help!

/David


explain analyse   SELECT *

                    FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND 
CR.CRYPNUM = CS.CSYPNUM
                    INNER JOIN GL ON CS.CSGLNUM = GL.GLNUM AND 
GL.GLSOCTRL = 1
                    INNER JOIN RR ON CR.CRRRNUM = RR.RRNUM
                    LEFT OUTER JOIN YR ON YR.YRYOTYPE = 'Client' AND 
YR.YRYONUM = 'Comptabilite.Recevable.Regroupement'  AND YR.YRREF = RR.RRNUM
                    WHERE CRYPNUM = 'M'
                         AND CRDATE  + INTERVAL '0 days' <= '2005-01-28'


"Nested Loop Left Join  (cost=0.00..42.12 rows=1 width=8143) (actual 
time=15.254..200198.524 rows=8335 loops=1)"
"  Join Filter: (("inner".yrref)::text = ("outer".rrnum)::text)"
"  ->  Nested Loop  (cost=0.00..36.12 rows=1 width=7217) (actual 
time=0.441..2719.821 rows=8335 loops=1)"
"        ->  Nested Loop  (cost=0.00..30.12 rows=1 width=1580) (actual 
time=0.242..1837.413 rows=8335 loops=1)"
"              ->  Nested Loop  (cost=0.00..18.07 rows=2 width=752) 
(actual time=0.145..548.607 rows=13587 loops=1)"
"                    ->  Seq Scan on gl  (cost=0.00..5.21 rows=1 
width=608) (actual time=0.036..0.617 rows=1 loops=1)"
"                          Filter: (glsoctrl = 1)"
"                    ->  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))"
"              ->  Index Scan using cr_pk on cr  (cost=0.00..6.02 rows=1 
width=828) (actual time=0.073..0.077 rows=1 loops=13587)"
"                    Index Cond: (((cr.crypnum)::text = 'M'::text) AND 
(cr.crnum = "outer".cscrnum))"
"                    Filter: ((crdate + '00:00:00'::interval) <= 
'2005-01-28 00:00:00'::timestamp without time zone)"
"        ->  Index Scan using rr_pk on rr  (cost=0.00..5.99 rows=1 
width=5637) (actual time=0.062..0.069 rows=1 loops=8335)"
"              Index Cond: (("outer".crrrnum)::text = (rr.rrnum)::text)"
"  ->  Index Scan using yr_idx1 on yr  (cost=0.00..5.99 rows=1 
width=926) (actual time=0.127..17.379 rows=1154 loops=8335)"
"        Index Cond: (((yryotype)::text = 'Client'::text) AND 
((yryonum)::text = 'Comptabilite.Recevable.Regroupement'::text))"
"Total runtime: 200235.732 ms"


Responses

pgsql-performance by date

Next:From: Bricklen AndersonDate: 2005-11-28 23:46:14
Subject: Re: Please help with this explain analyse...
Previous:From: Brendan DuddridgeDate: 2005-11-28 23:19:34
Subject: Re: Hardware/OS recommendations for large databases (

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