Re: Which side of a Merge Join gets executed first? Do both sides always get executed?

Thanks. Does this make sense?

- There are 3 nodes under the Merge Join
- The first node is an InitPlan, due to the ANY(ARRAY()) - that gets
executed and finds 0 matching rows
- The second node is the outer node in the Merge Join and that is the
expensive node in our query plan
- The third node is the inner node in the Merge Join and that node
references the SubPlan generated by the first node. The IndexCond has*
"id = ANY($2) AND ..."* and the comparison with the result of the
SubPlan does not find a match, so that's where the short-circuiting happens.

Here are the relevant lines from the node (12) accessing the result of the
SubPlan:

"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name":
"policyperi_u_id_1mw8mh83lyyd9",
"Relation Name": "pc_policyperiod",
"Alias": "qroots0",
"Startup Cost": 0.69,
"Total Cost": 18.15,
"Plan Rows": 10,
"Plan Width": 8,
"Actual Startup Time": 0.045,
"Actual Total Time": 0.045,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(*(id = ANY ($2)) AND*
(retired = 0) AND (temporarybranch = false))",

Here's the screenshot again:

[image: image.png]

Thanks,
Jerry

On Wed, Dec 20, 2023 at 10:32 AM Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com>
wrote:

>
>
> Le 20/12/2023 à 15:40, Jerry Brenner a écrit :
> > 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?
>
> Indeed, if 0 rows are returned from the outer relation, the scan of the
> inner relation is never executed.
>
> Best regards,
> Frédéric
>
>