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-issue-importer opened this issue Jan 18, 2019 · 7 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

Modified by: @asfernandes

Link: This issue relate to CORE5986 [ CORE5986 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

Commented by: Sean Leyne (seanleyne)

Adriano,

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2019

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant