explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tNfc

Settings
# exclusive inclusive rows x rows loops node
1. 73,865.573 73,865.573 ↓ 849.0 849 1

CTE Scan on src_side (cost=6,122,146.33..6,122,146.35 rows=1 width=544) (actual time=8,704.019..73,865.573 rows=849 loops=1)

2.          

CTE all_src_intervals

3. 425.541 5,863.577 ↓ 1.5 349,657 1

Recursive Union (cost=1,000.42..4,619,407.62 rows=235,000 width=56) (actual time=1.834..5,863.577 rows=349,657 loops=1)

4. 0.000 477.333 ↑ 1.2 191,939 1

Gather (cost=1,000.42..173,472.85 rows=233,640 width=41) (actual time=1.830..477.333 rows=191,939 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
5. 0.622 653.736 ↑ 1.5 31,990 6 / 6

Nested Loop Anti Join (cost=0.42..149,108.85 rows=46,728 width=41) (actual time=0.427..653.736 rows=31,990 loops=6)

6. 70.352 70.352 ↑ 1.2 58,276 6 / 6

Parallel Seq Scan on bag_panden s (cost=0.00..95,109.31 rows=69,931 width=37) (actual time=0.017..70.352 rows=58,276 loops=6)

7. 582.762 582.762 ↓ 0.0 0 349,657 / 6

Index Scan using bag_panden__id_volgnummer_key on bag_panden t (cost=0.42..0.76 rows=1 width=29) (actual time=0.010..0.010 rows=0 loops=349,657)

  • Index Cond: (((s._id)::text = (_id)::text) AND (volgnummer < s.volgnummer))
  • Filter: (eind_geldigheid = s.begin_geldigheid)
  • Rows Removed by Filter: 0
8. 1,045.077 4,960.703 ↓ 574.0 78,060 11

Merge Join (cost=423,972.96..444,123.48 rows=136 width=56) (actual time=355.968..450.973 rows=78,060 loops=11)

  • Merge Cond: ((intv.eind_geldigheid = src.begin_geldigheid) AND ((intv._id)::text = (src._id)::text))
  • Join Filter: (src.volgnummer > intv.volgnummer)
  • Rows Removed by Join Filter: 66,621
9. 1,357.422 1,397.616 ↑ 162.9 14,339 11

Sort (cost=293,870.71..299,711.71 rows=2,336,400 width=56) (actual time=124.900..127.056 rows=14,339 loops=11)

  • Sort Key: intv.eind_geldigheid, intv._id
  • Sort Method: quicksort Memory: 25kB
10. 40.194 40.194 ↑ 73.5 31,787 11

WorkTable Scan on all_src_intervals intv (cost=0.00..46,728.00 rows=2,336,400 width=56) (actual time=0.001..3.654 rows=31,787 loops=11)

11. 2,220.032 2,518.010 ↓ 1.3 469,419 11

Sort (cost=130,102.25..130,976.40 rows=349,657 width=37) (actual time=176.768..228.910 rows=469,419 loops=11)

  • Sort Key: src.begin_geldigheid, src._id
  • Sort Method: quicksort Memory: 39,605kB
12. 297.978 297.978 ↑ 1.0 349,657 1

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

  • Filter: (begin_geldigheid IS NOT NULL)
13.          

CTE all_dst_intervals

14. 234.882 1,171.241 ↓ 1.4 361,928 1

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

15. 216.212 526.099 ↑ 1.3 184,537 1

Hash Anti Join (cost=33,012.20..67,575.72 rows=244,872 width=30) (actual time=250.089..526.099 rows=184,537 loops=1)

  • 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)
  • Rows Removed by Join Filter: 11,814
16. 60.433 60.433 ↑ 1.0 361,928 1

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

17. 67.790 249.454 ↑ 2.0 177,396 1

Hash (cost=27,583.28..27,583.28 rows=361,928 width=18) (actual time=249.454..249.454 rows=177,396 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 13,791kB
18. 181.664 181.664 ↑ 1.0 361,928 1

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

19. 135.998 410.260 ↓ 37.6 35,533 5

Hash Join (cost=33,012.20..137,118.28 rows=946 width=56) (actual time=47.009..82.052 rows=35,533 loops=5)

  • Hash Cond: ((intv_1.eind_geldigheid = dst.begin_geldigheid) AND ((intv_1._id)::text = (dst._id)::text))
  • Join Filter: (dst.volgnummer > intv_1.volgnummer)
  • Rows Removed by Join Filter: 2,370
20. 41.830 41.830 ↑ 33.8 72,386 5

WorkTable Scan on all_dst_intervals intv_1 (cost=0.00..48,974.40 rows=2,448,720 width=56) (actual time=0.001..8.366 rows=72,386 loops=5)

21. 99.035 232.432 ↑ 1.0 361,928 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,711kB
22. 133.397 133.397 ↑ 1.0 361,928 1

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

  • Filter: (begin_geldigheid IS NOT NULL)
23.          

CTE src_entities

24. 0.929 12.463 ↑ 1.0 10,000 1

Limit (cost=0.00..2,800.07 rows=10,000 width=816) (actual time=0.024..12.463 rows=10,000 loops=1)

25. 11.534 11.534 ↑ 35.0 10,000 1

Seq Scan on bag_panden src_1 (cost=0.00..97,906.57 rows=349,657 width=816) (actual time=0.023..11.534 rows=10,000 loops=1)

26.          

CTE src_volgnummer_begin_geldigheid

27. 9.224 6,094.782 ↑ 2.4 10,000 1

HashAggregate (cost=7,369.06..7,604.06 rows=23,500 width=44) (actual time=6,090.073..6,094.782 rows=10,000 loops=1)

  • Group Key: all_src_intervals._id, all_src_intervals.volgnummer
28. 96.754 6,085.558 ↑ 5.9 10,000 1

Hash Join (cost=275.00..6,928.44 rows=58,750 width=44) (actual time=12.063..6,085.558 rows=10,000 loops=1)

  • Hash Cond: (((all_src_intervals._id)::text = (src_entities._id)::text) AND (all_src_intervals.volgnummer = src_entities.volgnummer))
29. 5,978.591 5,978.591 ↓ 1.5 349,657 1

CTE Scan on all_src_intervals (cost=0.00..4,700.00 rows=235,000 width=44) (actual time=1.835..5,978.591 rows=349,657 loops=1)

30. 2.057 10.213 ↓ 10.0 10,000 1

Hash (cost=260.00..260.00 rows=1,000 width=36) (actual time=10.213..10.213 rows=10,000 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 675kB
31. 5.360 8.156 ↓ 10.0 10,000 1

HashAggregate (cost=250.00..260.00 rows=1,000 width=36) (actual time=6.276..8.156 rows=10,000 loops=1)

  • Group Key: (src_entities._id)::text, src_entities.volgnummer
32. 2.796 2.796 ↑ 1.0 10,000 1

CTE Scan on src_entities (cost=0.00..200.00 rows=10,000 width=36) (actual time=0.002..2.796 rows=10,000 loops=1)

33.          

CTE dst_entities

34. 55.845 55.845 ↑ 1.0 361,928 1

Seq Scan on bag_onderzoeken (cost=0.00..27,583.28 rows=361,928 width=218) (actual time=0.006..55.845 rows=361,928 loops=1)

35.          

CTE dst_volgnummer_begin_geldigheid

36. 285.453 2,356.332 ↓ 14.2 361,928 1

HashAggregate (cost=17,630.67..17,885.00 rows=25,433 width=44) (actual time=2,253.154..2,356.332 rows=361,928 loops=1)

  • Group Key: all_dst_intervals._id, all_dst_intervals.volgnummer
37. 218.445 2,070.879 ↓ 5.7 361,928 1

Hash Join (cost=9,953.03..17,153.80 rows=63,583 width=44) (actual time=808.349..2,070.879 rows=361,928 loops=1)

  • Hash Cond: (((all_dst_intervals._id)::text = (dst_entities._id)::text) AND (all_dst_intervals.volgnummer = dst_entities.volgnummer))
38. 1,294.430 1,294.430 ↓ 1.4 361,928 1

CTE Scan on all_dst_intervals (cost=0.00..5,086.64 rows=254,332 width=44) (actual time=250.095..1,294.430 rows=361,928 loops=1)

39. 74.696 558.004 ↓ 10.0 361,928 1

Hash (cost=9,410.13..9,410.13 rows=36,193 width=36) (actual time=558.004..558.004 rows=361,928 loops=1)

  • Buckets: 524,288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 19,641kB
40. 244.261 483.308 ↓ 10.0 361,928 1

HashAggregate (cost=9,048.20..9,410.13 rows=36,193 width=36) (actual time=393.450..483.308 rows=361,928 loops=1)

  • Group Key: (dst_entities._id)::text, dst_entities.volgnummer
41. 239.047 239.047 ↑ 1.0 361,928 1

CTE Scan on dst_entities (cost=0.00..7,238.56 rows=361,928 width=36) (actual time=0.009..239.047 rows=361,928 loops=1)

42.          

CTE max_src_event

43. 0.002 0.041 ↑ 1.0 1 1

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

44.          

Initplan (for Result)

45. 0.003 0.039 ↑ 1.0 1 1

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

46. 0.036 0.036 ↑ 349,657.0 1 1

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

  • Index Cond: (_last_event IS NOT NULL)
  • Heap Fetches: 1
47.          

CTE max_dst_event

48. 0.001 0.048 ↑ 1.0 1 1

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

49.          

Initplan (for Result)

50. 0.002 0.047 ↑ 1.0 1 1

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

51. 0.045 0.045 ↑ 361,928.0 1 1

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 time=0.045..0.045 rows=1 loops=1)

  • Index Cond: (_last_event IS NOT NULL)
  • Heap Fetches: 0
52.          

CTE src_side

53. 10.159 73,862.101 ↓ 849.0 849 1

Nested Loop (cost=1,649.37..3,020.25 rows=1 width=492) (actual time=8,704.016..73,862.101 rows=849 loops=1)

54. 2.175 73,851.942 ↓ 849.0 849 1

Nested Loop (cost=1,649.37..3,020.13 rows=1 width=384) (actual time=8,703.951..73,851.942 rows=849 loops=1)

55. 4.183 73,849.767 ↓ 849.0 849 1

Nested Loop Left Join (cost=1,649.37..3,020.10 rows=1 width=380) (actual time=8,703.905..73,849.767 rows=849 loops=1)

  • Join Filter: (((src_2._application)::text = 'Neuron'::text) AND ((rel_bag_pnd_bag_ozk_heeft_onderzoeken.src_id)::text = (src_2._id)::text) AND (rel_bag_pnd_bag_ozk_heeft_onderzoeken.src_volgnummer = src_2.volgnummer) AND ((json_arr_elm.item ->> 'bronwaarde'::text) = (rel_bag_pnd_bag_ozk_heeft_onderzoeken.bronwaarde)::text))
  • Filter: ((rel_bag_pnd_bag_ozk_heeft_onderzoeken._date_deleted IS NULL) OR (src_2._id IS NOT NULL))
56. 12.363 73,836.245 ↓ 849.0 849 1

Nested Loop (cost=1,399.22..2,744.91 rows=1 width=244) (actual time=8,703.877..73,836.245 rows=849 loops=1)

57. 11.849 6,338.840 ↓ 10,566.0 10,566 1

Hash Right Join (cost=1,394.40..2,040.66 rows=1 width=246) (actual time=6,316.900..6,338.840 rows=10,566 loops=1)

  • Hash Cond: (((src_bg._id)::text = (src_2._id)::text) AND (src_bg.volgnummer = src_2.volgnummer))
58. 6,100.187 6,100.187 ↑ 2.4 10,000 1

CTE Scan on src_volgnummer_begin_geldigheid src_bg (cost=0.00..470.00 rows=23,500 width=44) (actual time=6,090.076..6,100.187 rows=10,000 loops=1)

59. 4.314 226.804 ↓ 10,566.0 10,566 1

Hash (cost=1,394.39..1,394.39 rows=1 width=238) (actual time=226.804..226.804 rows=10,566 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,808kB
60. 8.192 222.490 ↓ 10,566.0 10,566 1

Merge Join (cost=1,181.44..1,394.39 rows=1 width=238) (actual time=204.756..222.490 rows=10,566 loops=1)

  • 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)
61. 6.902 174.128 ↓ 2.1 10,566 1

WindowAgg (cost=955.03..1,092.53 rows=5,000 width=118) (actual time=165.647..174.128 rows=10,566 loops=1)

62. 44.832 167.226 ↓ 2.1 10,566 1

Sort (cost=955.03..967.53 rows=5,000 width=110) (actual time=165.635..167.226 rows=10,566 loops=1)

  • Sort Key: src_entities_2._id, src_entities_2.volgnummer, ((json_arr_elm_1.item ->> 'bronwaarde'::text))
  • Sort Method: quicksort Memory: 1,263kB
63. 8.891 122.394 ↓ 2.1 10,566 1

HashAggregate (cost=585.33..647.83 rows=5,000 width=110) (actual time=119.397..122.394 rows=10,566 loops=1)

  • Group Key: src_entities_2._id, dst_2._id, (json_arr_elm_1.item ->> 'bronwaarde'::text), src_entities_2._source, src_entities_2.volgnummer
64. 4.420 113.503 ↓ 2.1 10,566 1

Nested Loop (cost=0.42..510.33 rows=5,000 width=110) (actual time=0.102..113.503 rows=10,566 loops=1)

65. 7.345 98.517 ↓ 211.3 10,566 1

Nested Loop Left Join (cost=0.42..385.33 rows=50 width=110) (actual time=0.077..98.517 rows=10,566 loops=1)

  • Join Filter: ((src_entities_2._application)::text = 'Neuron'::text)
66. 31.172 31.172 ↓ 200.0 10,000 1

CTE Scan on src_entities src_entities_2 (cost=0.00..200.00 rows=50 width=172) (actual time=0.031..31.172 rows=10,000 loops=1)

  • Filter: (_date_deleted IS NULL)
67. 60.000 60.000 ↓ 0.0 0 10,000

Index Scan using bag_ozk_2c5e9e3edb055efb9ce5e2a9588c46f6 on bag_onderzoeken dst_2 (cost=0.42..3.68 rows=2 width=43) (actual time=0.006..0.006 rows=0 loops=10,000)

  • 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)))
  • Rows Removed by Filter: 0
68. 10.566 10.566 ↑ 100.0 1 10,566

Function Scan on jsonb_array_elements json_arr_elm_1 (cost=0.00..1.25 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=10,566)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
69. 36.035 40.170 ↓ 211.3 10,566 1

Sort (cost=226.41..226.54 rows=50 width=188) (actual time=39.098..40.170 rows=10,566 loops=1)

  • Sort Key: src_2._id, src_2.volgnummer
  • Sort Method: quicksort Memory: 1,791kB
70. 4.135 4.135 ↓ 200.0 10,000 1

CTE Scan on src_entities src_2 (cost=0.00..225.00 rows=50 width=188) (actual time=0.003..4.135 rows=10,000 loops=1)

  • Filter: ((_application)::text = 'Neuron'::text)
71. 36,069.681 67,485.042 ↓ 0.0 0 10,566

Hash Right Join (cost=4.82..704.23 rows=1 width=72) (actual time=3.185..6.387 rows=0 loops=10,566)

  • Hash Cond: (((dst_bg._id)::text = (dst_1._id)::text) AND (dst_bg.volgnummer = dst_1.volgnummer))
72. 31,373.097 31,373.097 ↓ 14.2 361,928 849

CTE Scan on dst_volgnummer_begin_geldigheid dst_bg (cost=0.00..508.66 rows=25,433 width=44) (actual time=2.655..36.953 rows=361,928 loops=849)

73. 0.000 42.264 ↓ 0.0 0 10,566

Hash (cost=4.80..4.80 rows=1 width=64) (actual time=0.004..0.004 rows=0 loops=10,566)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
74. 12.642 42.264 ↓ 0.0 0 10,566

Nested Loop (cost=0.42..4.80 rows=1 width=64) (actual time=0.004..0.004 rows=0 loops=10,566)

  • Join Filter: (((json_arr_elm_1.item ->> 'bronwaarde'::text)) = (json_arr_elm.item ->> 'bronwaarde'::text))
75. 21.132 21.132 ↓ 0.0 0 10,566

Index Scan using bag_onderzoeken__id_volgnummer_key on bag_onderzoeken dst_1 (cost=0.42..2.05 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=10,566)

  • Index Cond: (((_id)::text = (dst_2._id)::text) AND (_id IS NOT NULL) AND (volgnummer = (max(dst_2.volgnummer))))
  • Filter: (_date_deleted IS NULL)
76. 8.490 8.490 ↑ 100.0 1 849

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

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
77. 2.547 9.339 ↓ 0.0 0 849

Nested Loop (cost=250.15..275.17 rows=1 width=204) (actual time=0.011..0.011 rows=0 loops=849)

  • Join Filter: (((rel_bag_pnd_bag_ozk_heeft_onderzoeken.src_id)::text = (src_entities_1._id)::text) AND (rel_bag_pnd_bag_ozk_heeft_onderzoeken.src_volgnummer = src_entities_1.volgnummer))
78. 6.792 6.792 ↓ 0.0 0 849

Index Scan using rel_bag_pnd_bag_ozk_fb10656a_c5625cb292cd152f07c13709330d1712 on rel_bag_pnd_bag_ozk_heeft_onderzoeken (cost=0.14..0.17 rows=1 width=204) (actual time=0.008..0.008 rows=0 loops=849)

  • Index Cond: (((dst_id)::text = (dst_1._id)::text) AND (dst_volgnummer = dst_1.volgnummer))
79. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=250.00..260.00 rows=1,000 width=36) (never executed)

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

CTE Scan on src_entities src_entities_1 (cost=0.00..200.00 rows=10,000 width=36) (never executed)

81. 0.000 0.000 ↑ 1.0 1 849

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

82. 0.000 0.000 ↑ 1.0 1 849

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

Planning time : 18.242 ms
Execution time : 73,947.719 ms