Postgres using nested loops despite setting enable_nestloop to false

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