Message-ID: <d10b91e6-76eb-4a6c-bb17-c5736577f5ea@gmail.com>
Date: Fri, 10 Oct 2025 11:28:44 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Question about nested loops..
To: Frits Jalvingh <jal@etc.to>
References: 
 <CAKhTGFURW_AvZgWR20x-kWfSeGts=OWcuOnanhM-dwU9RjiRPw@mail.gmail.com>
 <cf8b21ef-42db-45f0-b71d-6f5f2d1673a0@gmail.com>
 <CAKhTGFW-KL0Fz8z5HrORtLh8YG1as=P1LZhG84U-HQ+ca77Azg@mail.gmail.com>
Content-Language: en-US
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAKhTGFW-KL0Fz8z5HrORtLh8YG1as=P1LZhG84U-HQ+ca77Azg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/d10b91e6-76eb-4a6c-bb17-c5736577f5ea%40gmail.com>
Precedence: bulk

On 10/10/2025 10:51, Frits Jalvingh wrote:
> Hi Andrei,
> Thanks a lot for your response. I do not fully get it though because the 
> well-performing query also references the outer (external?) relation:
> -> where eenheid.id_h_eenheid = eenheid_s.id_h_eenheid
> But your response made me think a but more.This is the schema of EXPLAIN for the second 'optimal' query:

Seq Scan on s_h_eenheid_ssm D
   SubPlan 2
     ->  Hash Join
           Hash Cond: (A.x = B.x)
           ->  Seq Scan on A
                 Filter: ...
           ->  Hash
                 ->  Hash Join
                       Hash Cond: (C.y = B.x)
                       ->  Seq Scan on s_h_huurovereenkomst_ssm C
                       ->  Hash
                             ->  Seq Scan on s_h_eenheid_ssm B

There is no reference to the upper relation D - each HashJoin refers to 
its left and right side only.

-- regards, Andrei Lepikhov, pgEdge