Hash Join over Nested Loop

Luís Roberto Weck <luisroberto@xxxxxxxxxxxxxxx> · Fri, 22 Nov 2019 14:33:29 -0300



    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