Message-ID: <7a5deb77-3607-4252-be85-63ed0093d9fb@dalibo.com>
Date: Wed, 20 Dec 2023 19:05:40 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Which side of a Merge Join gets executed first? Do both sides
 always get executed?
Content-Language: fr, en-US
To: Jerry Brenner <jbrenner@guidewire.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CACoKFYQLgfOYnEGKytSZOQ7hrpdcGgk0jn22jRsZCU9aChUnCw@mail.gmail.com>
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAl6G6CYCGwMFCQeE3WIFCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNWH2wEA/YqoX3f4vTCknwEQgJtqWmcahkMKeusqdddqAB/jclQBAIKj3+N8
 fj3S9ZASdfUUz+PoPBvuDSSTdOqwfjX9hZkNzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAl596uUFCQeE3WIACgkQ1XGWhN8zGNVjGgEA8sKopTosiMWP+pbyf4jF
 5WphrhyQf+EBlKIAs0eOpOABAMFDjT7cnlc72KVZd79jT3zmEoVq+IUW5QhcW03LnhoN
In-Reply-To: 
 <CACoKFYQLgfOYnEGKytSZOQ7hrpdcGgk0jn22jRsZCU9aChUnCw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7a5deb77-3607-4252-be85-63ed0093d9fb%40dalibo.com>
Precedence: bulk


Le 20/12/2023 à 15:40, Jerry Brenner a écrit :
> 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

I think those nodes (9 and 15) are expensive because they have to filter 
out 8 millions rows in order to produce their first output row. After 
that, they get short circuited.

Best regards,
Frédéric