outer joins strangeness

From: Alex Pilosov <alex(at)pilosoft(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: outer joins strangeness
Date: 2001-09-24 01:29:11
Message-ID: Pine.BSO.4.10.10109221458500.28103-100000@spider.pilosoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Serguei Mokhov 2001-09-24 02:15:26 Re: UTF-8 support
Previous Message Tatsuo Ishii 2001-09-23 23:58:22 Re: UTF-8 support

Browse pgsql-sql by date

  From Date Subject
Next Message Frederick Klauschen 2001-09-24 02:41:52 problem loading array
Previous Message Kovacs Baldvin 2001-09-23 23:55:38 Bug?: Update on ancestor for a row of a child