Re: Inner Join of the same table

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(at)sss(dot)pgh(dot)pa(dot)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