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)
David J.
