Hash Join over Nested Loop

Luís Roberto Weck <[email protected]> Fri, 22 Nov 2019 14:33:29 -0300
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <[email protected]>
Hey,

I'm trying to figure out why Postgres is choosing a Hash Join over a 
Nested Loop in this query:

SELECT T1.PesID, T1.PesNom, T1.PesValSal, T1.PesCPFCNPJ, T2.CarAti, 
T1.CarCod, T1.EmpCod,
        T2.CarFan, T1.PesDatAge, T1.PesCod, COALESCE( T3.PesDatAnt, DATE 
'00010101') AS PesDatAnt
   FROM ((public.Pessoa T1
             INNER JOIN public.Carteira T2 ON T2.EmpCod = T1.EmpCod AND 
T2.CarCod = T1.CarCod)
              LEFT JOIN  (SELECT MIN(COALESCE( T5.ConVenAnt, DATE 
'00010101')) AS PesDatAnt, T4.EmpCod, T4.CarCod, T4.ConPesCod AS ConPesCod
                            FROM (public.Contrato T4
                                  LEFT JOIN  (SELECT MIN(ConParDatVen) 
AS ConVenAnt, EmpCod, CarCod, ConPesCod, ConSeq
                                                FROM 
public.ContratoParcela T5
                                               WHERE ConParAti = true
                                                 AND ConParValSal > 0
GROUP BY EmpCod, CarCod, ConPesCod, ConSeq ) T5 ON T5.EmpCod    = 
T4.EmpCod    AND
                                  T5.CarCod    = T4.CarCod    AND
                                  T5.ConPesCod = T4.ConPesCod AND
                                  T5.ConSeq    = T4.ConSeq)
                           WHERE T4.ConAti = TRUE
GROUP BY T4.EmpCod, T4.CarCod, T4.ConPesCod ) T3 ON t3.EmpCod    = 
T1.EmpCod AND
               t3.CarCod    = T1.CarCod AND
               t3.ConPesCod = T1.PesCod)
  WHERE (T2.CarAti = true)
    AND (T1.EmpCod = 112)
    and (UPPER(T1.PesNom) like UPPER('%MARIA%'))
  ORDER BY T1.EmpCod, T1.CarCod, T1.PesCod

Here the Hash Join[1] plan takes ~700ms, and if I change the first LEFT 
JOIN to a LEFT JOIN LATERAL, forcing a nested loop, the query[2] runs in 
3ms.

[1] https://explain.depesz.com/s/8IL3
[2] https://explain.depesz.com/s/f8Q9****