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=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.
any hints? Or I am grossly misunderstanding outer join symantics?
-alex
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
