Re: NOT IN subquery optimization

Hi,

On March 1, 2019 4:53:03 AM PST, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>On Fri, 1 Mar 2019 at 15:27, Richard Guo <riguo(at)pivotal(dot)io> wrote:
>> I have reviewed your patch. Good job except two issues I can find:
>>
>> 1. The patch would give wrong results when the inner side is empty.
>In this
>> case, the whole data from outer side should be in the outputs. But
>with the
>> patch, we will lose the NULLs from outer side.
>>
>> 2. Because of the new added predicate 'OR (var is NULL)', we cannot
>use hash
>> join or merge join to do the ANTI JOIN. Nested loop becomes the only
>choice,
>> which is low-efficency.
>
>Yeah. Both of these seem pretty fundamental, so setting the patch to
>waiting on author.

I've not checked, but could we please make sure these cases are covered in the regression tests today with a single liner? Seems people had to rediscover them a number of times now, and unless this thread results in an integrated feature soonish, it seems likely other people will again.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.