Convert subquery for matching Patient ID into join to avoid performance issue on PostgreSQL

[gunterze]

Convert subquery for matching Patient ID, e.g.:

select s1_0.study_fk, ...
from series s1_0
         join study s2_0 on s2_0.pk = s1_0.study_fk
         join patient p1_0 on p1_0.pk = s2_0.patient_fk
         join dicomattrs a1_0 on a1_0.pk = p1_0.dicomattrs_fk
         left join study_query_attrs q1_0 on s2_0.pk = q1_0.study_fk and q1_0.view_id=?
         join dicomattrs a2_0 on a2_0.pk = s2_0.dicomattrs_fk
         left join metadata m1_0 on m1_0.pk = s1_0.metadata_fk
         left join series_query_attrs q2_0 on s1_0.pk = q2_0.series_fk and q2_0.view_id=?
         join dicomattrs a3_0 on a3_0.pk = s1_0.dicomattrs_fk
where exists(select p2_0.pk
             from patient_id p2_0
             where (p2_0.pat_id=?) and p2_0.patient_fk = p1_0.pk)

into join, e.g.:

select s1_0.study_fk, ...
from series s1_0
         join study s2_0 on s2_0.pk = s1_0.study_fk
         join patient p1_0 on p1_0.pk = s2_0.patient_fk
         join patient_id p2_0 on p1_0.pk = p2_0.patient_fk
         join dicomattrs a1_0 on a1_0.pk = p1_0.dicomattrs_fk
         left join study_query_attrs q1_0 on s2_0.pk = q1_0.study_fk and q1_0.view_id=?
         join dicomattrs a2_0 on a2_0.pk = s2_0.dicomattrs_fk
         left join metadata m1_0 on m1_0.pk = s1_0.metadata_fk
         left join series_query_attrs q2_0 on s1_0.pk = q2_0.series_fk and q2_0.view_id=?
         join dicomattrs a3_0 on a3_0.pk = s1_0.dicomattrs_fk
where p2_0.pat_id=?

to avoid performance issue on PostgreSQL caused by https://www.cybertec-postgresql.com/en/subqueries-and-performance-in-postgresql/

PostgreSQL can only execute a scalar subquery as a nested loop

The query will return duplicates, if one patient has multiple matching Patient IDs - which seems quite rare.