X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F136152C3A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 19 Aug 2005 12:22:53 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81433-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 19 Aug 2005 15:22:49 +0000 (GMT)
Received: from birao.terra.com.br (birao.terra.com.br [200.176.10.197])
	by svr1.postgresql.org (Postfix) with ESMTP id 62A1B52ACB
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Aug 2005 12:22:48 -0300 (ADT)
Received: from donga.terra.com.br (donga.terra.com.br [200.176.10.10])
	by birao.terra.com.br (Postfix) with ESMTP id 22BA0108C0AD
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Aug 2005 12:22:47 -0300 (BRT)
X-Terra-Karma: -2%
X-Terra-Hash: 4cf10e32f89f4380a51ac919858cb02c
Received-SPF: none (donga.terra.com.br: 200.176.10.10 is neither permitted nor
	denied by domain of sistemica.info) client-ip=200.176.10.10;
	envelope-from=diego@sistemica.info; helo=modem.sistemica;
Received: from modem.sistemica
 (201-25-44-253.paemt704.dsl.brasiltelecom.net.br
	[201.25.44.253]) (authenticated user sistemic)
	by donga.terra.com.br (Postfix) with ESMTP id 952BF9C4071
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Aug 2005 12:22:46 -0300 (BRT)
Received: from diretoria02 (unknown [192.168.200.61])
	by modem.sistemica (Postfix) with SMTP id 0FA2813F610
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Aug 2005 12:24:00 -0300 (BRT)
Message-ID: <002a01c5a4d1$d36f6420$3dc8a8c0@diretoria02>
Reply-To: "Diego de Lima" <diego@sistemica.info>
From: "Diego de Lima" <diego@sistemica.info>
To: <pgsql-performance@postgresql.org>
Subject: LEFT JOIN ON vs. LEFT JOIN USING performance
Date: Fri, 19 Aug 2005 12:22:35 -0300
Organization: Sistemica.info
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0027_01C5A4B8.AE118A50"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.146 required=5 tests=[FORGED_RCVD_HELO=0.05,
	HTML_50_60=0.095, HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200508/266
X-Sequence-Number: 14013

This is a multi-part message in MIME format.

------=_NextPart_000_0027_01C5A4B8.AE118A50
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi list,

I=B4m using Pg 8.0.3 on Linux FC2.

This question may have a very simple answer (I hope), but I=B4m having =
lots of trouble solving it, and I counldn=B4t find any other post about =
it or anything in the pg docs.

I have some very complex select statements on 4 million rows tables. =
When using LEFT JOIN ON, some select statements takes about 2 minutes. =
When I write exactly the same statement but with LEFT JOIN USING, it =
takes only 1 minute. Comparing to Oracle, the same statement takes 1 =
minute also, but with LEFT JOIN ON.

Sometimes tables have the same column names and I can use LEFT JOIN =
USING, but in some other cases I MUST use LEFT JOIN ON, because the =
tables have different column names.

So my question is: is there a way to make LEFT JOIN ON uses the same =
plan of LEFT JOIN USING?

Thanks,

Diego de Lima


------=_NextPart_000_0027_01C5A4B8.AE118A50
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi list,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I=B4m using Pg 8.0.3 on Linux =
FC2.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>This question may have a very simple =
answer (I=20
hope), but I=B4m having lots of trouble solving it, and I counldn=B4t =
find any other=20
post about it or anything in the pg docs.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have some very complex select =
statements on 4=20
million rows tables.&nbsp;When using&nbsp;LEFT JOIN ON, some select =
statements=20
takes about 2 minutes. When I write exactly the same statement but with =
LEFT=20
JOIN USING, it takes&nbsp;only 1 minute. Comparing to Oracle, the same =
statement=20
takes 1 minute also, but&nbsp;with LEFT JOIN ON.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Sometimes&nbsp;tables&nbsp;have the =
same column=20
names and I can use LEFT JOIN USING, but in some other cases I MUST use =
LEFT=20
JOIN ON, because the tables have different column names.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>So my question is: is there a way to =
make LEFT JOIN=20
ON uses the same plan of LEFT JOIN USING?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Diego de Lima</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0027_01C5A4B8.AE118A50--