[PERFORM] Inner Join of the same table

[Sebastián Baioni]

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

Sebastián Baioni

---

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