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@sss.pgh.pa.us> wrote:
>> Stephen Frost <sfrost@snowman.net> writes:
>>> * Tom Lane (tgl@sss.pgh.pa.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