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

From Sebastián Baioni
Subject Inner Join of the same table
Date
Msg-id 20060815143812.69494.qmail@web36109.mail.mud.yahoo.com
Whole thread Raw
Responses Re: Inner Join of the same table
List pgsql-performance
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

Instrumentos musicalesSebastián Baioni Ofertas náuticas


Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
Probalo ya!

pgsql-performance by date:

Previous
From: "Bucky Jordan"
Date:
Subject: Re: Dell PowerEdge 2950 performance
Next
From: "Jim C. Nasby"
Date:
Subject: Re: Beginner optimization questions, esp. regarding Tsearch2