opt: eliminate unnecessary IndexJoins that wrap partial index scans

[mgartner]

There are numerous cases where an unnecessary IndexJoin wraps a partial index scan. This happens when proving filter/predicate implication reduces the remaining filters such that a column that is not stored in the partial index is no longer referenced after the scan.

#53586 fixed this when the unnecessary IndexJoin was wrapped in a Project. However, there are other cases that this fix does not cover. Here is one example:

root@127.0.0.1:58553/defaultdb> create table t (a int, s string, index (a) where s = 'foo');

root@127.0.0.1:58553/defaultdb> explain (opt, verbose) select distinct(a) from t where s = 'foo';
                                            text
--------------------------------------------------------------------------------------------
  distinct-on
   ├── columns: a:1
   ├── grouping columns: a:1
   ├── internal-ordering: +1 opt(2)
   ├── stats: [rows=9.5617925, distinct(1)=9.5617925, null(1)=0.1]
   ├── cost: 56.1356179
   ├── key: (1)
   └── index-join t
        ├── columns: a:1 s:2
        ├── stats: [rows=10, distinct(1)=9.5617925, null(1)=0.1, distinct(2)=1, null(2)=0]
        ├── cost: 55.92
        ├── fd: ()-->(2)
        ├── ordering: +1 opt(2) [actual: +1]
        ├── prune: (1)
        ├── interesting orderings: (+1)
        └── scan t@t_a_idx,partial
             ├── columns: a:1 rowid:3
             ├── stats: [rows=10, distinct(2)=1, null(2)=0]
             ├── cost: 14.81
             ├── key: (3)
             ├── fd: (3)-->(1)
             ├── ordering: +1
             └── interesting orderings: (+3) (+1,+3)

Ideally, normalization rules could be run that would prune the unnecessary s column from the IndexJoin, and ultimately remove the IndexJoin altogether.

Jira issue: CRDB-3736

[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!