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 <t...@sss.pgh.pa.us> wrote:
> Frits Jalvingh <j...@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
