Re: Wrong query results caused by loss of join quals

On Thu, Feb 23, 2023 at 4:50 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I thought about this and decided that it's not really a problem.
> have_relevant_joinclause is just a heuristic, and I don't think we
> need to prioritize forming a join if the only relevant clauses look
> like this. We won't be able to use such clauses for merge or hash,
> so we're going to end up with an unconstrained nestloop, which isn't
> something to be eager to form. The join ordering rules will take
> care of forcing us to make the join when necessary.

Agreed. And as I tried, in lots of cases joins with such clauses would
be accepted by have_join_order_restriction(), which always appears with
have_relevant_joinclause().

> The only easy improvement I can see to make here is to apply the old
> rules at inner joins. Maybe it's worth complicating the data structures
> to be smarter at outer joins, but I rather doubt it: we could easily
> expend more overhead than we'll save here by examining irrelevant ECs.
> In any case, if there is a useful optimization here, it can be pursued
> later.

This makes sense.

> I changed it anyway after noting that (a) passing in the ojrelid is
> needful to be able to distinguish inner and outer joins, and
> (b) the existing comment about the join_relids input is now wrong.
> Even if it happens to not be borked for current callers, that seems
> like a mighty fragile assumption.

Agreed. This is reasonable.

> Less-hasty v2 patch attached.

I think the patch is in good shape now.

Thanks
Richard