cockroach opt: do not plan unnecessary paired semi- and anti- lookup joins

opt: do not plan unnecessary paired semi- and anti- lookup joins

Open mgartner opened this issue 3 years ago • 1 comments

trafficstars

This commit fixes an issue where the optimizer would plan a paired semi or anti lookup join in cases when a single lookup join would suffice. This only occurred in rare cases when the join filter contained a tautology or contradiction that could not be normalized to true or false in the canonical query plan, but could be eliminated from the filters when building a lookup join. If the tautology or contradiction referenced a column not covered by the lookup index, the optimizer mistakenly assumed that the index was not covering and planned a paired join. Now the optimizer can recognize that the index is actually covering, because the referenced column is not needed to evaluate the filters, and a single lookup join is planned.

Fixes #87306

Release note (performance improvement): The optimizer now explores a plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions.