Thread

Re: Question about nested loops..

Andrei Lepikhov <lepihov@gmail.com> — 2025-10-10T09:28:44Z

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