Bypass join to TokenIndexEntity table for SearchParameter _id

[LZRS]

Is your feature request related to a problem? Please describe.
Searching for resources given multiple ids with filter _id, does a join to the TokenIndexEntity, that may seem to incur an extra performance cost

Describe the solution you'd like
When search is done using filter in the form

fhirEngine.search<Patient>{
        filter(Patient.RES_ID, { value = of("1234") }, { value = of("23456") })
}

the query generated is of the form of

SELECT a.resourceUuid, a.serializedResource
 FROM ResourceEntity a
WHERE a.resourceType = 'Patient'
         AND a.resourceUuid IN (
SELECT resourceUuid FROM TokenIndexEntity
WHERE resourceType = 'Patient' AND index_name = '_id' AND (index_value = '1234' OR index_value = '23456')
 )

The suggestion we had was if we could bypass joining to the TokenIndexEntity table for the _id , and instead filter directly from the ResourceEntity table.
The resulting query would therefore look similar to

SELECT a.resourceUuid, a.serializedResource
 FROM ResourceEntity a
WHERE a.resourceType = 'Patient' 
         AND a.resourceId  IN ('1234', '23456')
 )

Additional context
Link to RES_ID constant

Describe alternatives you've considered
Enhancing the fhirEngine.get interface to support multiple resourceId to fetch multiple resources, although this wouldn't work with the rest of the Search DSL

Would you like to work on the issue?
Please state if this issue should be assigned to you or who you think could help to solve this issue.

[MJ1998]

Thanks for raising this @LZRS. This is a valid request. However the SQL generation logic in engine library is generic to deal with all kinds of keys.
So implementing the "bypass TokenIndexEntity" above would require to write custom logic for the RES_ID key.

Let us evaluate if this is possible. @jingtang10 @aditya-07

[aditya-07]

Thanks for raising this @LZRS. This is a valid request. However the SQL generation logic in engine library is generic to deal with all kinds of keys.

@LZRS Would an overloaded suspend fun get(type: ResourceType, vararg ids: String): Resource be better fit for this usecase?

[LZRS]

@LZRS Would an overloaded suspend fun get(type: ResourceType, vararg ids: String): Resource be better fit for this usecase?

Yeah, that would work although we have some usecases that would require Search DSL, for example getting Patient's Observations and Conditions using revInclude

Example

fhirEngine.search<Patient>{
        filter(Patient.RES_ID, { value = of("1234") }, { value = of("23456") })
        revInclude<Observation>(Observation.SUBJECT)
        revInclude<Condition>(Condition.SUBJECT)
}