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

David G. Johnston

November 13, 2015 at 10:48 PM

 

​

​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.

Better, but still bad. Execution time on my test system goes from 6583ms to 4394ms, whereas the version with just one "=" takes 12ms.

  It seems pointless to include a LATERAL if you are not going to output any of the fields from the laterally joined relation.

True. Our actual query is more complex than the stripped-down one here. I wanted to reduce it to the bare minimum to demonstrate the performance problem so as to make it easier to figure out what was going on. Including columns from the LATERAL query or leaving them out doesn't have much impact on execution time.

-Steve