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@gmail.com> 于2023年9月8日周五 14:06写道：

>
> On Fri, Sep 8, 2023 at 3:15 AM Robert Haas <robertmhaas@gmail.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
>