Compound analytics index usage on joins

I have the following analytics indexes:

create index observationsSingleItemsCodingIdx
on hdmpDevFhirLatest._default.ObservationsSingleItems (subject.reference:string, (unnest code.coding select code:string))
exclude unknown key;

create index observationsSingleItemsSubjectRefIdx
on hdmpDevFhirLatest._default.ObservationsSingleItems (subject.reference:string);

And the following query:

select pat.*
(select meta(p).id as pid, p.identifier[0].value as NISS, ([0].given[0] || ’ ’ ||[0].family) as Name
from hdmpDevFhirLatest._default.Patients p
where p.managingOrganization.reference=‘Organization/303’) as pat
join hdmpDevFhirLatest._default.ObservationsSingleItems si on any cod in si.code.coding satisfies to_string(‘Patient/’ || /*+ indexnl */ = si.subject.reference and ‘009B’ = cod.code;

In the access plan I see is using the index “observationsSingleItemsSubjectRefIdx” in the join. What should I do to make use of the compound index that includes the filtering by code “observationsSingleItemsCodingIdx”?

Thank you for your help!

Hi @TonyCastan,
Using a composite-atomic array index to satisfy a join predicate like the one in your case is not currently supported.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.