X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 3652B9FB274
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 15 Aug 2006 11:45:10 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 91386-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 15 Aug 2006 14:44:57 +0000 (UTC)
X-Greylist: delayed 00:06:40.617611 by SQLgrey-
Received: from web36109.mail.mud.yahoo.com (web36109.mail.mud.yahoo.com
	[66.163.179.223])
	by postgresql.org (Postfix) with SMTP id D83459FB269
	for <pgsql-performance@postgresql.org>;
	Tue, 15 Aug 2006 11:44:55 -0300 (ADT)
Received: (qmail 69496 invoked by uid 60001); 15 Aug 2006 14:38:12 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar;
	h=Message-ID:Received:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=NODwgyH/fecdOv9unjRIxt+L2B/NWHRkCUDO3ydLhIni8SVcCJq28O5ed/DoceHBod4qt1Gfa4uQwK8zga99MxUiKBd4UMhv/TE3nyaUYgFy90118LmFPPAzblpbEiuem+71Et12uh6Jow31Jaoe/fF2jfng2sG2nDHunF1Gz88=
	;
Message-ID: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com>
Received: from [200.49.144.50] by web36109.mail.mud.yahoo.com via HTTP;
	Tue, 15 Aug 2006 14:38:12 GMT
Date: Tue, 15 Aug 2006 14:38:12 +0000 (GMT)
From: =?iso-8859-1?q?Sebasti=E1n=20Baioni?= <sebaioni-postgresql@yahoo.com.ar>
Reply-To: sebaioni-postgresql@yahoo.com.ar
Subject: Inner Join of the same table
To: Performance PostgreSQL <pgsql-performance@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-455872028-1155652692=:68034"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200608/221
X-Sequence-Number: 20406

--0-455872028-1155652692=:68034
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

  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! 
--0-455872028-1155652692=:68034
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

  Hello, I'm migrating from MS SQL Server to PostgreSQL 8.1 and I have a serious problem:<br>Table: APORTES - Rows: 9,000,000 (9 million)<br>*cuiT (char 11)<br>*cuiL (char 11)<br>*PERI (char 6)<br>FAMI (numeric 6)<br><br>I need all the <span style="font-weight: bold;">cuiL</span>s whose max(<span style="font-weight: bold;">PERI</span>) are from a <span style="font-weight: bold;">cuiT</span>, and the Max(<span style="font-weight: bold;">FAMI</span>) of those <span style="font-weight: bold;">cuiL</span>s, so the sentence is:<br><br><span style="font-family: courier;"><span style="color: rgb(0, 0, 191);">SELECT DISTINCT</span> T.cuiT, T.cuiL. U.MAXPERI, U.MAXFAMI</span><br style="font-family: courier;"><span style="font-family: courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 191);">FROM</span> <span style="font-weight: bold;">APORTES </span>T</span><br style="font-family: courier;"><span style="font-family:
 courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 191);">INNER </span><span style="color: rgb(139, 139, 139);">JOIN</span></span><br style="font-family: courier;"><span style="font-family: courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<span style="color: rgb(0, 0, 191);">SELECT</span> cuiL, <span style="color: rgb(0, 0, 191);">MAX</span>(PERI) <span style="color: rgb(0, 0, 191);">AS </span>MAXPERI,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 191);">MAX</span>(FAMI) <span style="color: rgb(0, 0, 191);">AS </span>MAXFAMI</span><br style="font-family: courier;"><span style="font-family: courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 191);">FROM </span><span style="font-weight: bold;">APORTES</span></span><br style="font-family: courier;"><span style="font-family: courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:
 rgb(0, 0, 191);">GROUP BY</span> cuiL) <span style="color: rgb(0, 0, 191);">AS </span>U</span><br style="font-family: courier;"><span style="font-family: courier;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color: rgb(0, 0, 191);">ON </span>T.cuiL = U.cuiL <span style="color: rgb(139, 139, 139);">AND </span>T.PERI=U.MAXPERI</span><br style="font-family: courier;"><span style="font-family: courier;"><span style="color: rgb(0, 0, 191);">WHERE </span>T.cuiT=<span style="color: rgb(192, 0, 0);">'12345678901'</span></span><br><br>In MS SQL Server it lasts 1minute, in PostgreSQL for Windows it lasts 40minutes and in PostgreSQL for Linux (FreeBSD) it lasts 20minuts.<br><br>Do you know if there is any way to tune the server or optimize this sentence?<br><br>Thanks<br>&nbsp;&nbsp;&nbsp;&nbsp; Sebastián Baioni<BR><BR><a href="http://www.instrumento-musical.com.ar/" target="_blank">
  <font size="2">
  <IMG alt="Instrumentos musicales" src="http://smileys.smileycentral.com/cat/4_3_4.gif" border=0 target="_blank" width="30" height="30"></font></a><STRONG>Sebastián Baioni<font size="2">
  <a href="http://www.ofertas-nauticas.com.ar" target="_blank">
  <img border="0" src="http://personales.ciudad.com.ar/baioni/imagenes/Firmas/mdrisa.gif" alt="Ofertas náuticas" width="30" height="18"></a></font></STRONG><p>&#32;
		<hr size=1> 
<b>Preguntá. Respondé. Descubrí.</b><br> 
Todo lo que querías saber, y lo que ni imaginabas,<br> 
está en <b>Yahoo! Respuestas</b> (Beta).<br> 
<b><a href="http://ar.answers.yahoo.com">Probalo ya!</a></b> 
--0-455872028-1155652692=:68034--