From: Sebastián Baioni <sebaioni-postgresql@yahoo.com.ar>
To: Performance PostgreSQL <pgsql-performance@postgresql.org>
Subject: Inner Join of the same table
Date: Tue, 15 Aug 2006 14:38:12 +0000 (GMT)
Message-ID: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sebaioni-postgresql@yahoo.com.ar
Subject: Re: Inner Join of the same table
In-Reply-To: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox