Re: Allowing join removals for more join types

David Rowley <dgrowleyml@gmail.com> writes:
> It looks like the existing join removals are done quite early in the
> planning and redundant joins are removed before any subqueries from that
> query are planned. So this innerrel->subroot->parse has not been done yet.
> It seems to be done later in query_planner() when make_one_rel() is called.

It's true that we don't plan the subquery till later, but I don't see why
that's important here.  Everything you want to know is available from the
subquery parsetree; so just look at the RTE, don't worry about how much
of the RelOptInfo has been filled in.

> The best I can come up with on how to implement this is to have 2 stages of
> join removals. Stage 1 would be the existing stage that attempts to remove
> joins from non subqueries. Stage 2 would happen just after make_one_rel()
> is called from query_planner(), this would be to attempt to remove any
> subqueries that are not need, and if it managed to remove any it would
> force a 2nd call to make_one_rel().

That sounds like a seriously bad idea.  For one thing, it blows the
opportunity to not plan the subquery in the first place.  For another,
most of these steps happen in a carefully chosen order because there
are interdependencies.  You can't just go back and re-run some earlier
processing step.  A large fraction of the complexity of analyzejoins.c
right now arises from the fact that it has to undo some earlier
processing; that would get enormously worse if you delayed it further.

BTW, just taking one step back ... this seems like a pretty specialized
requirement.  Are you sure it wouldn't be easier to fix your app to
not generate such silly queries?
        regards, tom lane