Thread: Optimizations

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
substantiallyfaster, particularly as the tables involved increase in number of rows.  It seems that most people prefer
thefirst 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
syntaxas in the first query.  If I wanted all records in table1, and only associated records from table2, then the only
waythat 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
suspectthat a lot of people are inadvertently using cross joins, when that is not what they mean to be doing, and are
notimpressed 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
----------------------------------------

> 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

Using this is a sign of an SQL engine with a bad or nonexistant
optimizer.  We have a good optimizer which will properly handle such
queries.

>
> As I understand it, both queries will always return the same results, however, the second query will execute
substantiallyfaster, particularly as the tables involved increase in number of rows.  It seems that most people prefer
thefirst 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
syntaxas in the first query.  If I wanted all records in table1, and only associated records from table2, then the only
waythat 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).
Isuspect that a lot of people are inadvertently using cross joins, when that is not what they mean to be doing, and are
notimpressed with the speed. 

We don't support outer joins yet.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hi, everybody...

Well before any kind of question, let me wish you a merry .Xmas
I hope all your gnu-dreams come true in the next year  and
ask Santa for a new bug-free version of guindous ( a miracle !! ).

Well, I'm trying to build faster my postgresql applications, so I'm
looking for those system tables that could helpme.

I have a screen-shot of a "system-catalogs.gif", but it doesn't content
all the system tables.

Could you send me a better pic than the "system-catalogs.gif"  or
something to understand the relations among system tables ???.

Do you have an idea of what tables should I consider on the Data Dictionary ???

Now I'm working with ver. 6.3.2... but i will download the 6.4 on this week.

I'm interesed in having one or more tables to describe fields, aliases,
picture on edition, text on edition, realted querys  an more.

I'm I going to cathedral instead bazaar ???
I don't know. Please tellme what do you think.

P.S. Thanks to all the hackers that made possible the Postgresql proyect;
           in my third-world-considered-country, people's finding a new way to
           build cheaper and more powerful applications for government, education
           and bussiness. Thank you, and take de turkey out the freezer.


Saludos Amigos !!!

--
"Cuando Microdog ladra, es porque vamos caminando..."
                           M.C.S. et al

David.Martinez.Cuevas.mx
     Office 622-60-72      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     Home 565-25-17          "Eat Linux, Drink Linux...  SMOKE LINUX "
                           @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

At 2:35 +0200 on 15/12/98, David Martinez Cuevas wrote:


>
> Could you send me a better pic than the "system-catalogs.gif"  or
> something to understand the relations among system tables ???.
>
> Do you have an idea of what tables should I consider on the Data
>Dictionary ???

Did you take a look in "man catalogs"?

I think the pg_class and pg_attribute should be the most helpful. All the
fields are described in the manpage.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma