Re: Using each rel as both outer and inner for JOIN_ANTI

On Thu, Apr 6, 2023 at 8:18 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:

> On Thu, Apr 6, 2023 at 9:11 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > > Thanks for reminding. Attached is the rebased patch, with no other
> > > changes. I think the patch is ready for commit.
> >
> > Pushed after a little further fooling with the comments. I also had
> > to rebase it over 11c2d6fdf (Parallel Hash Full Join). I think I did
> > that correctly, but it's not clear to me whether any of the existing
> > test cases are now doing parallelized hashed right antijoins. Might
> > be worth a little more testing.
>
> I don't see any (at least that are EXPLAINed). Wondering if we should
> add some of these into join_hash.sql, but probably not before I figure
> out how to make that whole file run faster...

Thanks Tom for the rebase and pushing. Agreed that we need to add more
testing to cover Parallel Hash Right Anti Join. I tried one in
join_hash.sql as below

explain (costs off)
select count(*) from simple r right join bigger_than_it_looks s using (id)
where r.id is null;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Gather
Workers Planned: 2
-> Parallel Hash Right Anti Join
Hash Cond: (r.id = s.id)
-> Parallel Seq Scan on simple r
-> Parallel Hash
-> Parallel Seq Scan on bigger_than_it_looks s
(8 rows)

But as Thomas said, maybe we need to wait until that file becomes
faster.

Thanks
Richard