opt: transfer constraints on join equality columns

[RaduBerinde]

We saw a user case that was roughly of the form:
SELECT .. FROM (SELECT * FROM t WHERE x=1) AS t1 JOIN t2 ON t1.x=t2.x

Here it would be ideal to push down the x=1 constraint on the right-hand side, in the hope that there is an index there that can help. Without pushing the constraint, we would get a lookup join on that index which is not too bad, but pushing down x=1 can make things more efficient (and may trigger other optimizations).

The rough idea from some discussions:

• use rule props to bubble up constraints on relevant columns
• a normalization rule for join checks both sides for constraints on equality columns, if they are not the same, it pushes down additional filters as needed (by wrapping the input in a Select).

There is a difficulty here: if we create a Select and the filter gets pushed down by other rules, it must be guaranteed that the constraint would reflect the filter (in other words, the constraint propagation can't be "best effort"). If this is not the case, we would enter an infinite loop where we keep adding the filter.

Jira issue: CRDB-5595

[RaduBerinde]

#20100 is somewhat related.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!