To: Josh Berkus <josh@agliodbs.com>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>,
	Nicolas Fertig <nfertig@swissnow.ch>, pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN with filter 
In-reply-to: <200303031030.11785.josh@agliodbs.com> 
References: <20030301232815.Q24208-100000@megazone23.bigpanda.com>
	<200303031030.11785.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Mon, 03 Mar 2003 10:30:11 -0800"
Date: Mon, 03 Mar 2003 14:17:19 -0500
Message-ID: <15484.1046719039@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Berkus <josh@agliodbs.com> writes:
> For that matter, in a few queries I've found that it pays to force the 
> planner's hand by repeating some of the clauses in the WHERE clause in the 
> JOIN as well, as:

> SELECT a.x, b.y, c.z
> FROM a JOIN b ON (a.id = b.a_id AND b.type = 'm')
> 	LEFT OUTER JOIN c ON (b.id = c.b_id)
> WHERE b.type = 'm'

> The logic is fine.  The issue comes where the outer joined table is several 
> times larger than the main queried table.  The planner should recognize the 
> possibility of filtering the records in the joined table before joining in 
> order to minimize the join operation.

AFAIR, it's always done that; certainly the above query looks quite
redundant to me.  Note though that pushing down the WHERE clause is only
legal because b is on the outer side of the join.

A comparable query in the 7.3 regression database is

regression=# explain select * from
regression-# tenk1 a join tenk1 b on (a.unique1 = b.unique1)
regression-# left join tenk1 c on (b.unique2 = c.unique2)
regression-# where b.thousand = 999;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..572.49 rows=10 width=732)
   ->  Nested Loop  (cost=0.00..542.55 rows=10 width=488)
         ->  Seq Scan on tenk1 b  (cost=0.00..483.00 rows=10 width=244)
               Filter: (thousand = 999)
         ->  Index Scan using tenk1_unique1 on tenk1 a  (cost=0.00..6.01 rows=1 width=244)
               Index Cond: (a.unique1 = "outer".unique1)
   ->  Index Scan using tenk1_unique2 on tenk1 c  (cost=0.00..3.01 rows=1 width=244)
         Index Cond: ("outer".unique2 = c.unique2)
(8 rows)

You can see that the condition on b.thousand does get pushed down to the
bottom scan.  (There's no index on b.thousand, so we don't get an
indexscan --- but we would if there were an index...)

			regards, tom lane