Skip to content
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

Open
firebird-automations opened this issue Jan 18, 2019 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

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).

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE5986 [ CORE5986 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Adriano,

Please clarify, are you proposing that index should be used, or some other optimization?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Improve optimizer for IS DISTINCT FROM with boolean fields => Improve optimizer to consider index usage for IS DISTINCT FROM with boolean fields

@firebird-automations
Copy link
Collaborator Author

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}

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant