search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.23k stars 212 forks source link

Improve optimizer to consider index usage for IS DISTINCT FROM with boolean fields [CORE5988] #2238

Open firebird-automations opened 5 years ago

firebird-automations commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago
Modified by: @asfernandes Link: This issue relate to [CORE5986](https://github.com/FirebirdSQL/firebird/issues?q=CORE5986+in%3Atitle) \[ [CORE5986](https://github.com/FirebirdSQL/firebird/issues?q=CORE5986+in%3Atitle) \]
firebird-automations commented 5 years ago

Commented by: Sean Leyne (seanleyne)

Adriano,

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

firebird-automations commented 5 years ago
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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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?