Re: Inner Join of the same table - Mailing list pgsql-performance

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