Query where clause on indexed FK's where fieldvalue IS NULL bad performance

[Pieter Bas Hofstede]

Hi all,

FB 5.0.3 (as well on earlier 5.x versions).
I've got a query like

select
  t1.ID
from tableX t1
where
  (t1.FK2 IS NULL) and
  (t1.FK1 = char_to_uuid('9A05D014-1414-4B93-ADC3-1723C47F80E9')) and
  (t1.INTFIELD = 2) and
  (t1.DATEFIELD = '19.10.2024')
 
 
PLAN is:
PLAN (T1 INDEX (COMPOUND_IDX4, FK2_IDX))

COMPOUND_IDX4 = (FK1, INTFIELD, DATEFIELD);
FK2_IDX = (FK2)  
 

SQL Duration: 1s734ms
Fetches from cache = 23.754  


When I force to don't use the index of the FK2-field using coalesce (its char(16) OCTETS):

select
  t1.ID
from tableX t1
where
  (coalesce(t1.FK2, NULL) IS NULL) and
  (t1.FK1 = char_to_uuid('9A05D014-1414-4B93-ADC3-1723C47F80E9')) and
  (t1.INTFIELD = 2) and
  (t1.DATEFIELD = '19.10.2024')

PLAN Is:  
PLAN (T1 INDEX ( COMPOUND_IDX4 ))


SQL Duration: 0s015ms
Fetches from cache = 843
 
 
Question:
Can the Firebird-engine itself (optimizer, plan, index navigation on NULL) do optimizations like this automatically? So at some point I don't have to manually force to bypass the FK2-index in NULL situations?

[Pieter Bas Hofstede]

Recordcount result = 839

selectivity COMPOUND_IDX4  = 0,000001068938
selectivity  FK2_IDX = 0,000003139274

Op maandag 21 juli 2025 om 11:25:09 UTC+2 schreef Pieter Bas Hofstede:

[Dimitry Sibiryakov]

Pieter Bas Hofstede wrote 21.07.2025 11:25:
> Question:
> Can the Firebird-engine itself (optimizer, plan, index navigation on NULL) do
> optimizations like this automatically? So at some point I don't have to manually
> force to bypass the FK2-index in NULL situations?

Yes and no. Partial indexes[1] are designed exactly for such cases but
unfortunately, foreign keys cannot use them.
Perhaps it worth to request a feature that the index for FK was created with
"WHERE IS NOT NULL" condition by default.

1.
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-ddl-index.html#fblangref50-ddl-idx-partial

--
WBR, SD.

[Pieter Bas Hofstede]

Thanks for the quick response.

Would the solution be like the same thing what is currently in v6 roadmap planning? 

#7964, #7258
Make FOREIGN KEY constraint to use any suitable index

Op maandag 21 juli 2025 om 11:36:36 UTC+2 schreef sd:

[Dimitry Sibiryakov]

Pieter Bas Hofstede wrote 21.07.2025 11:42:
> Would the solution be like the same thing what is currently in v6 roadmap planning?

Yes, except it wouldn't require manual creation of the index.

> #7964 <https://github.com/FirebirdSQL/firebird/issues/7964>, #7258 <https://
> github.com/FirebirdSQL/firebird/issues/7258>

> Make FOREIGN KEY constraint to use any suitable index

--
WBR, SD.

[Pieter Bas Hofstede]

Thanks, https://github.com/FirebirdSQL/firebird/issues/8656 created

Op maandag 21 juli 2025 om 11:45:23 UTC+2 schreef sd: