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
PostgreSQL version is 11.5, I have run analyze on all the tables.
PG settings:
name |setting |unit|
-------------------------------|---------|----|
autovacuum |on | |
default_statistics_target |250 | |
effective_cache_size |983040 |8kB |
effective_io_concurrency |200 | |
max_parallel_workers |6 | |
max_parallel_workers_per_gather|3 | |
random_page_cost |1.1 | |
work_mem |51200 |kB |
|
