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.
Basically, if I mix ANSI-syntax outer joins (a left outer join b on
a.id=b.id) and "where-syntax" joins (from a,b where a.id=b.id) in the same
query, things get strange.
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=5904width=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
Scanusing 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.
any hints? Or I am grossly misunderstanding outer join symantics?
-alex