X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 415629DCC0C for ; Mon, 28 Nov 2005 19:37:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73445-04 for ; Mon, 28 Nov 2005 19:37:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from relais.videotron.ca (relais.videotron.ca [24.201.245.36]) by postgresql.org (Postfix) with ESMTP id A56989DCAB2 for ; Mon, 28 Nov 2005 19:37:10 -0400 (AST) Received: from [192.168.1.100] ([24.202.23.128]) by VL-MO-MR002.ip.videotron.ca (Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005)) with ESMTP id <0IQO009M1UY0MRC0@VL-MO-MR002.ip.videotron.ca> for pgsql-performance@postgresql.org; Mon, 28 Nov 2005 18:37:13 -0500 (EST) Date: Mon, 28 Nov 2005 18:40:59 -0500 From: David Gagnon Subject: Please help with this explain analyse... To: pgsql-performance@postgresql.org Message-id: <438B958B.3010602@siunik.com> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.343 required=5 tests=[AWL=0.343] X-Spam-Score: 0.343 X-Spam-Level: X-Archive-Number: 200511/541 X-Sequence-Number: 15798 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"