Issue Details (XML | Word | Printable)

Key: CORE-5988
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 2

If you were logged in you would be able to see more operations.
Firebird Core

Improve optimizer to consider index usage for IS DISTINCT FROM with boolean fields

Created: 18/Jan/19 03:02 PM   Updated: 18/Jan/19 06:44 PM
Component/s: Engine
Affects Version/s: 4.0 Alpha 1, 3.0.4
Fix Version/s: None

Issue Links:

QA Status: No test

 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 18/Jan/19 03:04 PM
That becomes more important with CORE-5986, as a solution for it is to treat IS NOT {TRUE | FALSE | NULL | UNKNOWN} as IS DISTINCT FROM (i.e. blr_equiv).

Sean Leyne added a comment - 18/Jan/19 06:10 PM

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

Sean Leyne added a comment - 18/Jan/19 06:26 PM
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}

Adriano dos Santos Fernandes added a comment - 18/Jan/19 06:34 PM
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.

Sean Leyne added a comment - 18/Jan/19 06:44 PM
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?