Message-ID: <4305FD8B.9090803@arbash-meinel.com>
Date: Fri, 19 Aug 2005 10:40:59 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: Diego de Lima <diego@sistemica.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: LEFT JOIN ON vs. LEFT JOIN USING performance
References: <002a01c5a4d1$d36f6420$3dc8a8c0@diretoria02>
In-Reply-To: <002a01c5a4d1$d36f6420$3dc8a8c0@diretoria02>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigF91D8A06A6C8BD5262A8C6FA"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigF91D8A06A6C8BD5262A8C6FA
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Diego de Lima wrote:
> Hi list,
> =20
> I=C2=B4m using Pg 8.0.3 on Linux FC2.
> =20
> This question may have a very simple answer (I hope), but I=C2=B4m havi=
ng
> lots of trouble solving it, and I counldn=C2=B4t find any other post ab=
out it
> or anything in the pg docs.
> =20
> 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.
> =20
> 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.
> =20
> So my question is: is there a way to make LEFT JOIN ON uses the same
> plan of LEFT JOIN USING?
> =20
> Thanks,
> =20
> Diego de Lima
> =20
> =20

I'm guessing that ON/USING isn't the specific problem. It's probably
more an issue of how the planner is deciding to do the joins (merge
join, hash join, nested loop, etc.)

Can you send the results of EXPLAIN ANALYZE <your query>?

Also, any sort of join where you have to join against millions of rows
is going to be slow. I don't know your specific design, but likely you
could change the design to be more selective at an earlier level, which
means that you can cut the size of the join by a lot. If you post you
query, a lot of times people here can help optimize your query. (But
make sure to explain what you are trying to do, so the optimizations
make sense.)

John
=3D:->


--------------enigF91D8A06A6C8BD5262A8C6FA
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDBf2LJdeBCYSNAAMRArdNAJ4msAEWqcEZRGvoYoGanRvDpL8YiACguaIY
oA3YYom0rtxPpTetl5SXeMM=
=vuUc
-----END PGP SIGNATURE-----

--------------enigF91D8A06A6C8BD5262A8C6FA--