On Fri, Nov 13, 2015 at 11:25 PM, Steven Grimm <sgr...@thesegovia.com> wrote:
> 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.
