Cascades planner can construct IN-union and IN-join plans with non-sargable predicates

[alecgrieser]

For certain kinds of queries, the Cascades planner can construct IN-union and IN-join plans with non-sargable predicates. This means that the plans end up scanning over the same ranges multiple times and then selecting out a different set of values each time. The alternative is to execute the scan once and then compensate for the IN filter using a single filter plan.

There's already logic in the cost model that's trying to prevent this. However, that doesn't always seem to work, as exemplified by the plan in this test case: https://github.com/FoundationDB/fdb-record-layer/blob/a61ac4b279c913c5d7db506ac424bdd896bfa8b7/fdb-record-layer-core/src/test/java/com/apple/foundationdb/record/provider/foundationdb/query/FDBInQueryTest.java#L2623-L2629

The plan ends up constructing an IN-union plan even if only one predicate can be pushed into an index scan. There are some other tests in FDBInQueryTestBase that have the same issue when they are switched to dual planner mode.

There is already logic in the CascadesCostModel that is supposed to tackle this: https://github.com/FoundationDB/fdb-record-layer/blob/1f02cc1ed315d7e66498ec96f1bb44eb2bdc7856/fdb-record-layer-core/src/main/java/com/apple/foundationdb/record/query/plan/cascades/CascadesCostModel.java#L349 I believe the problem is that it only kicks in if the immediate plan is an IN-join or IN-union. However, if the IN-join or IN-union is a child of a different plan, it can get past this check.