To: apb18@cornell.edu
cc: pgsql-performance@postgresql.org
Subject: Re: Propagating outer join conditions 
In-reply-to: <19ab0ccd0612030711s70a3e095hceb69509cbd41597@mail.gmail.com> 
References: <19ab0ccd0612030711s70a3e095hceb69509cbd41597@mail.gmail.com>
Comments: In-reply-to "Aaron Birkland" <birkie@gmail.com>
	message dated "Sun, 03 Dec 2006 10:11:30 -0500"
Date: Sun, 03 Dec 2006 13:24:19 -0500
Message-ID: <21633.1165170259@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Aaron Birkland" <birkie@gmail.com> writes:
> ... Is is possible to generate a plan that looks like this:

>  Nested Loop Left Join  (cost=???)
>    ->  Index Scan using t28_o on t28  (cost=0.00..9.11 rows=1 width=89)
>          Index Cond: (o = '"spec"'::text)
>    ->  Nested Loop  (cost=???)
>          ->  Index Scan using t1_s on t1  (cost=???)
>                Index Cond: (s = t28.s)
>          ->  Bitmap Heap Scan on t11  (cost=???)
>                Recheck Cond: (t11.s = t1.o)
>                Filter: (o = '<http://example.org>'::text)
>                ->  Bitmap Index Scan on t11_s  (cost=??? )
>                      Index Cond: (t11.s = t1.o)

No.  It'd take massive work in both the planner and executor to support
something like that --- they are designed around the assumption that
nestloop with inner index scan is exactly that, just one indexscan on
the inside of the loop.  (As of 8.2 there's some klugery to handle an
Append of indexscans too, but it won't generalize readily.)

It might be something to work on for the future, but I can see a couple
of risks to trying to support this type of plan:
* exponential growth in the number of plans the planner has to consider;
* greatly increased runtime penalty if the planner has underestimated
  the number of rows produced by the outer side of the nestloop.

			regards, tom lane