Hi tom, Do you have any comments or suggestions on this issue? Thanks. Richard Guo <guofengli...@gmail.com> 于2023年9月8日周五 14:06写道：
> > On Fri, Sep 8, 2023 at 3:15 AM Robert Haas <robertmh...@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 >
