Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()

On Tue, Sep 5, 2023 at 4:52 PM tender wang <tndrwang(at)gmail(dot)com> wrote:

> I recently run benchmark[1] on master, but I found performance problem
> as below:
> ...
>
> I debug the code and find consider_parallel_nestloop() doesn't consider
> materialized form of the cheapest inner path.
>

Yeah, this seems an omission in commit 45be99f8. I reviewed the patch
and here are some comments.

* I think we should not consider materializing the cheapest inner path
if we're doing JOIN_UNIQUE_INNER, because in this case we have to
unique-ify the inner path.

* I think we can check if it'd be parallel safe before creating the
material path, thus avoid the creation in unsafe cases.

* I don't think the test case you added works for the code changes.
Maybe a plan likes below is better:

explain (costs off)
select * from tenk1, tenk2 where tenk1.two = tenk2.two;
QUERY PLAN
----------------------------------------------
Gather
Workers Planned: 4
-> Nested Loop
Join Filter: (tenk1.two = tenk2.two)
-> Parallel Seq Scan on tenk1
-> Materialize
-> Seq Scan on tenk2
(7 rows)

Thanks
Richard