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 [image: image.png] 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
W_PC_PolicySearchByFirstAndLastNameWithANYARRAYInsteadOfINSubqueryRedacted.json
Description: application/json