Fb 2.5.1

I have an EXECUTE BLOCK statement, for which a small part goes NATURAL where I'd expect it to use an index associated with a UNIQUE CONSTRAINT.

I would expect

JOIN icd10_purres p ON p.icd10 IN (t.icd10, substring(t.icd10 from 1 for 3))

to use an index, maybe something along the lines of:

PLAN JOIN (P INDEX(U_ICD10_PURRES_ICD10, U_ICD10_PURRES_ICD10), ...

However, the optimizer goes NATURAL.

ICD10_PURRES is a tiny table, but I thought 112 records might be enough for an index to be useful Or is the possibility of reducing the number of potential records by 99% not enough to warrant the use of an index? Other tables in the query are considerably bigger (NPR contains 7 million, NPR_KRG_ID half a million and NPR_TILSTAND 10 million).

This is part of the following FOR SELECT:

for with tmp (id_npr, avdnr, reshid, icd10, innskrivingsdato, aar, omsorgsniva, diagnosenr) as
(select n.id_npr, n.tjenesteenhetkode, n.tjenesteenhetreshid,
iif(substring(t.tilstand from 4 for 1) = '.', substring(t.tilstand from 1 for 3)||substring(t.tilstand from 5 for 1), substring(t.tilstand from 4 for 1)),
n.innskrivingsdato, extract(year from n.innskrivingsdato), n.omsorgsniva, t.nr
from npr n
join npr_krg_id kid on n.npr_krg_id = kid.npr_krg_id
join npr_tilstand t on n.ID_NPR = t.ID_NPR
where kid.fnr_kryptert = :fnr_k
and not n.innskrivingsdato between current_date-90 and current_date)

select t.id_npr, t.avdnr, t.reshid, t.icd10, t.innskrivingsdato, t.aar, p.icd10_c, t.diagnosenr, t.omsorgsniva, g.gruppe, i.beskrivelse
from tmp t
join icd10_purres p on p.icd10 in (t.icd10, substring(t.icd10 from 1 for 3))
join icd10_gruppe g on g.icd10 = p.icd10_c and not exists(select * from icd10_gruppe g2 where g2.icd10 = g.icd10 and g.gruppe < g2.gruppe)
left join ICD10 i on t.icd10 = i.kode
into ... do ...

for which the optimizer creates this plan:

PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10))

Eventually, this EXECUTE BLOCK will be turned into a STORED PROCEDURE. Currently, I'm looking at running the procedure maybe half a million times in a loop, later it will be run daily or weekly, but then only on a few thousand records each time. I haven't tried running things yet, but would expect running the EXECUTE BLOCK once to finish quickly, whereas half a million times hopefully would take a few hours and not days.

I'm just puzzled and am asking because I would like to increase my understanding...

Set