Re: join removal

On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In particular, if
>> you remove one join, it might make some other join that wasn't
>> previously removable now able to be removed, and it's not exactly
>> clear to me how to make this method cope with that.
>
> I'm not seeing how that would occur or would matter, but the worst case
> answer is to restart the scan of the SpecialJoinInfos from scratch any
> time you succeed in doing a join removal.

Well, say you have something like

SELECT 1 FROM A LEFT JOIN (B LEFT JOIN C ON Pbc) ON Pab

I think that the SpecialJoinInfo structure for the join between B and
C will match the criteria I articulated upthread, but the one for the
join between A and {B C} will not. If C had not been in the query
from the begining then we'd have had:

SELECT 1 FROM A LEFT JOIN B ON Pab

...under which circumstances the SpecialJoinInfo would match the
aforementioned criteria.

>> But I think it's
>> worth thinking about because anything based on an O(n) pass over the
>> SpecialJoinInfo structures should be far cheaper than participating in
>> the combinatorial explosion that will ensue once we actually begin
>> testing through all the join orders.
>
> Agreed.  Just deleting one rel from the join search space is an enormous
> win.

Yeah.

...Robert