Re: NOT IN subquery optimization

[David Steele]

On 3/26/20 4:58 PM, Li, Zheng wrote:
     >BTW, so far as I can see, the only reason you're bothering with the whole
     thing is to compare the size of the subquery output with work_mem, because
     that's all that subplan_is_hashable does.  I wonder whether that
     consideration is even still necessary in the wake of 1f39bce02.  If it is,
     I wonder whether there isn't a cheaper way to figure it out.  (Note
     similar comment in make_subplan.)
     The comment in make_subplan says there is no cheaper way to figure out:
     /* At present, however, we can only check hashability after
      * we've made the subplan :-(.  (Determining whether it'll fit in work_mem
      * is the really hard part.)
      */

     I don't see why commit 1f39bce02 is related to this problem. Can you 
expand on this?
         
     >But can't you detect that case directly?  It seems like you'd need to
     figure out the NULL situation anyway to know whether the transformation
     to antijoin is valid in the first place.
     
     Yes, we do need to figure out the NULL situation, and there is always valid transformation
     to antijoin, it's just in the NULL case we need to stuff additional clause 
to the anti join
     condition, and in these cases the transformation actually outperforms 
Subplan (non-hashed),
     but underperforms the hashed Subplan. The unmodified anti hash join has 
similar performance
     compared to hashed Subplan.

There seem to be enough questions about this implementation that I think 
it makes sense to mark this patch Returned with Feedback.

Feel free to resubmit it to a future CF when there is more of a 
consensus on the implementation.

Regards,
--
-David
da...@pgmasters.net