pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres using nested loops despite setting enable_nestloop to false
9+ messages / 3 participants
[nested] [flat]
* Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 13:47 Frits Jalvingh <jal@etc.to>
0 siblings, 1 reply; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-17 13:47 UTC (permalink / raw)
To: pgsql-performance
Hi list,
We have an application that generates SQL statements that are then executed
on a postgresql database. The statements are always "bulk" type statements:
they always return a relatively large amount of data, and have only a few
not very selective filter expressions. They do contain a terrible amount of
joins, though.
The database has a "datavault" structure consisting of satellite, hub and
link tables. Tables can easily contain a large amount of rows (10..100
million). The individual tables have primary key constraints but no
referential constraints, and the only indexes present are those for the
primary key constraints. There are also no indices on any other column. The
reason for this is that everything is done in this database to get the
highest performance possible for both loading data and querying it for our
specific purpose, and indices do not help with that at all (they are never
used by the planner because the conditions are never selective enough).
One problem we have with these queries is that Postgresql's planner often
badly underestimates the number of rows returned by query steps. It then
uses nested loops for merging parts because it estimated it needs to loop
only a few times, but in reality it needs to loop 10 million times, and
that tends to not finish in any reasonable time ;)
Considering the type of query we do we can safely say that using a nested
loop is always a bad choice, and so we always run these statements after
setting enable_nestloop to false. This has greatly helped the stability of
these queries.
But lately while migrating to Postgres 13 (from 9.6) we found that Postgres
does not (always) obey the enable_nestloop = false setting anymore: some
queries make a plan that contains a nested loop, and consequently they do
not finish anymore. Whether a nested loop is being generated still seems to
depend on the database's actual statistics; on some databases it uses the
nested loop while on others (that use the exact same schema but have
different data in them) it uses only hash and merge joins- as it should.
What can I do to prevent these nested loops from occurring?
FYI: an example query in a datavault database:
select
coalesce(adres_pe.id_s, -1) as adres_id
, coalesce(tijd.tijdkey, 'Unknown') as calender_id
, coalesce(di01905cluster_pe.id_s, -1) as di01905cluster_id
, coalesce(di02697relatie_pe.id_s, -1) as di02697relatie_id
, coalesce(di04238cluster_pe.id_s, -1) as di04238cluster_id
, coalesce(di04306natuurlijkpersoon_pe.id_s, -1) as
di04306natuurlijkpersoon_id
, coalesce(eenheid_pe.id_s, -1) as eenheid_id
, coalesce(huurovereenkomst_pe.id_s, -1) as huurovereenkomst_id
, cast(count(huurovereenkomst_pe.identificatie) as bigint) as kg00770
from datavault.tijd tijd
cross join lateral (select * from datavault.s_h_huurovereenkomst_ssm where
dv_start_dts <= tijd.einddatum and dv_end_dts > tijd.einddatum)
huurovereenkomst_pe
inner join datavault.l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe
on huurovereenkomst_pe.id_h_huurovereenkomst =
l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
and l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum
and l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum
inner join datavault.s_h_overeenkomst_ssm overeenkomst_pe
on l_huurovk_ovk_ssm_pe.id_h_overeenkomst =
overeenkomst_pe.id_h_overeenkomst
and overeenkomst_pe.dv_start_dts <= tijd.einddatum
and overeenkomst_pe.dv_end_dts > tijd.einddatum
left join datavault.l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe
on huurovereenkomst_pe.id_h_huurovereenkomst =
l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst
and l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum
and l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum
left join datavault.s_h_eenheid_ssm eenheid_pe
on l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid
and eenheid_pe.dv_start_dts <= tijd.einddatum
and eenheid_pe.dv_end_dts > tijd.einddatum
left join datavault.l_adres_eenheid_ssm l_adres_eenheid_ssm_pe
on l_huurovk_eenheid_ssm_pe.id_h_eenheid =
l_adres_eenheid_ssm_pe.id_h_eenheid
and l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum
and l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum
left join datavault.s_h_adres_ssm adres_pe
on l_adres_eenheid_ssm_pe.id_h_adres = adres_pe.id_h_adres
and adres_pe.dv_start_dts <= tijd.einddatum
and adres_pe.dv_end_dts > tijd.einddatum
left join lateral (select
l_cluster_eenheid_ssm.id_h_eenheid
, di01905cluster.id_s
from datavault.l_cluster_eenheid_ssm
inner join datavault.s_h_cluster_ssm di01905cluster
on l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster
and di01905cluster.dv_start_dts <= tijd.einddatum
and di01905cluster.dv_end_dts > tijd.einddatum
where di01905cluster.soort = 'FIN'
) di01905cluster_pe
on l_huurovk_eenheid_ssm_pe.id_h_eenheid =
di01905cluster_pe.id_h_eenheid
left join lateral (select
l_ovk_ovkrel_ssm.id_h_overeenkomst
, di02697relatie.id_s
from datavault.l_ovk_ovkrel_ssm
inner join datavault.l_ovkrel_rel_ssm l_ovkrel_rel_ssm
on l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie =
l_ovkrel_rel_ssm.id_h_overeenkomstrelatie
inner join datavault.l_huurovk_ovk_ssm l_huurovk_ovk_ssm
on l_ovk_ovkrel_ssm.id_h_overeenkomst =
l_huurovk_ovk_ssm.id_h_overeenkomst
inner join s_h_huurovereenkomst_ssm huurovereenkomst_pe
on l_huurovk_ovk_ssm.id_h_huurovereenkomst =
huurovereenkomst_pe.id_h_huurovereenkomst
and huurovereenkomst_pe.dv_start_dts <= tijd.einddatum
and huurovereenkomst_pe.dv_end_dts > tijd.einddatum
inner join datavault.s_h_relatie_ssm di02697relatie
on l_ovkrel_rel_ssm.id_h_relatie = di02697relatie.id_h_relatie
and di02697relatie.dv_start_dts <= tijd.einddatum
and di02697relatie.dv_end_dts > tijd.einddatum
left join datavault.mv_ve0269801 ve02698
on ve02698.calender_id = coalesce(tijd.tijdkey, 'Unknown')
and di02697relatie.identificatie = VE02698.VE02698
and ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s
where VE02698.VE02698 is not null
) di02697relatie_pe
on l_huurovk_ovk_ssm_pe.id_h_overeenkomst =
di02697relatie_pe.id_h_overeenkomst
left join lateral (select
l_cluster_eenheid_ssm.id_h_eenheid
, di04238cluster.id_s
from datavault.l_cluster_eenheid_ssm
inner join datavault.s_h_cluster_ssm di04238cluster
on l_cluster_eenheid_ssm.id_h_cluster = di04238cluster.id_h_cluster
and di04238cluster.dv_start_dts <= tijd.einddatum
and di04238cluster.dv_end_dts > tijd.einddatum
where di04238cluster.soort = 'OND'
) di04238cluster_pe
on l_huurovk_eenheid_ssm_pe.id_h_eenheid =
di04238cluster_pe.id_h_eenheid
left join lateral (select
l_ovk_ovkrel_ssm.id_h_overeenkomst
, di04306natuurlijkpersoon.id_s
from datavault.l_ovk_ovkrel_ssm
inner join datavault.l_ovkrel_rel_ssm l_ovkrel_rel_ssm
on l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie =
l_ovkrel_rel_ssm.id_h_overeenkomstrelatie
inner join datavault.l_huurovk_ovk_ssm l_huurovk_ovk_ssm
on l_ovk_ovkrel_ssm.id_h_overeenkomst =
l_huurovk_ovk_ssm.id_h_overeenkomst
inner join s_h_huurovereenkomst_ssm huurovereenkomst_pe
on l_huurovk_ovk_ssm.id_h_huurovereenkomst =
huurovereenkomst_pe.id_h_huurovereenkomst
and huurovereenkomst_pe.dv_start_dts <= tijd.einddatum
and huurovereenkomst_pe.dv_end_dts > tijd.einddatum
inner join datavault.l_natuurlijkpersoon_rel_ssm
l_natuurlijkpersoon_rel_ssm
on l_ovkrel_rel_ssm.id_h_relatie =
l_natuurlijkpersoon_rel_ssm.id_h_relatie
inner join datavault.s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon
on l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon =
di04306natuurlijkpersoon.id_h_natuurlijkpersoon
and di04306natuurlijkpersoon.dv_start_dts <= tijd.einddatum
and di04306natuurlijkpersoon.dv_end_dts > tijd.einddatum
left join datavault.mv_ve0269801 ve02698
on ve02698.calender_id = coalesce(tijd.tijdkey, 'Unknown')
and di04306natuurlijkpersoon.identificatie = VE02698.VE02698
and ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s
where VE02698.VE02698 is not null
) di04306natuurlijkpersoon_pe
on l_huurovk_ovk_ssm_pe.id_h_overeenkomst =
di04306natuurlijkpersoon_pe.id_h_overeenkomst
where huurovereenkomst_pe.soort = 'HUU'
and overeenkomst_pe.begindatum <= tijd.einddatum
and (overeenkomst_pe.einddatum >= tijd.einddatum or
overeenkomst_pe.einddatum is null)
group by coalesce(adres_pe.id_s, -1)
, coalesce(tijd.tijdkey, 'Unknown')
, coalesce(di01905cluster_pe.id_s, -1)
, coalesce(di02697relatie_pe.id_s, -1)
, coalesce(di04238cluster_pe.id_s, -1)
, coalesce(di04306natuurlijkpersoon_pe.id_s, -1)
, coalesce(eenheid_pe.id_s, -1)
, coalesce(huurovereenkomst_pe.id_s, -1)
The execution plan on Postgres 13.1:
GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
(COALESCE(tijd.tijdkey, 'Unknown'::character varying)),
(COALESCE(di01905cluster.id_s, '-1'::integer)),
(COALESCE(di02697relatie_pe.id_s, '-1'::integer)),
(COALESCE(di04238cluster.id_s, '-1'::integer)),
(COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)),
(COALESCE(eenheid_pe.id_s, '-1'::integer)),
(COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=20008853763.07..20008853764.00 rows=370 width=81)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
(COALESCE(tijd.tijdkey, 'Unknown'::character varying)),
(COALESCE(di01905cluster.id_s, '-1'::integer)),
(COALESCE(di02697relatie_pe.id_s, '-1'::integer)),
(COALESCE(di04238cluster.id_s, '-1'::integer)),
(COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)),
(COALESCE(eenheid_pe.id_s, '-1'::integer)),
(COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Nested Loop Left Join (cost=20000106618.94..20008853747.29
rows=370 width=81)
Join Filter: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst =
l_ovk_ovkrel_ssm_1.id_h_overeenkomst)
-> Merge Left Join (cost=10000096634.62..10000097034.06
rows=370 width=60)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid =
l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <=
tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Merge Left Join
(cost=10000091816.99..10000092215.26 rows=370 width=60)
Merge Cond:
(l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <=
tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join
(cost=10000087694.35..10000087954.18 rows=370 width=56)
Merge Cond:
(l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts
<= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Sort
(cost=10000078369.96..10000078370.88 rows=370 width=52)
Sort Key:
l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Merge Join
(cost=10000077852.39..10000078354.17 rows=370 width=52)
Merge Cond:
(l_huurovk_ovk_ssm_pe.id_h_overeenkomst = overeenkomst_pe.id_h_overeenkomst)
Join Filter:
((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND
(overeenkomst_pe.dv_end_dts > tijd.einddatum) AND
(overeenkomst_pe.begindatum <= tijd.einddatum) AND
((overeenkomst_pe.einddatum >= tijd.einddatum) OR
(overeenkomst_pe.einddatum IS NULL)))
-> Sort
(cost=10000073751.26..10000073783.05 rows=12715 width=52)
Sort Key:
l_huurovk_ovk_ssm_pe.id_h_overeenkomst
-> Hash Right Join
(cost=10000068896.35..10000072884.46 rows=12715 width=52)
Hash Cond:
(adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter:
((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts >
tijd.einddatum))
-> Seq Scan on
s_h_adres_ssm adres_pe (cost=0.00..3424.19 rows=99519 width=24)
-> Hash
(cost=10000068737.41..10000068737.41 rows=12715 width=52)
-> Merge
Left Join (cost=10000068351.17..10000068737.41 rows=12715 width=52)
Merge
Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid =
l_adres_eenheid_ssm_pe.id_h_eenheid)
Join
Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND
(l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
->
Sort (cost=10000065526.53..10000065558.32 rows=12715 width=48)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10000063619.26..10000064659.74 rows=12715
width=48)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst =
s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum)
AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe
(cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=10000063460.32..10000063460.32 rows=12715 width=48)
-> Merge Join (cost=10000060987.75..10000063460.32 rows=12715
width=48)
Merge Cond: (s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
= l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <=
tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=4368.09..4460.04 rows=36782 width=45)
Sort Key:
s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm
(cost=0.00..1578.78 rows=36782 width=45)
Filter: (soort = 'HUU'::text)
-> Sort (cost=10000056619.67..10000056905.75 rows=114433
width=23)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Nested Loop (cost=10000022065.79..10000047004.92
rows=114433 width=23)
-> Seq Scan on tijd (cost=0.00..1.28 rows=28
width=11)
-> Hash Left Join (cost=22065.79..22915.56
rows=4087 width=28)
Hash Cond:
(l_huurovk_ovk_ssm_pe.id_h_overeenkomst =
di02697relatie_pe.id_h_overeenkomst)
Filter:
((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND
(l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm
l_huurovk_ovk_ssm_pe (cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=22065.78..22065.78 rows=1
width=8)
-> Subquery Scan on
di02697relatie_pe (cost=8384.39..22065.78 rows=1 width=8)
-> Hash Join
(cost=8384.39..22065.77 rows=1 width=8)
Hash Cond:
((l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm.id_h_overeenkomst)
AND (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s))
-> Hash Join
(cost=5710.64..19380.86 rows=1487 width=12)
Hash Cond:
(ve02698.ve02698 = di02697relatie.identificatie)
-> Seq Scan on
mv_ve0269801 ve02698 (cost=0.00..13559.11 rows=25663 width=15)
Filter:
((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey,
'Unknown'::character varying))::text))
-> Hash
(cost=5645.49..5645.49 rows=5212 width=18)
-> Hash
Join (cost=4509.43..5645.49 rows=5212 width=18)
Hash
Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie =
l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
->
Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..908.05 rows=46905 width=8)
->
Hash (cost=4444.28..4444.28 rows=5212 width=18)
-> Hash Join (cost=3308.22..4444.28 rows=5212 width=18)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie =
di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..908.05 rows=46905
width=8)
-> Hash (cost=3183.28..3183.28 rows=9995 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie
(cost=0.00..3183.28 rows=9995 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts
> tijd.einddatum))
-> Hash
(cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join
(cost=1721.82..2612.44 rows=4087 width=8)
Hash Cond:
(l_huurovk_ovk_ssm.id_h_huurovereenkomst =
huurovereenkomst_pe.id_h_huurovereenkomst)
-> Seq
Scan on l_huurovk_ovk_ssm (cost=0.00..711.82 rows=36782 width=8)
-> Hash
(cost=1670.73..1670.73 rows=4087 width=8)
->
Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe
(cost=0.00..1670.73 rows=4087 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
->
Sort (cost=2824.63..2899.95 rows=30128 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe
(cost=0.00..583.28 rows=30128 width=24)
-> Sort
(cost=4101.13..4193.09 rows=36782 width=28)
Sort Key:
overeenkomst_pe.id_h_overeenkomst
-> Seq Scan on
s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..1311.82 rows=36782
width=28)
-> Sort (cost=9324.37..9451.80 rows=50973
width=24)
Sort Key:
l_cluster_eenheid_ssm.id_h_eenheid
-> Hash Join (cost=168.45..5338.93
rows=50973 width=24)
Hash Cond:
(l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on
l_cluster_eenheid_ssm (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05
rows=1072 width=24)
-> Seq Scan on
s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort =
'FIN'::text)
-> Sort (cost=4122.63..4197.95 rows=30128
width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe
(cost=0.00..1881.28 rows=30128 width=24)
-> Sort (cost=4817.63..4817.75 rows=48 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Hash Join (cost=155.06..4816.29 rows=48
width=24)
Hash Cond:
(l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm
l_cluster_eenheid_ssm_1 (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05 rows=1
width=24)
-> Seq Scan on s_h_cluster_ssm
di04238cluster (cost=0.00..155.05 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Hash Join (cost=9984.32..23666.77 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm_1.id_h_overeenkomst =
l_huurovk_ovk_ssm_1.id_h_overeenkomst) AND (ve02698_1.huurovereenkomst_id =
huurovereenkomst_pe_1.id_s))
-> Hash Join (cost=7310.58..20981.40 rows=1548
width=12)
Hash Cond: (ve02698_1.ve02698 =
di04306natuurlijkpersoon.identificatie)
-> Seq Scan on mv_ve0269801 ve02698_1
(cost=0.00..13559.11 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND
((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character
varying))::text))
-> Hash (cost=7245.44..7245.44 rows=5211
width=18)
-> Hash Join (cost=6109.38..7245.44
rows=5211 width=18)
Hash Cond:
(l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie =
l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm
l_ovk_ovkrel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=6044.25..6044.25
rows=5211 width=18)
-> Hash Join
(cost=4908.18..6044.25 rows=5211 width=18)
Hash Cond:
(l_ovkrel_rel_ssm_1.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on
l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash
(cost=4797.20..4797.20 rows=8879 width=18)
-> Hash Join
(cost=2862.60..4797.20 rows=8879 width=18)
Hash Cond:
(l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon =
di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan
on l_natuurlijkpersoon_rel_ssm (cost=0.00..1546.13 rows=79913 width=8)
-> Hash
(cost=2742.64..2742.64 rows=9597 width=18)
-> Seq
Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon
(cost=0.00..2742.64 rows=9597 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join (cost=1721.82..2612.44 rows=4087
width=8)
Hash Cond:
(l_huurovk_ovk_ssm_1.id_h_huurovereenkomst =
huurovereenkomst_pe_1.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm
l_huurovk_ovk_ssm_1 (cost=0.00..711.82 rows=36782 width=8)
-> Hash (cost=1670.73..1670.73 rows=4087
width=8)
-> Seq Scan on
s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..1670.73
rows=4087 width=8)
Filter: ((dv_start_dts <=
tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
JIT:
Functions: 249
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Execution plan in graphical mode: https://controlc.com/95d76625 (save file
as html, then open in a browser).
Thanks a lot for your time and help.
Regards,
Frits
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 14:20 Justin Pryzby <pryzby@telsasoft.com>
parent: Frits Jalvingh <jal@etc.to>
0 siblings, 1 reply; 9+ messages in thread
From: Justin Pryzby @ 2020-11-17 14:20 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; +Cc: pgsql-performance
On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote:
> But lately while migrating to Postgres 13 (from 9.6) we found that Postgres
> does not (always) obey the enable_nestloop = false setting anymore: some
>
> The execution plan on Postgres 13.1:
Could you send the plans under pg13 and pg9.6 as attachments ?
What is the setting of work_mem ?
I see the cost is dominated by 2*disable_cost, but I wonder whether the I/O
cost of hash joins now exceeds that. Maybe hash_mem_multiplier helps you?
GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
--
Justin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 15:58 Frits Jalvingh <jal@etc.to>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 3 replies; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-17 15:58 UTC (permalink / raw)
To: pgsql-performance
Hi Justin, thanks for your help!
I have attached both plans, both made with set enable_nestloop = false in
the attachments.
On the Postgresql 13 server work_mem is 64MB. It cannot really be higher
there because Postgresql does not control its use of memory, setting it
higher on this VM will cause the OOM killer to kill Postgresql for some
queries.
On the Postgres 9.6 server we have it way higher, at 5GB (this machine is a
monster with about 800GB of RAM).
I indeed saw too that the artificial cost for the nested join led to 2x
that amount. But that seems to be because there are actually 2 nested joins
in there: we use a cross join with a "time" table (which contains just some
28 rows) and that one always seems to need a nested loop (it is present
always). So I'm not too certain that that 2x disable_cost is from joins; it
seems to be from 2x the nested loop. And I actually wondered whether that
would be a cause of the issue, because as far as costs are concerned that
second nested loops only _increases_ the cost by 2 times...
Regards,
Frits
On Tue, Nov 17, 2020 at 3:20 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote:
> > But lately while migrating to Postgres 13 (from 9.6) we found that
> Postgres
> > does not (always) obey the enable_nestloop = false setting anymore: some
> >
> > The execution plan on Postgres 13.1:
>
> Could you send the plans under pg13 and pg9.6 as attachments ?
>
> What is the setting of work_mem ?
>
> I see the cost is dominated by 2*disable_cost, but I wonder whether the I/O
> cost of hash joins now exceeds that. Maybe hash_mem_multiplier helps you?
>
> GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
>
>
> Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
>
>
>
>
> --
> Justin
>
>
>
GroupAggregate (cost=20008853763.12..20008853776.07 rows=370 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=20008853763.12..20008853764.05 rows=370 width=81)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Nested Loop Left Join (cost=20000106618.91..20008853747.34 rows=370 width=81)
Join Filter: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = l_ovk_ovkrel_ssm_1.id_h_overeenkomst)
-> Merge Left Join (cost=10000096634.59..10000097034.11 rows=370 width=60)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000091816.96..10000092215.31 rows=370 width=60)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000087694.33..10000087954.25 rows=370 width=56)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000078369.96..10000078370.89 rows=370 width=52)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Merge Join (cost=10000077852.39..10000078354.18 rows=370 width=52)
Merge Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = overeenkomst_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Sort (cost=10000073751.26..10000073783.05 rows=12715 width=52)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_overeenkomst
-> Hash Right Join (cost=10000068896.36..10000072884.47 rows=12715 width=52)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..3424.19 rows=99519 width=24)
-> Hash (cost=10000068737.42..10000068737.42 rows=12715 width=52)
-> Merge Left Join (cost=10000068351.16..10000068737.42 rows=12715 width=52)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000065526.53..10000065558.32 rows=12715 width=48)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10000063619.26..10000064659.74 rows=12715 width=48)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=10000063460.32..10000063460.32 rows=12715 width=48)
-> Merge Join (cost=10000060987.75..10000063460.32 rows=12715 width=48)
Merge Cond: (s_h_huurovereenkomst_ssm.id_h_huurovereenkomst = l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=4368.09..4460.04 rows=36782 width=45)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..1578.78 rows=36782 width=45)
Filter: (soort = 'HUU'::text)
-> Sort (cost=10000056619.67..10000056905.75 rows=114433 width=23)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Nested Loop (cost=10000022065.79..10000047004.92 rows=114433 width=23)
-> Seq Scan on tijd (cost=0.00..1.28 rows=28 width=11)
-> Hash Left Join (cost=22065.79..22915.56 rows=4087 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=22065.78..22065.78 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=8384.39..22065.78 rows=1 width=8)
-> Hash Join (cost=8384.39..22065.77 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm.id_h_overeenkomst) AND (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s))
-> Hash Join (cost=5710.64..19380.86 rows=1487 width=12)
Hash Cond: (ve02698.ve02698 = di02697relatie.identificatie)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..13559.11 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=5645.49..5645.49 rows=5212 width=18)
-> Hash Join (cost=4509.43..5645.49 rows=5212 width=18)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=4444.28..4444.28 rows=5212 width=18)
-> Hash Join (cost=3308.22..4444.28 rows=5212 width=18)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=3183.28..3183.28 rows=9995 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..3183.28 rows=9995 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join (cost=1721.82..2612.44 rows=4087 width=8)
Hash Cond: (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..711.82 rows=36782 width=8)
-> Hash (cost=1670.73..1670.73 rows=4087 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..1670.73 rows=4087 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=2824.63..2899.95 rows=30128 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..583.28 rows=30128 width=24)
-> Sort (cost=4101.13..4193.09 rows=36782 width=28)
Sort Key: overeenkomst_pe.id_h_overeenkomst
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..1311.82 rows=36782 width=28)
-> Sort (cost=9324.37..9451.80 rows=50973 width=24)
Sort Key: l_cluster_eenheid_ssm.id_h_eenheid
-> Hash Join (cost=168.45..5338.93 rows=50973 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05 rows=1072 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort = 'FIN'::text)
-> Sort (cost=4122.63..4197.95 rows=30128 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..1881.28 rows=30128 width=24)
-> Sort (cost=4817.63..4817.75 rows=48 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Hash Join (cost=155.06..4816.29 rows=48 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..155.05 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Hash Join (cost=9984.32..23666.77 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm_1.id_h_overeenkomst = l_huurovk_ovk_ssm_1.id_h_overeenkomst) AND (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s))
-> Hash Join (cost=7310.58..20981.40 rows=1548 width=12)
Hash Cond: (ve02698_1.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..13559.11 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=7245.44..7245.44 rows=5211 width=18)
-> Hash Join (cost=6109.38..7245.44 rows=5211 width=18)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=6044.25..6044.25 rows=5211 width=18)
-> Hash Join (cost=4908.18..6044.25 rows=5211 width=18)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=4797.20..4797.20 rows=8879 width=18)
-> Hash Join (cost=2862.60..4797.20 rows=8879 width=18)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..1546.13 rows=79913 width=8)
-> Hash (cost=2742.64..2742.64 rows=9597 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..2742.64 rows=9597 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join (cost=1721.82..2612.44 rows=4087 width=8)
Hash Cond: (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..711.82 rows=36782 width=8)
-> Hash (cost=1670.73..1670.73 rows=4087 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..1670.73 rows=4087 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
JIT:
Functions: 249
Options: Inlining true, Optimization true, Expressions true, Deforming true
GroupAggregate (cost=10008280752.32..10008281029.52 rows=7920 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=10008280752.32..10008280772.12 rows=7920 width=81)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Hash Right Join (cost=10008276299.12..10008280239.45 rows=7920 width=81)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..3448.57 rows=99457 width=24)
-> Hash (cost=10008276200.12..10008276200.12 rows=7920 width=87)
-> Hash Right Join (cost=10008270566.20..10008276200.12 rows=7920 width=87)
Hash Cond: (l_cluster_eenheid_ssm.id_h_eenheid = l_huurovk_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Hash Join (cost=168.45..5335.54 rows=50936 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..3901.53 rows=201653 width=8)
-> Hash (cost=155.05..155.05 rows=1072 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort = 'FIN'::text)
-> Hash (cost=10008270298.75..10008270298.75 rows=7920 width=87)
-> Merge Left Join (cost=10008269702.10..10008270298.75 rows=7920 width=87)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10008266881.95..10008267200.29 rows=7920 width=83)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10008262760.80..10008262800.83 rows=7920 width=79)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10008257946.19..10008257965.99 rows=7920 width=75)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10008256464.89..10008257433.32 rows=7920 width=75)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..711.73 rows=36773 width=24)
-> Hash (cost=10008256365.89..10008256365.89 rows=7920 width=75)
-> Merge Join (cost=10008254756.36..10008256365.89 rows=7920 width=75)
Merge Cond: (s_h_huurovereenkomst_ssm.id_h_huurovereenkomst = l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=4365.23..4457.16 rows=36773 width=45)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..1576.66 rows=36773 width=45)
Filter: (soort = 'HUU'::text)
-> Sort (cost=10008250391.14..10008250569.35 rows=71283 width=50)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Nested Loop (cost=10000034476.78..10008244645.28 rows=71283 width=50)
-> Seq Scan on tijd (cost=0.00..16.00 rows=600 width=38)
-> Hash Right Join (cost=34476.78..48159.17 rows=119 width=52)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
-> Hash Join (cost=9980.35..23662.73 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm.id_h_overeenkomst) AND (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s))
-> Hash Join (cost=7308.91..20979.66 rows=1548 width=12)
Hash Cond: (ve02698.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..13559.04 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=7243.78..7243.78 rows=5210 width=18)
-> Hash Join (cost=6107.87..7243.78 rows=5210 width=18)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=6042.75..6042.75 rows=5210 width=18)
-> Hash Join (cost=4906.83..6042.75 rows=5210 width=18)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=4795.86..4795.86 rows=8878 width=18)
-> Hash Join (cost=2861.42..4795.86 rows=8878 width=18)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..1546.02 rows=79902 width=8)
-> Hash (cost=2741.47..2741.47 rows=9596 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..2741.47 rows=9596 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2610.16..2610.16 rows=4086 width=8)
-> Hash Join (cost=1719.67..2610.16 rows=4086 width=8)
Hash Cond: (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..711.73 rows=36773 width=8)
-> Hash (cost=1668.60..1668.60 rows=4086 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..1668.60 rows=4086 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=24494.94..24494.94 rows=119 width=52)
-> Hash Join (cost=22963.59..24494.94 rows=119 width=52)
Hash Cond: (overeenkomst_pe.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..1311.73 rows=36773 width=28)
-> Hash (cost=22912.51..22912.51 rows=4086 width=28)
-> Hash Left Join (cost=22062.87..22912.51 rows=4086 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..711.73 rows=36773 width=24)
-> Hash (cost=22062.85..22062.85 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=8381.54..22062.85 rows=1 width=8)
-> Hash Join (cost=8381.54..22062.85 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm_1.id_h_overeenkomst = l_huurovk_ovk_ssm_1.id_h_overeenkomst) AND (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s))
-> Hash Join (cost=5710.09..19380.23 rows=1487 width=12)
Hash Cond: (ve02698_1.ve02698 = di02697relatie.identificatie)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..13559.04 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=5644.97..5644.97 rows=5210 width=18)
-> Hash Join (cost=4509.06..5644.97 rows=5210 width=18)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=4443.93..4443.93 rows=5210 width=18)
-> Hash Join (cost=3308.03..4443.93 rows=5210 width=18)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=3183.11..3183.11 rows=9993 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..3183.11 rows=9993 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2610.16..2610.16 rows=4086 width=8)
-> Hash Join (cost=1719.67..2610.16 rows=4086 width=8)
Hash Cond: (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..711.73 rows=36773 width=8)
-> Hash (cost=1668.60..1668.60 rows=4086 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..1668.60 rows=4086 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=4814.61..4814.73 rows=48 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Hash Join (cost=155.06..4813.27 rows=48 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..3901.53 rows=201653 width=8)
-> Hash (cost=155.05..155.05 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..155.05 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Sort (cost=4121.14..4196.33 rows=30079 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..1883.79 rows=30079 width=24)
-> Sort (cost=2820.14..2895.33 rows=30079 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..582.79 rows=30079 width=24)
Attachments:
[text/plain] plan13.txt (19.3K, ../../CAKhTGFUzjFaDon6cjF7NsWhDA0yXUHbhbAF66CZdaVK_v_YqVQ@mail.gmail.com/3-plan13.txt)
download | inline:
GroupAggregate (cost=20008853763.12..20008853776.07 rows=370 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=20008853763.12..20008853764.05 rows=370 width=81)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Nested Loop Left Join (cost=20000106618.91..20008853747.34 rows=370 width=81)
Join Filter: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = l_ovk_ovkrel_ssm_1.id_h_overeenkomst)
-> Merge Left Join (cost=10000096634.59..10000097034.11 rows=370 width=60)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000091816.96..10000092215.31 rows=370 width=60)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000087694.33..10000087954.25 rows=370 width=56)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000078369.96..10000078370.89 rows=370 width=52)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Merge Join (cost=10000077852.39..10000078354.18 rows=370 width=52)
Merge Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = overeenkomst_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Sort (cost=10000073751.26..10000073783.05 rows=12715 width=52)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_overeenkomst
-> Hash Right Join (cost=10000068896.36..10000072884.47 rows=12715 width=52)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..3424.19 rows=99519 width=24)
-> Hash (cost=10000068737.42..10000068737.42 rows=12715 width=52)
-> Merge Left Join (cost=10000068351.16..10000068737.42 rows=12715 width=52)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000065526.53..10000065558.32 rows=12715 width=48)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10000063619.26..10000064659.74 rows=12715 width=48)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=10000063460.32..10000063460.32 rows=12715 width=48)
-> Merge Join (cost=10000060987.75..10000063460.32 rows=12715 width=48)
Merge Cond: (s_h_huurovereenkomst_ssm.id_h_huurovereenkomst = l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=4368.09..4460.04 rows=36782 width=45)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..1578.78 rows=36782 width=45)
Filter: (soort = 'HUU'::text)
-> Sort (cost=10000056619.67..10000056905.75 rows=114433 width=23)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Nested Loop (cost=10000022065.79..10000047004.92 rows=114433 width=23)
-> Seq Scan on tijd (cost=0.00..1.28 rows=28 width=11)
-> Hash Left Join (cost=22065.79..22915.56 rows=4087 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..711.82 rows=36782 width=24)
-> Hash (cost=22065.78..22065.78 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=8384.39..22065.78 rows=1 width=8)
-> Hash Join (cost=8384.39..22065.77 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm.id_h_overeenkomst) AND (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s))
-> Hash Join (cost=5710.64..19380.86 rows=1487 width=12)
Hash Cond: (ve02698.ve02698 = di02697relatie.identificatie)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..13559.11 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=5645.49..5645.49 rows=5212 width=18)
-> Hash Join (cost=4509.43..5645.49 rows=5212 width=18)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=4444.28..4444.28 rows=5212 width=18)
-> Hash Join (cost=3308.22..4444.28 rows=5212 width=18)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=3183.28..3183.28 rows=9995 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..3183.28 rows=9995 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join (cost=1721.82..2612.44 rows=4087 width=8)
Hash Cond: (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..711.82 rows=36782 width=8)
-> Hash (cost=1670.73..1670.73 rows=4087 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..1670.73 rows=4087 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=2824.63..2899.95 rows=30128 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..583.28 rows=30128 width=24)
-> Sort (cost=4101.13..4193.09 rows=36782 width=28)
Sort Key: overeenkomst_pe.id_h_overeenkomst
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..1311.82 rows=36782 width=28)
-> Sort (cost=9324.37..9451.80 rows=50973 width=24)
Sort Key: l_cluster_eenheid_ssm.id_h_eenheid
-> Hash Join (cost=168.45..5338.93 rows=50973 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05 rows=1072 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort = 'FIN'::text)
-> Sort (cost=4122.63..4197.95 rows=30128 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..1881.28 rows=30128 width=24)
-> Sort (cost=4817.63..4817.75 rows=48 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Hash Join (cost=155.06..4816.29 rows=48 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..3904.00 rows=201800 width=8)
-> Hash (cost=155.05..155.05 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..155.05 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Hash Join (cost=9984.32..23666.77 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm_1.id_h_overeenkomst = l_huurovk_ovk_ssm_1.id_h_overeenkomst) AND (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s))
-> Hash Join (cost=7310.58..20981.40 rows=1548 width=12)
Hash Cond: (ve02698_1.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..13559.11 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=7245.44..7245.44 rows=5211 width=18)
-> Hash Join (cost=6109.38..7245.44 rows=5211 width=18)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=6044.25..6044.25 rows=5211 width=18)
-> Hash Join (cost=4908.18..6044.25 rows=5211 width=18)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..908.05 rows=46905 width=8)
-> Hash (cost=4797.20..4797.20 rows=8879 width=18)
-> Hash Join (cost=2862.60..4797.20 rows=8879 width=18)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..1546.13 rows=79913 width=8)
-> Hash (cost=2742.64..2742.64 rows=9597 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..2742.64 rows=9597 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2612.44..2612.44 rows=4087 width=8)
-> Hash Join (cost=1721.82..2612.44 rows=4087 width=8)
Hash Cond: (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..711.82 rows=36782 width=8)
-> Hash (cost=1670.73..1670.73 rows=4087 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..1670.73 rows=4087 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
JIT:
Functions: 249
Options: Inlining true, Optimization true, Expressions true, Deforming true
[text/plain] plan96.txt (21.1K, ../../CAKhTGFUzjFaDon6cjF7NsWhDA0yXUHbhbAF66CZdaVK_v_YqVQ@mail.gmail.com/4-plan96.txt)
download | inline:
GroupAggregate (cost=10008280752.32..10008281029.52 rows=7920 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=10008280752.32..10008280772.12 rows=7920 width=81)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Hash Right Join (cost=10008276299.12..10008280239.45 rows=7920 width=81)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..3448.57 rows=99457 width=24)
-> Hash (cost=10008276200.12..10008276200.12 rows=7920 width=87)
-> Hash Right Join (cost=10008270566.20..10008276200.12 rows=7920 width=87)
Hash Cond: (l_cluster_eenheid_ssm.id_h_eenheid = l_huurovk_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Hash Join (cost=168.45..5335.54 rows=50936 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..3901.53 rows=201653 width=8)
-> Hash (cost=155.05..155.05 rows=1072 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort = 'FIN'::text)
-> Hash (cost=10008270298.75..10008270298.75 rows=7920 width=87)
-> Merge Left Join (cost=10008269702.10..10008270298.75 rows=7920 width=87)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10008266881.95..10008267200.29 rows=7920 width=83)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10008262760.80..10008262800.83 rows=7920 width=79)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10008257946.19..10008257965.99 rows=7920 width=75)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10008256464.89..10008257433.32 rows=7920 width=75)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..711.73 rows=36773 width=24)
-> Hash (cost=10008256365.89..10008256365.89 rows=7920 width=75)
-> Merge Join (cost=10008254756.36..10008256365.89 rows=7920 width=75)
Merge Cond: (s_h_huurovereenkomst_ssm.id_h_huurovereenkomst = l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=4365.23..4457.16 rows=36773 width=45)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..1576.66 rows=36773 width=45)
Filter: (soort = 'HUU'::text)
-> Sort (cost=10008250391.14..10008250569.35 rows=71283 width=50)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Nested Loop (cost=10000034476.78..10008244645.28 rows=71283 width=50)
-> Seq Scan on tijd (cost=0.00..16.00 rows=600 width=38)
-> Hash Right Join (cost=34476.78..48159.17 rows=119 width=52)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
-> Hash Join (cost=9980.35..23662.73 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm.id_h_overeenkomst = l_huurovk_ovk_ssm.id_h_overeenkomst) AND (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s))
-> Hash Join (cost=7308.91..20979.66 rows=1548 width=12)
Hash Cond: (ve02698.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..13559.04 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=7243.78..7243.78 rows=5210 width=18)
-> Hash Join (cost=6107.87..7243.78 rows=5210 width=18)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=6042.75..6042.75 rows=5210 width=18)
-> Hash Join (cost=4906.83..6042.75 rows=5210 width=18)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=4795.86..4795.86 rows=8878 width=18)
-> Hash Join (cost=2861.42..4795.86 rows=8878 width=18)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..1546.02 rows=79902 width=8)
-> Hash (cost=2741.47..2741.47 rows=9596 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..2741.47 rows=9596 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2610.16..2610.16 rows=4086 width=8)
-> Hash Join (cost=1719.67..2610.16 rows=4086 width=8)
Hash Cond: (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..711.73 rows=36773 width=8)
-> Hash (cost=1668.60..1668.60 rows=4086 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..1668.60 rows=4086 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=24494.94..24494.94 rows=119 width=52)
-> Hash Join (cost=22963.59..24494.94 rows=119 width=52)
Hash Cond: (overeenkomst_pe.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..1311.73 rows=36773 width=28)
-> Hash (cost=22912.51..22912.51 rows=4086 width=28)
-> Hash Left Join (cost=22062.87..22912.51 rows=4086 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..711.73 rows=36773 width=24)
-> Hash (cost=22062.85..22062.85 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=8381.54..22062.85 rows=1 width=8)
-> Hash Join (cost=8381.54..22062.85 rows=1 width=8)
Hash Cond: ((l_ovk_ovkrel_ssm_1.id_h_overeenkomst = l_huurovk_ovk_ssm_1.id_h_overeenkomst) AND (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s))
-> Hash Join (cost=5710.09..19380.23 rows=1487 width=12)
Hash Cond: (ve02698_1.ve02698 = di02697relatie.identificatie)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..13559.04 rows=25663 width=15)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=5644.97..5644.97 rows=5210 width=18)
-> Hash Join (cost=4509.06..5644.97 rows=5210 width=18)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=4443.93..4443.93 rows=5210 width=18)
-> Hash Join (cost=3308.03..4443.93 rows=5210 width=18)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..907.95 rows=46895 width=8)
-> Hash (cost=3183.11..3183.11 rows=9993 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..3183.11 rows=9993 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=2610.16..2610.16 rows=4086 width=8)
-> Hash Join (cost=1719.67..2610.16 rows=4086 width=8)
Hash Cond: (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst)
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..711.73 rows=36773 width=8)
-> Hash (cost=1668.60..1668.60 rows=4086 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..1668.60 rows=4086 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=4814.61..4814.73 rows=48 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Hash Join (cost=155.06..4813.27 rows=48 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..3901.53 rows=201653 width=8)
-> Hash (cost=155.05..155.05 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..155.05 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Sort (cost=4121.14..4196.33 rows=30079 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..1883.79 rows=30079 width=24)
-> Sort (cost=2820.14..2895.33 rows=30079 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..582.79 rows=30079 width=24)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 16:06 Justin Pryzby <pryzby@telsasoft.com>
parent: Frits Jalvingh <jal@etc.to>
2 siblings, 0 replies; 9+ messages in thread
From: Justin Pryzby @ 2020-11-17 16:06 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; +Cc: pgsql-performance
On Tue, Nov 17, 2020 at 04:58:45PM +0100, Frits Jalvingh wrote:
> Hi Justin, thanks for your help!
> I have attached both plans, both made with set enable_nestloop = false in
> the attachments.
> On the Postgresql 13 server work_mem is 64MB. It cannot really be higher
> there because Postgresql does not control its use of memory, setting it
> higher on this VM will cause the OOM killer to kill Postgresql for some
> queries.
Can you try to get an explain just for this query with either increased
work_mem or hash_mem_multiplier ?
Or possibly by messing with the cost parameters, including seq_page_cost.
Making all cost_* params 1000x smaller might allow the disable cost to be
effective.
--
Justin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 16:08 Frits Jalvingh <jal@etc.to>
parent: Frits Jalvingh <jal@etc.to>
2 siblings, 1 reply; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-17 16:08 UTC (permalink / raw)
To: pgsql-performance
Ah, sorry, I forgot. I set "hash_mem_multiplier = 2", and after that to 20.
It did had no effects on the nested loops.
On Tue, Nov 17, 2020 at 4:58 PM Frits Jalvingh <jal@etc.to> wrote:
> Hi Justin, thanks for your help!
> I have attached both plans, both made with set enable_nestloop = false in
> the attachments.
> On the Postgresql 13 server work_mem is 64MB. It cannot really be higher
> there because Postgresql does not control its use of memory, setting it
> higher on this VM will cause the OOM killer to kill Postgresql for some
> queries.
> On the Postgres 9.6 server we have it way higher, at 5GB (this machine is
> a monster with about 800GB of RAM).
>
> I indeed saw too that the artificial cost for the nested join led to 2x
> that amount. But that seems to be because there are actually 2 nested joins
> in there: we use a cross join with a "time" table (which contains just some
> 28 rows) and that one always seems to need a nested loop (it is present
> always). So I'm not too certain that that 2x disable_cost is from joins; it
> seems to be from 2x the nested loop. And I actually wondered whether that
> would be a cause of the issue, because as far as costs are concerned that
> second nested loops only _increases_ the cost by 2 times...
>
> Regards,
>
> Frits
>
>
> On Tue, Nov 17, 2020 at 3:20 PM Justin Pryzby <pryzby@telsasoft.com>
> wrote:
>
>> On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote:
>> > But lately while migrating to Postgres 13 (from 9.6) we found that
>> Postgres
>> > does not (always) obey the enable_nestloop = false setting anymore: some
>> >
>> > The execution plan on Postgres 13.1:
>>
>> Could you send the plans under pg13 and pg9.6 as attachments ?
>>
>> What is the setting of work_mem ?
>>
>> I see the cost is dominated by 2*disable_cost, but I wonder whether the
>> I/O
>> cost of hash joins now exceeds that. Maybe hash_mem_multiplier helps you?
>>
>> GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
>>
>>
>>
>> Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
>>
>>
>>
>>
>> --
>> Justin
>>
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 16:14 Frits Jalvingh <jal@etc.to>
parent: Frits Jalvingh <jal@etc.to>
0 siblings, 0 replies; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-17 16:14 UTC (permalink / raw)
To: pgsql-performance
Ok, I set all those cost parameters:
# - Planner Cost Constants -
seq_page_cost = 0.0001 # measured on an arbitrary scale
random_page_cost = 0.0002
cpu_tuple_cost = 0.00001 # same scale as above
cpu_index_tuple_cost = 0.000005 # same scale as above
cpu_operator_cost = 0.0000025 # same scale as above
parallel_tuple_cost = 0.0001 # same scale as above
parallel_setup_cost = 1.0 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
effective_cache_size = 2GB
It still has the nested loop on top, but the total cost is now:
GroupAggregate (cost=20000005652.88..20000005652.90 rows=370 width=68)
On Tue, Nov 17, 2020 at 5:08 PM Frits Jalvingh <jal@etc.to> wrote:
> Ah, sorry, I forgot. I set "hash_mem_multiplier = 2", and after that to
> 20. It did had no effects on the nested loops.
>
> On Tue, Nov 17, 2020 at 4:58 PM Frits Jalvingh <jal@etc.to> wrote:
>
>> Hi Justin, thanks for your help!
>> I have attached both plans, both made with set enable_nestloop = false in
>> the attachments.
>> On the Postgresql 13 server work_mem is 64MB. It cannot really be higher
>> there because Postgresql does not control its use of memory, setting it
>> higher on this VM will cause the OOM killer to kill Postgresql for some
>> queries.
>> On the Postgres 9.6 server we have it way higher, at 5GB (this machine is
>> a monster with about 800GB of RAM).
>>
>> I indeed saw too that the artificial cost for the nested join led to 2x
>> that amount. But that seems to be because there are actually 2 nested joins
>> in there: we use a cross join with a "time" table (which contains just some
>> 28 rows) and that one always seems to need a nested loop (it is present
>> always). So I'm not too certain that that 2x disable_cost is from joins; it
>> seems to be from 2x the nested loop. And I actually wondered whether that
>> would be a cause of the issue, because as far as costs are concerned that
>> second nested loops only _increases_ the cost by 2 times...
>>
>> Regards,
>>
>> Frits
>>
>>
>> On Tue, Nov 17, 2020 at 3:20 PM Justin Pryzby <pryzby@telsasoft.com>
>> wrote:
>>
>>> On Tue, Nov 17, 2020 at 02:47:55PM +0100, Frits Jalvingh wrote:
>>> > But lately while migrating to Postgres 13 (from 9.6) we found that
>>> Postgres
>>> > does not (always) obey the enable_nestloop = false setting anymore:
>>> some
>>> >
>>> > The execution plan on Postgres 13.1:
>>>
>>> Could you send the plans under pg13 and pg9.6 as attachments ?
>>>
>>> What is the setting of work_mem ?
>>>
>>> I see the cost is dominated by 2*disable_cost, but I wonder whether the
>>> I/O
>>> cost of hash joins now exceeds that. Maybe hash_mem_multiplier helps
>>> you?
>>>
>>> GroupAggregate (cost=20008853763.07..20008853776.02 rows=370 width=68)
>>>
>>>
>>>
>>> Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)),
>>>
>>>
>>>
>>>
>>> --
>>> Justin
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 16:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Frits Jalvingh <jal@etc.to>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2020-11-17 16:21 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; +Cc: pgsql-performance
Frits Jalvingh <jal@etc.to> writes:
> I have attached both plans, both made with set enable_nestloop = false in
> the attachments.
The reason why you're getting a nested loop is that the planner has no
other choice. The "tijd" table has no join conditions that would be
amenable to hash- or merge-joining it to something else, because both
of those join methods require a plain equality join condition. AFAICS
in a quick look, all of tijd's join conditions look more like
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
which is not amenable to anything except brute force cross-join-and-
test-the-condition.
Given that, it's likely that "enable_nestloop = false" is making things
worse not better, by artificially distorting the plan shape.
Seeing the large number of joins involved, I wonder what your
geqo_threshold, join_collapse_limit, and from_collapse_limit settings
are, and whether you can get a better plan by increasing them.
The planner doesn't seem to think that any of these joins involve
a very large number of rows, so I doubt that your work_mem setting
is very relevant. However, are these rowcount estimates accurate?
You claimed upthread that you were dealing with hundreds of millions
of rows, but it's impossible to credit that cost estimates like
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05 rows=1072 width=24)
Filter: (soort = 'FIN'::text)
correspond to scanning large tables.
In the end, I fear that finding a way to get rid of those
inequality join conditions may be your only real answer.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-17 16:42 Frits Jalvingh <jal@etc.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-17 16:42 UTC (permalink / raw)
To: pgsql-performance
Hello Tom, thanks for your help!
I understand that the "time" table cross join needs a nested loop. Indeed
that nested loop is present in all plans generated.
But it is the _second_ (topmost) nested loop that is the issue. Once the
time table has been joined it should be possible to do something else for
that second nested loop. This is proven by that query on 9.6 (which has
only one nested loop for that exact same query, on almost the same database
content as the Postgresql 13 one). Even on Postgresql 13 a correct plan is
made in another database (exact same structure, different data); I have
attached the plan that is made there too.
All databases that make a plan without the second nested loops also finish
the query within a reasonable time period (16 seconds on the .9.6 server).
On the 13 server with the nested loops plan the process times out after 2
hours.
As far as the row counts go: yes, this database is not by far the biggest
one, so the row counts are less. It also depends on what query we actually
run (we can have hundreds of them on different tables, and not all tables
are that big).
I disabled nested_loops not just for fun, I disabled it because without it
many of the queries effectively hang because their plan estimate expects
only a few rows while in reality there are millions. Disabling nested loops
will let lots of the generated queries fail, even on smaller datasets.
I have no idea of how to get rid of those inequality queries, except by not
using SQL and doing them by hand in code.. That would prove to be
disastrous for performance as I'd have to read all those datasets
completely... Do you have an idea on how to do that better?
Regards,
Frits
On Tue, Nov 17, 2020 at 5:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Frits Jalvingh <jal@etc.to> writes:
> > I have attached both plans, both made with set enable_nestloop = false in
> > the attachments.
>
> The reason why you're getting a nested loop is that the planner has no
> other choice. The "tijd" table has no join conditions that would be
> amenable to hash- or merge-joining it to something else, because both
> of those join methods require a plain equality join condition. AFAICS
> in a quick look, all of tijd's join conditions look more like
>
> Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND
> (di04238cluster.dv_end_dts > tijd.einddatum))
>
> which is not amenable to anything except brute force cross-join-and-
> test-the-condition.
>
> Given that, it's likely that "enable_nestloop = false" is making things
> worse not better, by artificially distorting the plan shape.
>
> Seeing the large number of joins involved, I wonder what your
> geqo_threshold, join_collapse_limit, and from_collapse_limit settings
> are, and whether you can get a better plan by increasing them.
>
> The planner doesn't seem to think that any of these joins involve
> a very large number of rows, so I doubt that your work_mem setting
> is very relevant. However, are these rowcount estimates accurate?
> You claimed upthread that you were dealing with hundreds of millions
> of rows, but it's impossible to credit that cost estimates like
>
> -> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05
> rows=1072 width=24)
> Filter: (soort = 'FIN'::text)
>
> correspond to scanning large tables.
>
> In the end, I fear that finding a way to get rid of those
> inequality join conditions may be your only real answer.
>
> regards, tom lane
>
GroupAggregate (cost=10000164791.63..10000164797.65 rows=172 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon_pe.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=10000164791.63..10000164792.06 rows=172 width=91)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon_pe.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Hash Right Join (cost=10000157476.98..10000164785.24 rows=172 width=91)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..6628.04 rows=180704 width=24)
-> Hash (cost=10000157474.83..10000157474.83 rows=172 width=70)
-> Hash Right Join (cost=10000139732.16..10000157474.83 rows=172 width=70)
Hash Cond: (l_cluster_eenheid_ssm.id_h_eenheid = l_huurovk_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Hash Join (cost=658.26..18097.02 rows=80259 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..13935.03 rows=720303 width=8)
-> Hash (cost=633.98..633.98 rows=1943 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..633.98 rows=1943 width=24)
Filter: (soort = 'FIN'::text)
-> Hash (cost=10000139071.75..10000139071.75 rows=172 width=70)
-> Merge Left Join (cost=10000138378.33..10000139071.75 rows=172 width=70)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000130869.09..10000131216.34 rows=172 width=66)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000120406.85..10000120407.93 rows=172 width=62)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000107908.76..10000107909.19 rows=172 width=58)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10000105474.31..10000107902.38 rows=172 width=58)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..2031.81 rows=104981 width=24)
-> Hash (cost=10000105472.16..10000105472.16 rows=172 width=58)
-> Merge Join (cost=10000104932.90..10000105472.16 rows=172 width=58)
Merge Cond: (l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000092135.67..10000092141.12 rows=2181 width=23)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Hash Join (cost=10000084162.49..10000092014.72 rows=2181 width=23)
Hash Cond: (overeenkomst_pe.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..5412.27 rows=142827 width=28)
-> Hash (cost=10000082972.26..10000082972.26 rows=95218 width=27)
-> Nested Loop (cost=10000061868.55..10000082972.26 rows=95218 width=27)
-> Seq Scan on tijd (cost=0.00..1.06 rows=6 width=11)
-> Hash Left Join (cost=61868.55..65226.96 rows=15870 width=32)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di04306natuurlijkpersoon_pe.id_h_overeenkomst)
-> Hash Left Join (cost=30022.21..33321.10 rows=15870 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..2763.27 rows=142827 width=24)
-> Hash (cost=30022.20..30022.20 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=26187.70..30022.20 rows=1 width=8)
-> Hash Join (cost=26187.70..30022.19 rows=1 width=8)
Hash Cond: ((l_huurovk_ovk_ssm.id_h_overeenkomst = l_ovk_ovkrel_ssm.id_h_overeenkomst) AND (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst))
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..2763.27 rows=142827 width=8)
-> Hash (cost=26169.18..26169.18 rows=1235 width=12)
-> Hash Join (cost=23328.58..26169.18 rows=1235 width=12)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=23313.14..23313.14 rows=1235 width=12)
-> Hash Join (cost=20472.54..23313.14 rows=1235 width=12)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=20457.39..20457.39 rows=1212 width=12)
-> Hash Join (cost=9034.62..20457.39 rows=1212 width=12)
Hash Cond: (ve02698.ve02698 = di02697relatie.identificatie)
-> Hash Join (cost=4756.53..16126.28 rows=10906 width=14)
Hash Cond: (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..11112.10 rows=98148 width=14)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=4610.72..4610.72 rows=11665 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..4610.72 rows=11665 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=4111.31..4111.31 rows=13343 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..4111.31 rows=13343 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=31846.33..31846.33 rows=1 width=8)
-> Subquery Scan on di04306natuurlijkpersoon_pe (cost=28011.84..31846.33 rows=1 width=8)
-> Hash Join (cost=28011.84..31846.32 rows=1 width=8)
Hash Cond: ((l_huurovk_ovk_ssm_1.id_h_overeenkomst = l_ovk_ovkrel_ssm_1.id_h_overeenkomst) AND (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..2763.27 rows=142827 width=8)
-> Hash (cost=27991.08..27991.08 rows=1384 width=12)
-> Hash Join (cost=25148.99..27991.08 rows=1384 width=12)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=25131.69..25131.69 rows=1384 width=12)
-> Hash Join (cost=22289.60..25131.69 rows=1384 width=12)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=22274.45..22274.45 rows=1212 width=12)
-> Hash Join (cost=19786.33..22274.45 rows=1212 width=12)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..2074.00 rows=107200 width=8)
-> Hash (cost=19771.18..19771.18 rows=1212 width=12)
-> Hash Join (cost=8348.42..19771.18 rows=1212 width=12)
Hash Cond: (ve02698_1.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Hash Join (cost=4756.53..16126.28 rows=10906 width=14)
Hash Cond: (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..11112.10 rows=98148 width=14)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=4610.72..4610.72 rows=11665 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..4610.72 rows=11665 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=3443.00..3443.00 rows=11911 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..3443.00 rows=11911 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=12797.23..13051.22 rows=101596 width=55)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..4348.26 rows=101596 width=55)
Filter: (soort = 'HUU'::text)
-> Sort (cost=12498.09..12498.19 rows=41 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Gather (cost=1633.99..12496.99 rows=41 width=24)
Workers Planned: 2
-> Hash Join (cost=633.99..11492.89 rows=17 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Parallel Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..9733.26 rows=300126 width=8)
-> Hash (cost=633.98..633.98 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..633.98 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Sort (cost=10462.23..10649.38 rows=74860 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..4401.60 rows=74860 width=24)
-> Sort (cost=7509.23..7696.38 rows=74860 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..1448.60 rows=74860 width=24)
JIT:
Functions: 254
Options: Inlining true, Optimization true, Expressions true, Deforming true
Attachments:
[text/plain] plan13_correct.txt (22.8K, ../../CAKhTGFUE-WQAD_56PP3zFbfU-MduBJSx+FAbVb14zNTn0DzvkQ@mail.gmail.com/3-plan13_correct.txt)
download | inline:
GroupAggregate (cost=10000164791.63..10000164797.65 rows=172 width=68)
Group Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon_pe.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Sort (cost=10000164791.63..10000164792.06 rows=172 width=91)
Sort Key: (COALESCE(adres_pe.id_s, '-1'::integer)), (COALESCE(tijd.tijdkey, 'Unknown'::character varying)), (COALESCE(di01905cluster.id_s, '-1'::integer)), (COALESCE(di02697relatie_pe.id_s, '-1'::integer)), (COALESCE(di04238cluster.id_s, '-1'::integer)), (COALESCE(di04306natuurlijkpersoon_pe.id_s, '-1'::integer)), (COALESCE(eenheid_pe.id_s, '-1'::integer)), (COALESCE(s_h_huurovereenkomst_ssm.id_s, '-1'::integer))
-> Hash Right Join (cost=10000157476.98..10000164785.24 rows=172 width=91)
Hash Cond: (adres_pe.id_h_adres = l_adres_eenheid_ssm_pe.id_h_adres)
Join Filter: ((adres_pe.dv_start_dts <= tijd.einddatum) AND (adres_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on s_h_adres_ssm adres_pe (cost=0.00..6628.04 rows=180704 width=24)
-> Hash (cost=10000157474.83..10000157474.83 rows=172 width=70)
-> Hash Right Join (cost=10000139732.16..10000157474.83 rows=172 width=70)
Hash Cond: (l_cluster_eenheid_ssm.id_h_eenheid = l_huurovk_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((di01905cluster.dv_start_dts <= tijd.einddatum) AND (di01905cluster.dv_end_dts > tijd.einddatum))
-> Hash Join (cost=658.26..18097.02 rows=80259 width=24)
Hash Cond: (l_cluster_eenheid_ssm.id_h_cluster = di01905cluster.id_h_cluster)
-> Seq Scan on l_cluster_eenheid_ssm (cost=0.00..13935.03 rows=720303 width=8)
-> Hash (cost=633.98..633.98 rows=1943 width=24)
-> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..633.98 rows=1943 width=24)
Filter: (soort = 'FIN'::text)
-> Hash (cost=10000139071.75..10000139071.75 rows=172 width=70)
-> Merge Left Join (cost=10000138378.33..10000139071.75 rows=172 width=70)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_adres_eenheid_ssm_pe.id_h_eenheid)
Join Filter: ((l_adres_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_adres_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000130869.09..10000131216.34 rows=172 width=66)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = eenheid_pe.id_h_eenheid)
Join Filter: ((eenheid_pe.dv_start_dts <= tijd.einddatum) AND (eenheid_pe.dv_end_dts > tijd.einddatum))
-> Merge Left Join (cost=10000120406.85..10000120407.93 rows=172 width=62)
Merge Cond: (l_huurovk_eenheid_ssm_pe.id_h_eenheid = l_cluster_eenheid_ssm_1.id_h_eenheid)
Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND (di04238cluster.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000107908.76..10000107909.19 rows=172 width=58)
Sort Key: l_huurovk_eenheid_ssm_pe.id_h_eenheid
-> Hash Right Join (cost=10000105474.31..10000107902.38 rows=172 width=58)
Hash Cond: (l_huurovk_eenheid_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((l_huurovk_eenheid_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_eenheid_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_eenheid_ssm l_huurovk_eenheid_ssm_pe (cost=0.00..2031.81 rows=104981 width=24)
-> Hash (cost=10000105472.16..10000105472.16 rows=172 width=58)
-> Merge Join (cost=10000104932.90..10000105472.16 rows=172 width=58)
Merge Cond: (l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst = s_h_huurovereenkomst_ssm.id_h_huurovereenkomst)
Join Filter: ((s_h_huurovereenkomst_ssm.dv_start_dts <= tijd.einddatum) AND (s_h_huurovereenkomst_ssm.dv_end_dts > tijd.einddatum))
-> Sort (cost=10000092135.67..10000092141.12 rows=2181 width=23)
Sort Key: l_huurovk_ovk_ssm_pe.id_h_huurovereenkomst
-> Hash Join (cost=10000084162.49..10000092014.72 rows=2181 width=23)
Hash Cond: (overeenkomst_pe.id_h_overeenkomst = l_huurovk_ovk_ssm_pe.id_h_overeenkomst)
Join Filter: ((overeenkomst_pe.dv_start_dts <= tijd.einddatum) AND (overeenkomst_pe.dv_end_dts > tijd.einddatum) AND (overeenkomst_pe.begindatum <= tijd.einddatum) AND ((overeenkomst_pe.einddatum >= tijd.einddatum) OR (overeenkomst_pe.einddatum IS NULL)))
-> Seq Scan on s_h_overeenkomst_ssm overeenkomst_pe (cost=0.00..5412.27 rows=142827 width=28)
-> Hash (cost=10000082972.26..10000082972.26 rows=95218 width=27)
-> Nested Loop (cost=10000061868.55..10000082972.26 rows=95218 width=27)
-> Seq Scan on tijd (cost=0.00..1.06 rows=6 width=11)
-> Hash Left Join (cost=61868.55..65226.96 rows=15870 width=32)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di04306natuurlijkpersoon_pe.id_h_overeenkomst)
-> Hash Left Join (cost=30022.21..33321.10 rows=15870 width=28)
Hash Cond: (l_huurovk_ovk_ssm_pe.id_h_overeenkomst = di02697relatie_pe.id_h_overeenkomst)
Filter: ((l_huurovk_ovk_ssm_pe.dv_start_dts <= tijd.einddatum) AND (l_huurovk_ovk_ssm_pe.dv_end_dts > tijd.einddatum))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_pe (cost=0.00..2763.27 rows=142827 width=24)
-> Hash (cost=30022.20..30022.20 rows=1 width=8)
-> Subquery Scan on di02697relatie_pe (cost=26187.70..30022.20 rows=1 width=8)
-> Hash Join (cost=26187.70..30022.19 rows=1 width=8)
Hash Cond: ((l_huurovk_ovk_ssm.id_h_overeenkomst = l_ovk_ovkrel_ssm.id_h_overeenkomst) AND (l_huurovk_ovk_ssm.id_h_huurovereenkomst = huurovereenkomst_pe.id_h_huurovereenkomst))
-> Seq Scan on l_huurovk_ovk_ssm (cost=0.00..2763.27 rows=142827 width=8)
-> Hash (cost=26169.18..26169.18 rows=1235 width=12)
-> Hash Join (cost=23328.58..26169.18 rows=1235 width=12)
Hash Cond: (l_ovk_ovkrel_ssm.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=23313.14..23313.14 rows=1235 width=12)
-> Hash Join (cost=20472.54..23313.14 rows=1235 width=12)
Hash Cond: (l_ovkrel_rel_ssm.id_h_relatie = di02697relatie.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=20457.39..20457.39 rows=1212 width=12)
-> Hash Join (cost=9034.62..20457.39 rows=1212 width=12)
Hash Cond: (ve02698.ve02698 = di02697relatie.identificatie)
-> Hash Join (cost=4756.53..16126.28 rows=10906 width=14)
Hash Cond: (ve02698.huurovereenkomst_id = huurovereenkomst_pe.id_s)
-> Seq Scan on mv_ve0269801 ve02698 (cost=0.00..11112.10 rows=98148 width=14)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=4610.72..4610.72 rows=11665 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe (cost=0.00..4610.72 rows=11665 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=4111.31..4111.31 rows=13343 width=18)
-> Seq Scan on s_h_relatie_ssm di02697relatie (cost=0.00..4111.31 rows=13343 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=31846.33..31846.33 rows=1 width=8)
-> Subquery Scan on di04306natuurlijkpersoon_pe (cost=28011.84..31846.33 rows=1 width=8)
-> Hash Join (cost=28011.84..31846.32 rows=1 width=8)
Hash Cond: ((l_huurovk_ovk_ssm_1.id_h_overeenkomst = l_ovk_ovkrel_ssm_1.id_h_overeenkomst) AND (l_huurovk_ovk_ssm_1.id_h_huurovereenkomst = huurovereenkomst_pe_1.id_h_huurovereenkomst))
-> Seq Scan on l_huurovk_ovk_ssm l_huurovk_ovk_ssm_1 (cost=0.00..2763.27 rows=142827 width=8)
-> Hash (cost=27991.08..27991.08 rows=1384 width=12)
-> Hash Join (cost=25148.99..27991.08 rows=1384 width=12)
Hash Cond: (l_ovk_ovkrel_ssm_1.id_h_overeenkomstrelatie = l_ovkrel_rel_ssm_1.id_h_overeenkomstrelatie)
-> Seq Scan on l_ovk_ovkrel_ssm l_ovk_ovkrel_ssm_1 (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=25131.69..25131.69 rows=1384 width=12)
-> Hash Join (cost=22289.60..25131.69 rows=1384 width=12)
Hash Cond: (l_ovkrel_rel_ssm_1.id_h_relatie = l_natuurlijkpersoon_rel_ssm.id_h_relatie)
-> Seq Scan on l_ovkrel_rel_ssm l_ovkrel_rel_ssm_1 (cost=0.00..2369.18 rows=122418 width=8)
-> Hash (cost=22274.45..22274.45 rows=1212 width=12)
-> Hash Join (cost=19786.33..22274.45 rows=1212 width=12)
Hash Cond: (l_natuurlijkpersoon_rel_ssm.id_h_natuurlijkpersoon = di04306natuurlijkpersoon.id_h_natuurlijkpersoon)
-> Seq Scan on l_natuurlijkpersoon_rel_ssm (cost=0.00..2074.00 rows=107200 width=8)
-> Hash (cost=19771.18..19771.18 rows=1212 width=12)
-> Hash Join (cost=8348.42..19771.18 rows=1212 width=12)
Hash Cond: (ve02698_1.ve02698 = di04306natuurlijkpersoon.identificatie)
-> Hash Join (cost=4756.53..16126.28 rows=10906 width=14)
Hash Cond: (ve02698_1.huurovereenkomst_id = huurovereenkomst_pe_1.id_s)
-> Seq Scan on mv_ve0269801 ve02698_1 (cost=0.00..11112.10 rows=98148 width=14)
Filter: ((ve02698 IS NOT NULL) AND ((calender_id)::text = (COALESCE(tijd.tijdkey, 'Unknown'::character varying))::text))
-> Hash (cost=4610.72..4610.72 rows=11665 width=8)
-> Seq Scan on s_h_huurovereenkomst_ssm huurovereenkomst_pe_1 (cost=0.00..4610.72 rows=11665 width=8)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Hash (cost=3443.00..3443.00 rows=11911 width=18)
-> Seq Scan on s_h_natuurlijkpersoon_ssm di04306natuurlijkpersoon (cost=0.00..3443.00 rows=11911 width=18)
Filter: ((dv_start_dts <= tijd.einddatum) AND (dv_end_dts > tijd.einddatum))
-> Sort (cost=12797.23..13051.22 rows=101596 width=55)
Sort Key: s_h_huurovereenkomst_ssm.id_h_huurovereenkomst
-> Seq Scan on s_h_huurovereenkomst_ssm (cost=0.00..4348.26 rows=101596 width=55)
Filter: (soort = 'HUU'::text)
-> Sort (cost=12498.09..12498.19 rows=41 width=24)
Sort Key: l_cluster_eenheid_ssm_1.id_h_eenheid
-> Gather (cost=1633.99..12496.99 rows=41 width=24)
Workers Planned: 2
-> Hash Join (cost=633.99..11492.89 rows=17 width=24)
Hash Cond: (l_cluster_eenheid_ssm_1.id_h_cluster = di04238cluster.id_h_cluster)
-> Parallel Seq Scan on l_cluster_eenheid_ssm l_cluster_eenheid_ssm_1 (cost=0.00..9733.26 rows=300126 width=8)
-> Hash (cost=633.98..633.98 rows=1 width=24)
-> Seq Scan on s_h_cluster_ssm di04238cluster (cost=0.00..633.98 rows=1 width=24)
Filter: (soort = 'OND'::text)
-> Sort (cost=10462.23..10649.38 rows=74860 width=24)
Sort Key: eenheid_pe.id_h_eenheid
-> Seq Scan on s_h_eenheid_ssm eenheid_pe (cost=0.00..4401.60 rows=74860 width=24)
-> Sort (cost=7509.23..7696.38 rows=74860 width=24)
Sort Key: l_adres_eenheid_ssm_pe.id_h_eenheid
-> Seq Scan on l_adres_eenheid_ssm l_adres_eenheid_ssm_pe (cost=0.00..1448.60 rows=74860 width=24)
JIT:
Functions: 254
Options: Inlining true, Optimization true, Expressions true, Deforming true
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgres using nested loops despite setting enable_nestloop to false
@ 2020-11-19 11:52 Frits Jalvingh <jal@etc.to>
parent: Frits Jalvingh <jal@etc.to>
0 siblings, 0 replies; 9+ messages in thread
From: Frits Jalvingh @ 2020-11-19 11:52 UTC (permalink / raw)
To: pgsql-performance
I found out that setting:
set join_collapse_limit = 14;
set from_collapse_limit = 14;
In addition to disabling the nested loops does produce a viable plan, with
only the nested loop to generate the tijd table cross join as a basic part
down low... The original values for those were 12. It does seem scary to
update those as the possibility of having 14! plans to choose from seems...
scary...
It does feel a bit like throwing dice...
I assume the bad plan is being made by the gequ planner. Is there a way to
discourage it from using those nested loops?
Regards,
Frits
On Tue, Nov 17, 2020 at 5:42 PM Frits Jalvingh <jal@etc.to> wrote:
> Hello Tom, thanks for your help!
>
> I understand that the "time" table cross join needs a nested loop. Indeed
> that nested loop is present in all plans generated.
> But it is the _second_ (topmost) nested loop that is the issue. Once the
> time table has been joined it should be possible to do something else for
> that second nested loop. This is proven by that query on 9.6 (which has
> only one nested loop for that exact same query, on almost the same database
> content as the Postgresql 13 one). Even on Postgresql 13 a correct plan is
> made in another database (exact same structure, different data); I have
> attached the plan that is made there too.
> All databases that make a plan without the second nested loops also finish
> the query within a reasonable time period (16 seconds on the .9.6 server).
> On the 13 server with the nested loops plan the process times out after 2
> hours.
>
> As far as the row counts go: yes, this database is not by far the biggest
> one, so the row counts are less. It also depends on what query we actually
> run (we can have hundreds of them on different tables, and not all tables
> are that big).
>
> I disabled nested_loops not just for fun, I disabled it because without it
> many of the queries effectively hang because their plan estimate expects
> only a few rows while in reality there are millions. Disabling nested loops
> will let lots of the generated queries fail, even on smaller datasets.
>
> I have no idea of how to get rid of those inequality queries, except by
> not using SQL and doing them by hand in code.. That would prove to be
> disastrous for performance as I'd have to read all those datasets
> completely... Do you have an idea on how to do that better?
>
> Regards,
> Frits
>
>
> On Tue, Nov 17, 2020 at 5:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Frits Jalvingh <jal@etc.to> writes:
>> > I have attached both plans, both made with set enable_nestloop = false
>> in
>> > the attachments.
>>
>> The reason why you're getting a nested loop is that the planner has no
>> other choice. The "tijd" table has no join conditions that would be
>> amenable to hash- or merge-joining it to something else, because both
>> of those join methods require a plain equality join condition. AFAICS
>> in a quick look, all of tijd's join conditions look more like
>>
>> Join Filter: ((di04238cluster.dv_start_dts <= tijd.einddatum) AND
>> (di04238cluster.dv_end_dts > tijd.einddatum))
>>
>> which is not amenable to anything except brute force cross-join-and-
>> test-the-condition.
>>
>> Given that, it's likely that "enable_nestloop = false" is making things
>> worse not better, by artificially distorting the plan shape.
>>
>> Seeing the large number of joins involved, I wonder what your
>> geqo_threshold, join_collapse_limit, and from_collapse_limit settings
>> are, and whether you can get a better plan by increasing them.
>>
>> The planner doesn't seem to think that any of these joins involve
>> a very large number of rows, so I doubt that your work_mem setting
>> is very relevant. However, are these rowcount estimates accurate?
>> You claimed upthread that you were dealing with hundreds of millions
>> of rows, but it's impossible to credit that cost estimates like
>>
>> -> Seq Scan on s_h_cluster_ssm di01905cluster (cost=0.00..155.05
>> rows=1072 width=24)
>> Filter: (soort = 'FIN'::text)
>>
>> correspond to scanning large tables.
>>
>> In the end, I fear that finding a way to get rid of those
>> inequality join conditions may be your only real answer.
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-11-19 11:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-17 13:47 Postgres using nested loops despite setting enable_nestloop to false Frits Jalvingh <jal@etc.to>
2020-11-17 14:20 ` Justin Pryzby <pryzby@telsasoft.com>
2020-11-17 15:58 ` Frits Jalvingh <jal@etc.to>
2020-11-17 16:06 ` Justin Pryzby <pryzby@telsasoft.com>
2020-11-17 16:08 ` Frits Jalvingh <jal@etc.to>
2020-11-17 16:14 ` Frits Jalvingh <jal@etc.to>
2020-11-17 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-17 16:42 ` Frits Jalvingh <jal@etc.to>
2020-11-19 11:52 ` Frits Jalvingh <jal@etc.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox