opt: unconstrained partial index scans should not add index joins when remaining filters prune columns #51018
Description
The index join in the plan below is not necessary. I think that the index join exists because GeneratePartialIndexScans does not consider the new required columns with the remaining filters.
exec-ddl
CREATE TABLE t (
k INT PRIMARY KEY,
i INT,
f FLOAT,
s STRING,
b BOOL,
INDEX (i) WHERE s = 'foo'
)
----
opt format=show-all expect=GeneratePartialIndexScans
SELECT i FROM t WHERE s = 'foo'
----
project
├── columns: i:2(int)
├── stats: [rows=10]
├── cost: 51.33
├── prune: (2)
├── interesting orderings: (+2)
└── index-join t
├── columns: t.public.t.i:2(int) t.public.t.s:4(string!null)
├── stats: [rows=10, distinct(4)=1, null(4)=0]
├── cost: 51.22
├── fd: ()-->(4)
├── prune: (2)
├── interesting orderings: (+2)
└── scan t.public.t@secondary,partial
├── columns: t.public.t.k:1(int!null) t.public.t.i:2(int)
├── stats: [rows=10, distinct(4)=1, null(4)=10]
├── cost: 10.41
├── key: (1)
├── fd: (1)-->(2)
├── prune: (1,2)
└── interesting orderings: (+1) (+2,+1)
Jira issue: CRDB-56271