Re: NOT IN subquery optimization

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
david(at)pgmasters(dot)net