opt: improve selectivity estimation of JSON/Array/Spatial predicates in Select

[rytaft]

Our selectivity estimation of JSON/Array/Spatial predicates is pretty good once these predicates are pushed down into a Scan over an inverted index, because they are converted into an InvertedConstraint, which is used to constrain an inverted histogram and calculate selectivity from the histogram.

However, our selectivity estimation of these predicates when they are still represented as a filter condition in a Select is very inaccurate; we treat all of these predicates as having selectivity 1/3 or 1/9. For other filters, we can use the Constraints in the scalarProps of the filter to constrain a histogram. In order to do something similar for JSON/Array/Spatial filters, we should consider storing an InvertedConstraint with an inverted.SpanExpression in props.Scalar. This would allow us to constrain a histogram if available and calculate selectivity more accurately.

Storing the InvertedConstraint likely has other benefits, too. For example, it may allow us to completely remove code for @> and other JSON/Array operators from the constraint library.

Epic CRDB-16930

Jira issue: CRDB-2659

Jira issue: CRDB-13905

[angelazxu]

Didn't make a lot of progress on this issue, but here are some things I tried, for whoever takes it next!

• Add InvertedConstraint *inverted.SpanExpression to props.Scalar struct
• Make a new function buildInvertedConstraint in constraint_builder.go, this is called by buildFiltersItemProps in logical_props_builder.go (similar to buildConstraints). This would return something like (s *inverted.SpanExpression, ok bool).
• Inside buildInvertedConstraint, I tried to use ExtractInvertedFilterConditionFromLeaf (exported the current extractInvertedFilterConditionFromLeaf function, however this required an invertedFilterPlanner receiver, which we would have to construct. I then tried making a GetInvertedFilterPlanner function to select the correct filter planner (geo vs. JSON/Array), but this required arguments such as cat.Index that were not accessible from memo.
• I then tried to construct a new function instead, for finding the span expression, but didn't get too far here.

[rytaft]

Thanks for sharing this info, @angelazxu!

[github-actions[bot]]

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!