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

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