Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
| От | Richard Guo |
|---|---|
| Тема | Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() |
| Дата | |
| Msg-id | CAMbWs49agQ1THL0XYv_4aeZXN2R1cRksevja05n-MFOzZtNsuA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() (Robert Haas <robertmhaas@gmail.com>) |
| Ответы |
Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop() |
| Список | pgsql-hackers |
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
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
В списке pgsql-hackers по дате отправления: