Re: Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

On Fri, Nov 13, 2015 at 11:25 PM, Steven Grimm <sgrimm(at)thesegovia(dot)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.