Re: OUTER JOIN performance regression remains in 8.3beta4

"Alvaro Herrera" <alvherre(at)commandprompt(dot)com> writes:

> Tom Lane wrote:
>
>> Comparing the behavior of this to my patch for HEAD, I am coming to the
>> conclusion that this is actually a *better* planning method than
>> removing the redundant join conditions, even when they're truly
>> rendundant! The reason emerges as soon as you look at cases involving
>> more than a single join. If we strip the join condition from just one
>> of the joins, then we find that the planner insists on doing that join
>> last, whether it's a good idea or not, because clauseful joins are
>> always preferred to clauseless joins in the join search logic.
>
> Would it be a good idea to keep removing redundant clauses and rethink
> the preference for clauseful joins, going forward?

I don't understand what's going on here. The planner is choosing one join
order over another because one join has more join clauses than the other? Even
though some of those joins are entirely redundant and have no selectivity?
That seems like a fortuitous choice made on entirely meaningless data.

Is there some other source of data we could use to make this decision instead
of the number of clauses? I would suggest the selectivity but from the sound
of it that's not going to help at all.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!