Re: Inner Join of the same table

Hi Nark, thanks for your answer.

It's expected to return 1,720 rows (of 80,471 that match with condition WHERE
T.cuiT='12345678901')

We have indexes by :
uesapt000: cuiT, cuiL, PERI;
uesapt001: cuiL, PERI;
uesapt002: cuiT, PERI;

We usually make a vacuum analyze and reindex of every table, and we are running 8.0 and 8.1 for
windows and 7.4 for Linux.

Here is the EXPLAIN:
QUERY PLAN
 1 Unique  (cost=37478647.41..37478650.53 rows=312 width=62)
 2  ->  Sort  (cost=37478647.41..37478648.19 rows=312 width=62)
 3        Sort Key: t.cuiT, t.cuiL, u.maxperi
 4        ->  Merge Join  (cost=128944.78..37478634.48 rows=312 width=62)
 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)
 8                    ->  GroupAggregate  (cost=0.00..37348395.05 rows=3951 width=25)
 9                          ->  Index Scan using uesapt001 on APORTES  (cost=0.00..37301678.64
rows=9339331 width=25)
10              ->  Sort  (cost=128944.78..129100.44 rows=62263 width=40)
11                    Sort Key: t.cuiL
12                    ->  Index Scan using uesapt002 on APORTES t  (cost=0.00..122643.90
rows=62263 width=40)
13                          Index Cond: (cuiT = '30701965554'::bpchar)

Thanks
     Sebastián Baioni

 --- Mark Lewis <mark.lewis@mir3.com> escribió:

> Can you provide an EXPLAIN ANALYZE of the query in PG?  Have you
> analyzed the PG database?  How many rows is this query expected to
> return?  Which version of PG are you running?  What indexes have you
> defined?
>
> -- Mark
>
> On Tue, 2006-08-15 at 14:38 +0000, Sebastián Baioni wrote:
> > Hello, I'm migrating from MS SQL Server to PostgreSQL 8.1 and I have a
> > serious problem:
> > Table: APORTES - Rows: 9,000,000 (9 million)
> > *cuiT (char 11)
> > *cuiL (char 11)
> > *PERI (char 6)
> > FAMI (numeric 6)
> >
> > I need all the cuiLs whose max(PERI) are from a cuiT, and the Max
> > (FAMI) of those cuiLs, so the sentence is:
> >
> > SELECT DISTINCT T.cuiT, T.cuiL. U.MAXPERI, U.MAXFAMI
> >        FROM APORTES T
> >        INNER JOIN
> >        (SELECT cuiL, MAX(PERI) AS MAXPERI,
> >                MAX(FAMI) AS MAXFAMI
> >         FROM APORTES
> >         GROUP BY cuiL) AS U
> >        ON T.cuiL = U.cuiL AND T.PERI=U.MAXPERI
> > WHERE T.cuiT='12345678901'
> >
> > In MS SQL Server it lasts 1minute, in PostgreSQL for Windows it lasts
> > 40minutes and in PostgreSQL for Linux (FreeBSD) it lasts 20minuts.
> >
> > Do you know if there is any way to tune the server or optimize this
> > sentence?
> >
> > 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