opt: eliminate unnecessary IndexJoins that wrap partial index scans #54588
Description
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