Re: Memoize ANTI and SEMI JOIN inner

On Wed, 9 Apr 2025 at 18:48, Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Thu, Mar 20, 2025 at 3:02 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > For making this work, I think the attached should be about the guts of
> > the code changes. I didn't look at the comments. Right now I can't
> > think of any reason why this can't be done, but some experimentation
> > might reveal some reason that it can't.
>
> I conducted some experiments, and I'm afraid it's not safe to consider
> Memoize for semi or anti joins, unless the inner side is provably
> unique.  As an example, please consider:

Thanks for checking that. I was just looking at the spot you'd need to
adjust to prove the inner_unique for anti joins and found I'd written:

/*
* XXX it may be worth proving this to allow a Memoize to be
* considered for Nested Loop Semi/Anti Joins.
*/

Looks like I must have known that at one point in time...

> Perhaps we could spend some planner cycles proving inner_unique for
> anti joins, so that Memoize nodes can be considered for them?

Worth a try. It should be pretty easy to enable, as far as I can see.
It might just be a case of shuffling the cases around in the switch
statement in add_paths_to_joinrel().

David