Re: Removing INNER JOINs

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.

(It still seems a shame to have to plan for the not-removing-the-joins
case since it will so rarely happen. But maybe I should take what I
can get.)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company