Re: outer joins strangeness

On Sun, 23 Sep 2001, Alex Pilosov wrote:

> It may be just me, or I am grossly misunderstanding syntax of outer joins,
> but I see that plans for my queries are different depending on how I place
> join conditions and sometimes even on order of the tables.
>
> Example:
> 1:
> explain select * from customers c,orders o left outer join adsl_orders ao
> on ao.order_id=o.order_id
> where c.cust_id=o.cust_id
> and c.cust_id=152
>
>
> Nested Loop (cost=94.23..577.47 rows=2 width=290)
> -> Index Scan using customers_pkey on customers c (cost=0.00..2.02
> rows=1 width=125)
> -> Materialize (cost=501.65..501.65 rows=5904 width=165)
> -> Hash Join (cost=94.23..501.65 rows=5904 width=165)
> -> Seq Scan on orders o (cost=0.00..131.04 rows=5904
> width=58)
> -> Hash (cost=86.18..86.18 rows=3218 width=107)
> -> Seq Scan on adsl_orders ao (cost=0.00..86.18
> rows=3218 width=107)
>
> Query 2:
>
> explain select * from customers c join orders o on c.cust_id=o.cust_id
> left outer join adsl_orders ao on ao.order_id=o.order_id
> where c.cust_id=152
>
> Nested Loop (cost=0.00..9.30 rows=2 width=290)
> -> Nested Loop (cost=0.00..5.06 rows=2 width=183)
> -> Index Scan using customers_pkey on customers c
> (cost=0.00..2.02 rows=1 width=125)
> -> Index Scan using orders_idx1 on orders o (cost=0.00..3.03
> rows=1 width=58)
> -> Index Scan using adsl_orders_pkey on adsl_orders ao
> (cost=0.00..2.02 rows=1 width=107)
>
> To me, both queries seem exactly identical in meaning, and should generate
> the same plans. However, in my experience, if I use outer join anywhere in
> the query, I must use "JOIN" syntax to join all other tables as well,
> otherwise, my query plans are _extremely_ slow.

Postgres treats join syntax as an explicit definition of what order to
joins in. So, I'd guess it sees the first as: do the LOJ and then join
that to the separate table.

And for right outer join (for example), those two queries would not
be equivalent if I read the ordering correctly. The former syntax
would mean outer first and then the inner, whereas the second would
be inner first then the outer, and that could have different results.