Re: Removing INNER JOINs

On 12/03/2014 07:41 PM, Robert Haas wrote:
> On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>>> However, even granting that that is a concern, so what? You *have* to
>>>> do the planning twice, or you're going to be generating a crap plan for
>>>> one case or the other.
>>
>>> Yeah, I don't see a way around that..
>>
>> Also, it occurs to me that it's only necessary to repeat the join search
>> part of the process, which means that in principle the mechanisms already
>> exist for that; see GEQO. This means that for small join problems, the
>> total planning time would much less than double anyway. For large
>> problems, where the join search is the bulk of the time, we could hope
>> that removal of unnecessary joins would reduce the join search runtime
>> enough that the second search would be pretty negligible next to the
>> first (which is not optional). So I think "it'll double the runtime"
>> is an unfounded objection, or at least there's good reason to hope it's
>> unfounded.
>
> OK. One other point of hope is that, in my experience, the queries
> where you need join removal are the ones where there are lots of
> tables being joined and there are often quite a few of those joins
> that can be removed, not just one. So the extra planner overhead
> might pay off anyway.

Do you need to plan for every combination, where some joins are removed
and some are not?

I hope the same mechanism could be used to prepare a plan for a query
with parameters, where the parameters might or might not allow a partial
index to be used. We have some smarts nowadays to use custom plans, but
this could be better.

- Heikki