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.
