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@xxxxxxxxxxxxx> 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)
