DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar;
	h=Message-ID:Received:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=zAobWsLpxakoE1i83ujKYTLFu6dTnVvkJwAZPU3Tqxpr9tHg34M+DcN9yVBZKO1lZiSqahmoCuQISItpy8SmGD0qmRo8pcbS/7B3t3J4UTp9qKt8Tr52S4hf5we95L2+OoaJJThVAMRMx8sopnTNr5liRe5F4dXoEjXYmam47d0=
	;
Message-ID: <20060816182705.96338.qmail@web36104.mail.mud.yahoo.com>
Date: Wed, 16 Aug 2006 18:27:05 +0000 (GMT)
From: =?iso-8859-1?q?Sebasti=E1n=20Baioni?= <sebaioni-postgresql@yahoo.com.ar>
Reply-To: sebaioni-postgresql@yahoo.com.ar
Subject: Re: Inner Join of the same table 
To: Performance PostgreSQL <pgsql-performance@postgresql.org>
In-Reply-To: <20384.1155676382@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I had enable_seqscan turned OFF; With enable_seqscan turned ON it takes only 6 minutes to complete
the query and not 44minuts like it did with enable_seqscan turned OFF.  THANKS A LOT!
It's still much more slower than MS SQL server but now it has acceptable times.

     Sebastián Baioni

 --- Tom Lane <tgl@sss.pgh.pa.us> escribió:
> Given the relatively small estimated number of group rows, I'd have
> expected the thing to use a seqscan and HashAggregate for this part.
> Do you have enable_hashagg turned off for some reason?  Or enable_seqscan?
> 
> 			regards, tom lane
> > Hello Jim, we can't use the Where cuiT='12345678901' in the subquery because we need max(cuiL)
independently of that cuiT:
> > cuiT cuiL PERI    FAMI
> > 1    a    200608  0
> > 1    a    200601  2
> > 1    b    200607  3
> > 1    c    200605  4
> > 2    a    200605  9
> > 2    c    200604  4
> > 2    b    200608  1
> > We need:
> > where cuiT = '1'
> > cuiT cuiL PERI    FAMI
> > 1    a    200608  9
> > 1    c    200605  4
> > If we place the Where cuiT = '1' in the subquery we couldn't get the max(FAMI) of cuiL a = 9
and we couldn't know if that PERI is the max(PERI) of that cuiL independently of that cuiT.
> > 
> > Here is the explain analyze with PG 8.0 for Windows:
> > Explain Analyze
> > SELECT DISTINCT T.cuiT,T.cuiL, U.MAXPERI AS  ULT_APORTE_O_DDJJ
> > 		FROM APORTES AS T
> > 		INNER JOIN
> > 		(
> > 		SELECT cuiL, MAX(PERI) AS MAXPERI
> > 		FROM APORTES
> > 		GROUP BY cuiL
> > 		) AS U ON T.cuiL=U.cuiL AND T.PERI=U.MAXPERI
> > WHERE T.cuiT='12345678901'
> > order by T.cuiT, T.cuiL, U.MAXPERI;
> > 
> > QUERY PLAN
> > 1 Unique  (cost=37478647.41..37478650.53 rows=312 width=62) (actual
time=2677209.000..2677520.000
> > rows=1720 loops=1)
> >  2  ->  Sort  (cost=37478647.41..37478648.19 rows=312 width=62) (actual
time=2677209.000..2677260.000 rows=3394 loops=1)
> >  3        Sort Key: t.cuiT, t.cuiL, u.maxperi
> >  4        ->  Merge Join  (cost=128944.78..37478634.48 rows=312 width=62) (actual
time=74978.000..2677009.000 rows=3394 loops=1)
> >  5              Merge Cond: ("outer".cuiL = "inner".cuiL)
> >  6              Join Filter: (("inner".peri)::text = "outer".maxperi)
> >  7              ->  Subquery Scan u  (cost=0.00..37348434.56 rows=3951 width=47) (actual
time=130.000..2634923.000 rows=254576 loops=1)
> >  8                    ->  GroupAggregate  (cost=0.00..37348395.05 rows=3951 width=25) (actual
time=130.000..2629617.000 rows=254576 loops=1)
> >  9                          ->  Index Scan using uesapt001 on APORTES  (cost=0.00..37301678.64
rows=9339331 width=25) (actual time=110.000..2520690.000 rows=9335892 loops=1)
> > 10              ->  Sort  (cost=128944.78..129100.44 rows=62263 width=40) (actual
time=30684.000..36838.000 rows=80471 loops=1)
> > 11                    Sort Key: t.cuiL
> > 12                    ->  Index Scan using uesapt002 on APORTES t  (cost=0.00..122643.90
rows=62263 width=40) (actual time=170.000..25566.000 rows=80471 loops=1)
> > 13                          Index Cond: (cuiT = '12345678901'::bpchar)
> > Total runtime: 2677640.000 ms
> > 
> > Thanks
> >      Sebastián Baioni


__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas