explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sfKJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on src_side (cost=6,185,321.56..6,185,321.58 rows=1 width=544) (actual rows= loops=)

2.          

CTE all_src_intervals

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=1,000.42..4,619,407.62 rows=235,000 width=56) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.42..173,472.85 rows=233,640 width=41) (actual rows= loops=)

  • Workers Planned: 5
5. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.42..149,108.85 rows=46,728 width=41) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on bag_panden s (cost=0.00..95,109.31 rows=69,931 width=37) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using bag_panden__id_volgnummer_key on bag_panden t (cost=0.42..0.76 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (((s._id)::text = (_id)::text) AND (volgnummer < s.volgnummer))
  • Filter: (eind_geldigheid = s.begin_geldigheid)
8. 0.000 0.000 ↓ 0.0

Merge Join (cost=423,972.96..444,123.48 rows=136 width=56) (actual rows= loops=)

  • Merge Cond: ((intv.eind_geldigheid = src.begin_geldigheid) AND ((intv._id)::text = (src._id)::text))
  • Join Filter: (src.volgnummer > intv.volgnummer)
9. 0.000 0.000 ↓ 0.0

Sort (cost=293,870.71..299,711.71 rows=2,336,400 width=56) (actual rows= loops=)

  • Sort Key: intv.eind_geldigheid, intv._id
10. 0.000 0.000 ↓ 0.0

WorkTable Scan on all_src_intervals intv (cost=0.00..46,728.00 rows=2,336,400 width=56) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=130,102.25..130,976.40 rows=349,657 width=37) (actual rows= loops=)

  • Sort Key: src.begin_geldigheid, src._id
12. 0.000 0.000 ↓ 0.0

Seq Scan on bag_panden src (cost=0.00..97,906.57 rows=349,657 width=37) (actual rows= loops=)

  • Filter: (begin_geldigheid IS NOT NULL)
13.          

CTE all_dst_intervals

14. 0.000 0.000 ↓ 0.0

Recursive Union (cost=33,012.20..1,443,845.11 rows=254,332 width=56) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=33,012.20..67,575.72 rows=244,872 width=30) (actual rows= loops=)

  • Hash Cond: (((s_1._id)::text = (t_1._id)::text) AND (s_1.begin_geldigheid = t_1.eind_geldigheid))
  • Join Filter: (t_1.volgnummer < s_1.volgnummer)
16. 0.000 0.000 ↓ 0.0

Seq Scan on bag_onderzoeken s_1 (cost=0.00..27,583.28 rows=361,928 width=26) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=27,583.28..27,583.28 rows=361,928 width=18) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on bag_onderzoeken t_1 (cost=0.00..27,583.28 rows=361,928 width=18) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=33,012.20..137,118.28 rows=946 width=56) (actual rows= loops=)

  • Hash Cond: ((intv_1.eind_geldigheid = dst.begin_geldigheid) AND ((intv_1._id)::text = (dst._id)::text))
  • Join Filter: (dst.volgnummer > intv_1.volgnummer)
20. 0.000 0.000 ↓ 0.0

WorkTable Scan on all_dst_intervals intv_1 (cost=0.00..48,974.40 rows=2,448,720 width=56) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=27,583.28..27,583.28 rows=361,928 width=26) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on bag_onderzoeken dst (cost=0.00..27,583.28 rows=361,928 width=26) (actual rows= loops=)

  • Filter: (begin_geldigheid IS NOT NULL)
23.          

CTE src_entities

24. 0.000 0.000 ↓ 0.0

Index Scan using bag_pnd_37abd7da5cbd49b20a1090ba960d82e7 on bag_panden src_1 (cost=0.68..64,676.28 rows=116,552 width=816) (actual rows= loops=)

  • Index Cond: (_last_event > $9)
25.          

Initplan (for Index Scan)

26. 0.000 0.000 ↓ 0.0

Result (cost=0.24..0.25 rows=1 width=4) (actual rows= loops=)

27.          

Initplan (for Result)

28. 0.000 0.000 ↓ 0.0

Limit (cost=0.14..0.24 rows=1 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using rel_bag_pnd_bag_ozk_fb10656a_dc79a884dc55f09863437f9198baf021 on rel_bag_pnd_bag_ozk_heeft_onderzoeken (cost=0.14..14.85 rows=149 width=4) (actual rows= loops=)

  • Index Cond: (_last_src_event IS NOT NULL)
30.          

CTE src_volgnummer_begin_geldigheid

31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,299.24..10,534.24 rows=23,500 width=44) (actual rows= loops=)

  • Group Key: all_src_intervals._id, all_src_intervals.volgnummer
32. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,205.18..9,858.61 rows=58,750 width=44) (actual rows= loops=)

  • Hash Cond: (((all_src_intervals._id)::text = (src_entities._id)::text) AND (all_src_intervals.volgnummer = src_entities.volgnummer))
33. 0.000 0.000 ↓ 0.0

CTE Scan on all_src_intervals (cost=0.00..4,700.00 rows=235,000 width=44) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=3,030.35..3,030.35 rows=11,655 width=36) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,913.80..3,030.35 rows=11,655 width=36) (actual rows= loops=)

  • Group Key: (src_entities._id)::text, src_entities.volgnummer
36. 0.000 0.000 ↓ 0.0

CTE Scan on src_entities (cost=0.00..2,331.04 rows=116,552 width=36) (actual rows= loops=)

37.          

CTE dst_entities

38. 0.000 0.000 ↓ 0.0

Index Scan using bag_ozk_613273a0ec2090693894cea102aa8c06 on bag_onderzoeken (cost=0.68..13,536.05 rows=120,643 width=218) (actual rows= loops=)

  • Index Cond: (_last_event > $13)
39.          

Initplan (for Index Scan)

40. 0.000 0.000 ↓ 0.0

Result (cost=0.24..0.25 rows=1 width=4) (actual rows= loops=)

41.          

Initplan (for Result)

42. 0.000 0.000 ↓ 0.0

Limit (cost=0.14..0.24 rows=1 width=4) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using rel_bag_pnd_bag_ozk_fb10656a_f49c273bd9b194a2b48ebed02cfba269 on rel_bag_pnd_bag_ozk_heeft_onderzoeken rel_bag_pnd_bag_ozk_heeft_onderzoeken_1 (cost=0.14..14.85 rows=149 width=4) (actual rows= loops=)

  • Index Cond: (_last_dst_event IS NOT NULL)
44.          

CTE dst_volgnummer_begin_geldigheid

45. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,995.32..11,249.65 rows=25,433 width=44) (actual rows= loops=)

  • Group Key: all_dst_intervals._id, all_dst_intervals.volgnummer
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,317.68..10,518.45 rows=63,583 width=44) (actual rows= loops=)

  • Hash Cond: (((all_dst_intervals._id)::text = (dst_entities._id)::text) AND (all_dst_intervals.volgnummer = dst_entities.volgnummer))
47. 0.000 0.000 ↓ 0.0

CTE Scan on all_dst_intervals (cost=0.00..5,086.64 rows=254,332 width=44) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=3,136.72..3,136.72 rows=12,064 width=36) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,016.08..3,136.72 rows=12,064 width=36) (actual rows= loops=)

  • Group Key: (dst_entities._id)::text, dst_entities.volgnummer
50. 0.000 0.000 ↓ 0.0

CTE Scan on dst_entities (cost=0.00..2,412.86 rows=120,643 width=36) (actual rows= loops=)

51.          

CTE max_src_event

52. 0.000 0.000 ↓ 0.0

Result (cost=0.47..0.48 rows=1 width=4) (actual rows= loops=)

53.          

Initplan (for Result)

54. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..0.47 rows=1 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using bag_pnd_613273a0ec2090693894cea102aa8c06 on bag_panden (cost=0.42..15,917.83 rows=349,657 width=4) (actual rows= loops=)

  • Index Cond: (_last_event IS NOT NULL)
56.          

CTE max_dst_event

57. 0.000 0.000 ↓ 0.0

Result (cost=0.45..0.46 rows=1 width=4) (actual rows= loops=)

58.          

Initplan (for Result)

59. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..0.45 rows=1 width=4) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using bag_ozk_613273a0ec2090693894cea102aa8c06 on bag_onderzoeken bag_onderzoeken_1 (cost=0.42..9,548.36 rows=361,928 width=4) (actual rows= loops=)

  • Index Cond: (_last_event IS NOT NULL)
61.          

CTE src_side

62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17,652.52..22,071.67 rows=1 width=492) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17,652.52..22,071.55 rows=1 width=384) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17,652.52..22,071.52 rows=1 width=380) (actual rows= loops=)

  • Join Filter: (((dst_bg._id)::text = (dst_1._id)::text) AND (dst_bg.volgnummer = dst_1.volgnummer))
65. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17,652.52..21,181.36 rows=1 width=372) (actual rows= loops=)

  • Join Filter: (((src_bg._id)::text = (src_2._id)::text) AND (src_bg.volgnummer = src_2.volgnummer))
66. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17,652.52..20,358.86 rows=1 width=364) (actual rows= loops=)

  • Join Filter: (((src_2._application)::text = 'Neuron'::text) AND ((rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.src_id)::text = (src_2._id)::text) AND (rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.src_volgnummer = src_2.volgnummer) AND ((rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.dst_id)::text = (dst_1._id)::text) AND (rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.dst_volgnummer = dst_1.volgnummer) AND ((json_arr_elm.item ->> 'bronwaarde'::text) = (rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.bronwaarde)::text))
  • Filter: ((rel_bag_pnd_bag_ozk_heeft_onderzoeken_2._date_deleted IS NULL) OR (src_2._id IS NOT NULL))
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=14,724.97..17,225.86 rows=1 width=228) (actual rows= loops=)

  • Join Filter: (((json_arr_elm_1.item ->> 'bronwaarde'::text)) = (json_arr_elm.item ->> 'bronwaarde'::text))
68. 0.000 0.000 ↓ 0.0

Nested Loop (cost=14,724.96..17,223.11 rows=1 width=260) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Merge Join (cost=14,724.54..17,219.29 rows=4 width=238) (actual rows= loops=)

  • Merge Cond: (((src_entities_2._id)::text = (src_2._id)::text) AND (src_entities_2.volgnummer = src_2.volgnummer))
  • Join Filter: ((src_2._source)::text = (src_entities_2._source)::text)
70. 0.000 0.000 ↓ 0.0

WindowAgg (cost=12,075.34..13,678.59 rows=58,300 width=118) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Sort (cost=12,075.34..12,221.09 rows=58,300 width=110) (actual rows= loops=)

  • Sort Key: src_entities_2._id, src_entities_2.volgnummer, ((json_arr_elm_1.item ->> 'bronwaarde'::text))
72. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,731.79..7,460.54 rows=58,300 width=110) (actual rows= loops=)

  • Group Key: src_entities_2._id, dst_2._id, (json_arr_elm_1.item ->> 'bronwaarde'::text), src_entities_2._source, src_entities_2.volgnummer
73. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..5,857.29 rows=58,300 width=110) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..4,399.79 rows=583 width=110) (actual rows= loops=)

  • Join Filter: ((src_entities_2._application)::text = 'Neuron'::text)
75. 0.000 0.000 ↓ 0.0

CTE Scan on src_entities src_entities_2 (cost=0.00..2,331.04 rows=583 width=172) (actual rows= loops=)

  • Filter: (_date_deleted IS NULL)
76. 0.000 0.000 ↓ 0.0

Index Scan using bag_ozk_2c5e9e3edb055efb9ce5e2a9588c46f6 on bag_onderzoeken dst_2 (cost=0.42..3.52 rows=2 width=43) (actual rows= loops=)

  • Index Cond: ((object_identificatie)::text = (src_entities_2.identificatie)::text)
  • Filter: ((_date_deleted IS NULL) AND ((begin_geldigheid < src_entities_2.eind_geldigheid) OR (src_entities_2.eind_geldigheid IS NULL)) AND ((eind_geldigheid >= src_entities_2.eind_geldigheid) OR (eind_geldigheid IS NULL)))
77. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_array_elements json_arr_elm_1 (cost=0.00..1.25 rows=100 width=32) (actual rows= loops=)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
78. 0.000 0.000 ↓ 0.0

Sort (cost=2,649.20..2,650.66 rows=583 width=188) (actual rows= loops=)

  • Sort Key: src_2._id, src_2.volgnummer
79. 0.000 0.000 ↓ 0.0

CTE Scan on src_entities src_2 (cost=0.00..2,622.42 rows=583 width=188) (actual rows= loops=)

  • Filter: ((_application)::text = 'Neuron'::text)
80. 0.000 0.000 ↓ 0.0

Index Scan using bag_onderzoeken__id_volgnummer_key on bag_onderzoeken dst_1 (cost=0.42..0.96 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (((_id)::text = (dst_2._id)::text) AND (_id IS NOT NULL) AND (volgnummer = (max(dst_2.volgnummer))))
  • Filter: (_date_deleted IS NULL)
81. 0.000 0.000 ↓ 0.0

Function Scan on jsonb_array_elements json_arr_elm (cost=0.00..1.25 rows=100 width=32) (actual rows= loops=)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
82. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,927.55..3,131.95 rows=38 width=204) (actual rows= loops=)

  • Hash Cond: (((src_entities_1._id)::text = (rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.src_id)::text) AND (src_entities_1.volgnummer = rel_bag_pnd_bag_ozk_heeft_onderzoeken_2.src_volgnummer))
83. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,913.80..3,030.35 rows=11,655 width=36) (actual rows= loops=)

  • Group Key: (src_entities_1._id)::text, src_entities_1.volgnummer
84. 0.000 0.000 ↓ 0.0

CTE Scan on src_entities src_entities_1 (cost=0.00..2,331.04 rows=116,552 width=36) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=11.50..11.50 rows=150 width=204) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on rel_bag_pnd_bag_ozk_heeft_onderzoeken rel_bag_pnd_bag_ozk_heeft_onderzoeken_2 (cost=0.00..11.50 rows=150 width=204) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

CTE Scan on src_volgnummer_begin_geldigheid src_bg (cost=0.00..470.00 rows=23,500 width=44) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

CTE Scan on dst_volgnummer_begin_geldigheid dst_bg (cost=0.00..508.66 rows=25,433 width=44) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

CTE Scan on max_src_event (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

CTE Scan on max_dst_event (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)