pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Pilosov <alex@pilosoft.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] outer joins strangeness
Date: Mon, 24 Sep 2001 01:09:31 -0400 (EDT)
Message-ID: <Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com> (raw)
In-Reply-To: <Pine.BSF.4.21.0109232149160.3131-100000@megazone23.bigpanda.com>

On Sun, 23 Sep 2001, Stephan Szabo wrote:

> 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
<snip>
> > 
> > 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

> 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.  
Yeah, I figure that's how it sees it, but that's pretty stupid from
performance reasons :P)

It _should_ realize that left outer join only constricts join order
between two tables in outer join, and joins to all other tables should
still be treated normally.

I'm going to CC this to -hackers, maybe someone will shed a light on the
internals of this. 

> 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.
True. But this is not right outer join, its a left outer join...:)

Postgres should understand that left outer join does not constrict join
order...

-alex




view thread (6+ messages)  latest in thread

Message-ID: <Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com>
Permalink:  ../Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com/
Also on:    postgresql.org/message-id/Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: alex@pilosoft.com, sszabo@megazone23.bigpanda.com, pgsql-hackers@postgresql.org
  Subject: Re: [SQL] outer joins strangeness
  In-Reply-To: <Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com>
  References: <Pine.BSF.4.21.0109232149160.3131-100000@megazone23.bigpanda.com> <Pine.BSO.4.10.10109240102090.28103-100000@spider.pilosoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox