Re: GEQO vs join order restrictions

On Sun, Jul 19, 2009 at 3:08 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Tom, do you think the "independent subproblem" stuff from last night
>> would be worth pursuing?
>
> It's worth looking into.  I'm not certain if it will end up being a good
> idea or not.  Right now the joinlist collapse code is pretty stupid
> (as you know --- it only thinks about the collapse_limit variables,
> plus IIRC it knows about FULL JOIN).  Making it smarter might result in
> duplication of logic, or require unpleasant refactoring to avoid such
> duplication, or even add more cycles than it's likely to save later on.
> Another issue is order of operations: I'm not sure if all the
> information needed to make such decisions has been computed at that
> point.  But we won't know unless we try it.  It seems at least
> potentially useful.

I thought about this a little more and I don't think it's going to
work.  The problem is that LEFT joins can be reordered VERY freely.
So if you have something like:

A LJ (B IJ C IJ D)

Then, sure, {B C D} is a subproblem.  But if you have:

A LJ (B IJ C IJ D) LJ E

...then it's not, any more.  Suppose E is being joined against B, for
example.  You could decide to do this:

A LJ (B LJ E IJ C IJ D)

So I think this idea has crashed and burned, as Tom would say, unless
someone has an idea for resurrecting it.

...Robert