Which side of a Merge Join gets executed first? Do both sides always get executed?
The attached query plan is from 11.
We are getting Merge Joins on both sides of the UNION. In both cases, the first node under the Merge Join returns 0 rows but the other side of the Merge Join (the one being sorted) is executed and that's where all of the time is spent.
On the surface, I don't see any way from the attached explain plan to determine which side of the Merge Join is executed first. Some questions:
- Which side gets executed first?
- How would one tell that from the json?
- Have there been any relevant changes to later releases to make that more apparent?
- Whichever side gets executed first, is the execution of the side that would be second get short circuited if 0 rows are returned by the first side?
Here's a screenshot from pgMustard.
- Nodes 6 and 14 (the first node under each of the Merge Joins) each return 0 rows
- Nodes 9 and 15 are the expensive sides of the Merge Joins and return lots of rows
NOTE:
- The query plan in 13 is slightly different, but still includes the Merge Joins.
- Replacing ANY(ARRAY(<subquery)) with IN(<subquery>) fixes the performance problem, but we'd still like to understand the execution characteristics of Merge Join
Thanks,
Jerry
Вложения
В списке pgsql-performance по дате отправления:
Следующее
От: Frédéric YhuelДата:
Сообщение: Re: Which side of a Merge Join gets executed first? Do both sides always get executed?