mv_stat_lvl_1_ehr_observations

-- NEW QUERY
EXPLAIN ANALYZE
SELECT ehr_observations.id,
       ehr_observations.encounter_id,
       ehr_observations.diagnostic_report_id,
       ehr_observations.employee_id,
       ehr_observations.patient_id,
       nullif(trim(ehr_observations.surveillance_value ->>'value'), '') as value,
       classification_fhir_observation_codes.title_translations -> 'uk' as title,
       classification_fhir_observation_codes.code,
       mv_stat_lvl_0_receptions.asserted_date,
       mv_stat_lvl_0_receptions.onset_date,
       mv_stat_lvl_0_receptions.legal_entity_id
FROM ehr_observations
         LEFT JOIN mv_stat_lvl_0_receptions ON ehr_observations.encounter_id = mv_stat_lvl_0_receptions.id
         LEFT JOIN ehr_diagnostic_reports ON ehr_diagnostic_reports.ehealth_status = 1 AND ehr_observations.diagnostic_report_id = ehr_diagnostic_reports.id
         INNER JOIN classification_fhir_observation_codes ON classification_fhir_observation_codes.id = ehr_observations.codeable_id
WHERE (ehr_observations.diagnostic_report_id IS NOT NULL OR ehr_observations.encounter_id IS NOT NULL) AND classification_fhir_observation_codes.code IN ('29463-7', '39156-5', '8302-2', '14743-9', '8462-4', '8480-6')

-- OLD QUERY
EXPLAIN ANALYZE
SELECT
    ehr_observations.id,
    ehr_observations.encounter_id,
    ehr_observations.employee_id,
    ehr_observations.patient_id,
    nullif(trim(ehr_observations.surveillance_value ->>'value'), '') as value,
    classification_fhir_observation_codes.title_translations -> 'uk' as title,
    classification_fhir_observation_codes.code,
    mv_stat_lvl_0_receptions.asserted_date,
    mv_stat_lvl_0_receptions.onset_date,
    mv_stat_lvl_0_receptions.legal_entity_id
FROM ehr_observations
         INNER JOIN mv_stat_lvl_0_receptions ON ehr_observations.encounter_id = mv_stat_lvl_0_receptions.id
         INNER JOIN classification_fhir_observation_codes ON classification_fhir_observation_codes.id = ehr_observations.codeable_id
WHERE classification_fhir_observation_codes.code IN ('29463-7', '39156-5', '8302-2', '14743-9', '8462-4', '8480-6')