We want to find all the rows from multi_id where any of the IDs (including its primary key) have a certain setting with a certain value.
LATERAL seemed like the tool for the job, so we tried the following:
--------------------------------------------- SELECT mid.id1 FROM multi_id AS mid, LATERAL ( SELECT 1 FROM settings WHERE setting_id = 1 AND setting_value = 'common_1' AND owner_id IN (mid.id1, mid.id2, mid.id3) ) AS setting_matcher; ---------------------------------------------
IN semantics w.r.t NULL can result in atrocious performance in some instances. I cannot speak to this one in particular but I'm curious if
[...]
WHERE setting_id = 1
AND setting_value = 'common_1'
AND (
owner_id = mid.id1
OR
owner_id = mid.id2
OR
owner_id = mid.id3
)
placed into an EXISTS would work any better. It seems pointless to include a LATERAL if you are not going to output any of the fields from the laterally joined relation. If you want a join I'm not sure that INNER wouldn't be just as good, with an ON clause of (owner_id = mid.id1 OR owner_id = mid.id2 OR owner_id = mid.id3)
