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

Hi tom,
Do you have any comments or suggestions on this issue? Thanks.

Richard Guo <guofenglinux(at)gmail(dot)com> 于2023年9月8日周五 14:06写道：

>
> On Fri, Sep 8, 2023 at 3:15 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> The example query provided here seems rather artificial. Surely few
>> people write a join clause that references neither of the tables being
>> joined. Is there a more realistic case where this makes a big
>> difference?
>
>
> Yes the given example query is not that convincing. I tried a query
> with plans as below (after some GUC setting) which might be more
> realistic in real world.
>
> unpatched:
>
> explain select * from partsupp join lineitem on l_partkey > ps_partkey;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------
> Gather (cost=0.00..5522666.44 rows=160466667 width=301)
> Workers Planned: 4
> -> Nested Loop (cost=0.00..5522666.44 rows=40116667 width=301)
> Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
> -> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
> width=144)
> -> Seq Scan on partsupp (cost=0.00..267.00 rows=8000 width=157)
> (6 rows)
>
> patched:
>
> explain select * from partsupp join lineitem on l_partkey > ps_partkey;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------
> Gather (cost=0.00..1807085.44 rows=160466667 width=301)
> Workers Planned: 4
> -> Nested Loop (cost=0.00..1807085.44 rows=40116667 width=301)
> Join Filter: (lineitem.l_partkey > partsupp.ps_partkey)
> -> Parallel Seq Scan on lineitem (cost=0.00..1518.44 rows=15044
> width=144)
> -> Materialize (cost=0.00..307.00 rows=8000 width=157)
> -> Seq Scan on partsupp (cost=0.00..267.00 rows=8000
> width=157)
> (7 rows)
>
> The execution time (ms) are (avg of 3 runs):
>
> unpatched: 71769.21
> patched: 65510.04
>
> So we can see some (~9%) performance gains in this case.
>
> Thanks
> Richard
>