From: Michael Ansley <michael.ansley@intec.co.za>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Optimizations
Date: Thu, 3 Dec 1998 14:14:23 +0200
Message-ID: <01BE1EC7.3A7A4790@MANSLEY> (raw)
Please would somebody tell me whether or not the optimizer sufficiently optimizes a cross join like this:
SELECT *
FROM table1 t1, table2, t2
WHERE t1.field1 = t2.field4
to
SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON t1.field1 = t2.field4
As I understand it, both queries will always return the same results, however, the second query will execute substantially faster, particularly as the tables involved increase in number of rows. It seems that most people prefer the first syntax, as it is probably quite simple logically, however, I suspect that the optimizer does not optimize it absolutely.
Also, when LEFT, and OUTER JOINs are required, people forget that the JOIN keyword exists, and try to use the same syntax as in the first query. If I wanted all records in table1, and only associated records from table2, then the only way that I know of to retrieve this information would be as follows:
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t1.field1 = t2.field4
Would somebody please enlighten me (with regard to the optimizer, as well as alternatives for the last query above). I suspect that a lot of people are inadvertently using cross joins, when that is not what they mean to be doing, and are not impressed with the speed.
Thanks...
----------------------------------------
Michael Ansley
Intec (Ireland)
Tel : +27 21 430-9000
Cell : +27 82 784-4229
eMail: michael.ansley@intec.co.za
ICQ : 23465105
----------------------------------------
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: michael.ansley@intec.co.za
Subject: Re: Optimizations
In-Reply-To: <01BE1EC7.3A7A4790@MANSLEY>
References: <01BE1EC7.3A7A4790@MANSLEY>
* 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