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
Attachment:
W_PC_PolicySearchByFirstAndLastNameWithANYARRAYInsteadOfINSubqueryRedacted.json
Description: application/json
