better performance of relationship testing

In-Depth Discussions
,
1

relationships tests, used for example when testing foreign keys, aim to figure out missing links from the child (referencing) to the parent (referred) table. As of now, this is implemented by left join, which typically triggers hash anti-join algorithm

with child as (
    select patient_id as from_field
    from "clinical_observations"
    where patient_id is not null
),

parent as (
    select patient_id as to_field
    from "demographic_information"
)

select
    from_field
from child
left join parent
    on child.from_field = parent.to_field

where parent.to_field is null;

For the sake of better performance and syntax simplicity, we could make it with simple except, to be executed via HashSetOp

explain
with child as (
    select patient_id as from_field
    from "clinical_observations"
    where patient_id is not null
),

parent as (
    select patient_id as to_field
    from "demographic_information"
)

select from_field from child
except
select to_field from parent;

NOTE: the second variants answer the question: what are distinct child values that are missing in its parent - keeping the goal in mind that should be fine as well.