New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Improve optimizer to consider index usage for IS DISTINCT FROM with boolean fields [CORE5988] #2238
Comments
Commented by: @asfernandes That becomes more important with CORE5986, as a solution for it is to treat IS NOT {TRUE | FALSE | NULL | UNKNOWN} as IS DISTINCT FROM (i.e. blr_equiv). |
Modified by: @asfernandes |
Commented by: Sean Leyne (seanleyne) Adriano, Please clarify, are you proposing that index should be used, or some other optimization? |
Modified by: @asfernandessummary: Improve optimizer for IS DISTINCT FROM with boolean fields => Improve optimizer to consider index usage for IS DISTINCT FROM with boolean fields |
Commented by: Sean Leyne (seanleyne) Thanks for clarifying the ticket summary/description. I wonder, though, given the limited selectivity of a Boolean based index, would such an index actually provide any benefit? {The cost of the random IO involved in checking the current value of the field would, in many cases, out-weight the value of using the index, thus making a NATURAL table scan more efficient} |
Commented by: @asfernandes There could be multi-segmented boolean fields in a index, that when combined would have a better selectivity. Currently even with an explicit PLAN the index is not usable with IS DISTINCT FROM. |
Commented by: Sean Leyne (seanleyne) I agree that Boolean fields present in multi-segment indexes should be used. But, at the moment, this ticket suggests that even single-segment Boolean indexes would be considered... Are you proposing that they would be considered? Or only in multi-segment index use cases? |
Submitted by: @asfernandes
Relate to CORE5986
Currently optimizer do not use index for <boolean field> IS DISTINCT FROM {TRUE | FALSE | NULL}
While it seems ok that IS DISTINCT FROM does not use index for others field types, it could be more smart for booleans.
The text was updated successfully, but these errors were encountered: