Bug: SQL cannot be executed in a reasonable time

[kapsner]

This SQL statement takes very very long to return the results:

SELECT 
r1.fhir_id AS "Fall.Versorgungsstellenkontakt.Aufnahmenummer", 
r2.cd_system ->> 'code' AS "Laborbefund.Laboruntersuchung.Code" 
FROM ( SELECT * FROM ( 
SELECT 
fhir_id, 
to_timestamp(jsonb_path_query(DATA, '$.period') ->> 'start', 'YYYY-MM-DDTHH:MI:SS') AS fhir_start_date, 
jsonb_array_elements_text(jsonb_path_query(DATA, '$.meta.profile')) AS fhir_profile 
FROM resources 
WHERE TYPE = 'Encounter') AS r_intermediate ) r1 
LEFT JOIN ( 
SELECT 
REPLACE(jsonbdata2 -> 'encounter' ->> 'reference', 'Encounter/', '') AS eid, 
cd_system FROM ( 
SELECT 
DATA AS jsonbdata2, 
jsonb_array_elements(jsonb_path_query(DATA, '$.code.coding')) AS cd_system, 
jsonb_array_elements(jsonb_path_query(DATA, '$.category.coding')) AS cd_category 
FROM resources 
WHERE TYPE = 'Observation') r3 
WHERE r3.cd_system ->> 'system' = 'http://loinc.org/' AND 
r3.cd_category ->> 'code' = '26436-6' 
) r2 ON 
r2.eid = r1.fhir_id 
WHERE r1.fhir_profile = 'https://fhir.miracum.org/core/StructureDefinition/Versorgungsfall';

It would be great, if the fhir-gateway could somehow be "enhanced" in a way, that this and similar SQL statements can be executed in a reasonable amount of time.

Some suggestions exist already, e.g.

• introducing ID-columns with indices that can be used for joining: Add new columns to table 'resources' #31
• setting indices direclty inside the JSON: set indices in json #42

[rajesh-murali]

Would it be ok if we add a filter on date to systematically filter and search. Similar to the following where an effectivedate. However the Query need to be tested

SELECT 
r1.fhir_id AS "Fall.Versorgungsstellenkontakt.Aufnahmenummer", 
r2.cd_system ->> 'code' AS "Laborbefund.Laboruntersuchung.Code" 
FROM ( SELECT * FROM ( 
SELECT 
fhir_id, 
to_timestamp(jsonb_path_query(DATA, '$.period') ->> 'start', 'YYYY-MM-DDTHH:MI:SS') AS fhir_start_date, 
jsonb_array_elements_text(jsonb_path_query(DATA, '$.meta.profile')) AS fhir_profile 
FROM resources 
WHERE TYPE = 'Encounter') AS r_intermediate ) r1 
LEFT JOIN ( 
SELECT 
REPLACE(jsonbdata2 -> 'encounter' ->> 'reference', 'Encounter/', '') AS eid, 
cd_system FROM ( 
SELECT 
DATA AS jsonbdata2, 
jsonb_array_elements(jsonb_path_query(DATA, '$.code.coding')) AS cd_system, 
jsonb_array_elements(jsonb_path_query(DATA, '$.category.coding')) AS cd_category, 
jsonb_array_elements(jsonb_path_query(DATA, '$.effectiveDatetime')) AS cd_effectivedate
FROM resources 
WHERE TYPE = 'Observation') r3 
WHERE r3.cd_system ->> 'system' = 'http://loinc.org/' AND 
r3.cd_category ->> 'code' = '26436-6' AND
r3.cd_effectivedate ->> 'date' BETWEEN TO_DATE('2020-01-01T00:00:00','YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2020-01-02T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
) r2 ON 
r2.eid = r1.fhir_id 
WHERE r1.fhir_profile = 'https://fhir.miracum.org/core/StructureDefinition/Versorgungsfall';

[kapsner]

@rajesh-murali thanks for your suggestion. We do that already by replacing AS r_intermediate with AS r_intermediate WHERE r_intermediate.fhir_start_date BETWEEN .... AND. ....

However, that doesn't help as well.

FYI @KoesterH