Re: Inner Join of the same table

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
>
> 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!