explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8yAak

Settings
# exclusive inclusive rows x rows loops node
1. 11,911.257 11,911.257 ↓ 63.0 63 1

CTE Scan on src_side (cost=5,857,358.04..5,857,358.06 rows=1 width=544) (actual time=7,567.295..11,911.257 rows=63 loops=1)

2.          

CTE all_src_intervals

3. 367.102 5,321.481 ↓ 1.5 348,184 1

Recursive Union (cost=1,000.42..4,365,015.78 rows=234,066 width=56) (actual time=0.847..5,321.481 rows=348,184 loops=1)

4. 0.000 498.642 ↑ 1.2 191,910 1

Gather (cost=1,000.42..157,630.74 rows=232,676 width=41) (actual time=0.844..498.642 rows=191,910 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
5. 10.508 621.481 ↑ 1.5 38,382 5 / 5

Nested Loop Anti Join (cost=0.42..133,363.14 rows=58,169 width=41) (actual time=0.061..621.481 rows=38,382 loops=5)

6. 53.879 53.879 ↑ 1.2 69,637 5 / 5

Parallel Seq Scan on bag_panden s (cost=0.00..72,953.46 rows=87,046 width=37) (actual time=0.011..53.879 rows=69,637 loops=5)

7. 557.094 557.094 ↓ 0.0 0 348,184 / 5

Index Scan using bag_panden__id_volgnummer_key on bag_panden t (cost=0.42..0.68 rows=1 width=29) (actual time=0.008..0.008 rows=0 loops=348,184)

  • Index Cond: (((s._id)::text = (_id)::text) AND (volgnummer < s.volgnummer))
  • Filter: (eind_geldigheid = s.begin_geldigheid)
  • Rows Removed by Filter: 0
8. 941.391 4,455.737 ↓ 560.1 77,848 11

Merge Join (cost=400,203.09..420,270.37 rows=139 width=56) (actual time=317.561..405.067 rows=77,848 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,507
9. 1,240.756 1,274.867 ↑ 163.8 14,208 11

Sort (cost=292,588.80..298,405.70 rows=2,326,760 width=56) (actual time=113.899..115.897 rows=14,208 loops=11)

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

WorkTable Scan on all_src_intervals intv (cost=0.00..46,535.20 rows=2,326,760 width=56) (actual time=0.002..3.101 rows=31,653 loops=11)

11. 2,043.295 2,239.479 ↓ 1.3 468,333 11

Sort (cost=107,614.29..108,484.75 rows=348,184 width=37) (actual time=151.102..203.589 rows=468,333 loops=11)

  • Sort Key: src.begin_geldigheid, src._id
  • Sort Method: quicksort Memory: 39,490kB
12. 196.184 196.184 ↑ 1.0 348,184 1

Seq Scan on bag_panden src (cost=0.00..75,564.84 rows=348,184 width=37) (actual time=0.014..196.184 rows=348,184 loops=1)

  • Filter: (begin_geldigheid IS NOT NULL)
13.          

CTE all_dst_intervals

14. 212.360 992.164 ↓ 1.4 361,901 1

Recursive Union (cost=32,739.53..1,438,106.55 rows=253,813 width=56) (actual time=162.216..992.164 rows=361,901 loops=1)

15. 187.519 399.494 ↑ 1.3 184,548 1

Hash Anti Join (cost=32,739.53..67,032.92 rows=244,293 width=30) (actual time=162.213..399.494 rows=184,548 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,843
16. 50.392 50.392 ↑ 1.0 361,901 1

Seq Scan on bag_onderzoeken s_1 (cost=0.00..27,311.01 rows=361,901 width=26) (actual time=0.008..50.392 rows=361,901 loops=1)

17. 53.119 161.583 ↑ 2.0 177,358 1

Hash (cost=27,311.01..27,311.01 rows=361,901 width=18) (actual time=161.582..161.583 rows=177,358 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 13,789kB
18. 108.464 108.464 ↑ 1.0 361,901 1

Seq Scan on bag_onderzoeken t_1 (cost=0.00..27,311.01 rows=361,901 width=18) (actual time=0.003..108.464 rows=361,901 loops=1)

19. 126.233 380.310 ↓ 37.3 35,525 5

Hash Join (cost=32,739.53..136,599.74 rows=952 width=56) (actual time=43.700..76.062 rows=35,525 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. 37.875 37.875 ↑ 33.8 72,380 5

WorkTable Scan on all_dst_intervals intv_1 (cost=0.00..48,858.60 rows=2,442,930 width=56) (actual time=0.001..7.575 rows=72,380 loops=5)

21. 93.213 216.202 ↑ 1.0 361,901 1

Hash (cost=27,311.01..27,311.01 rows=361,901 width=26) (actual time=216.202..216.202 rows=361,901 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,709kB
22. 122.989 122.989 ↑ 1.0 361,901 1

Seq Scan on bag_onderzoeken dst (cost=0.00..27,311.01 rows=361,901 width=26) (actual time=0.009..122.989 rows=361,901 loops=1)

  • Filter: (begin_geldigheid IS NOT NULL)
23.          

CTE src_entities

24. 0.076 0.776 ↑ 1.0 1,000 1

Limit (cost=0.00..217.03 rows=1,000 width=816) (actual time=0.015..0.776 rows=1,000 loops=1)

25. 0.700 0.700 ↑ 348.2 1,000 1

Seq Scan on bag_panden src_1 (cost=0.00..75,564.84 rows=348,184 width=816) (actual time=0.014..0.700 rows=1,000 loops=1)

26.          

CTE src_volgnummer_begin_geldigheid

27. 1.027 5,475.266 ↑ 23.4 1,000 1

HashAggregate (cost=7,095.86..7,329.93 rows=23,407 width=44) (actual time=5,474.780..5,475.266 rows=1,000 loops=1)

  • Group Key: all_src_intervals._id, all_src_intervals.volgnummer
28. 56.695 5,474.239 ↑ 58.5 1,000 1

Hash Join (cost=30.00..6,656.99 rows=58,516 width=44) (actual time=1.745..5,474.239 rows=1,000 loops=1)

  • Hash Cond: (((all_src_intervals._id)::text = (src_entities._id)::text) AND (all_src_intervals.volgnummer = src_entities.volgnummer))
29. 5,416.684 5,416.684 ↓ 1.5 348,184 1

CTE Scan on all_src_intervals (cost=0.00..4,681.32 rows=234,066 width=44) (actual time=0.849..5,416.684 rows=348,184 loops=1)

30. 0.142 0.860 ↓ 5.0 1,000 1

Hash (cost=27.00..27.00 rows=200 width=36) (actual time=0.860..0.860 rows=1,000 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
31. 0.405 0.718 ↓ 5.0 1,000 1

HashAggregate (cost=25.00..27.00 rows=200 width=36) (actual time=0.574..0.718 rows=1,000 loops=1)

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

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

33.          

CTE dst_entities

34. 45.985 45.985 ↑ 1.0 361,901 1

Seq Scan on bag_onderzoeken (cost=0.00..27,311.01 rows=361,901 width=216) (actual time=0.007..45.985 rows=361,901 loops=1)

35.          

CTE dst_volgnummer_begin_geldigheid

36. 253.660 2,051.138 ↓ 14.3 361,901 1

HashAggregate (cost=17,614.25..17,868.06 rows=25,381 width=44) (actual time=1,962.363..2,051.138 rows=361,901 loops=1)

  • Group Key: all_dst_intervals._id, all_dst_intervals.volgnummer
37. 199.831 1,797.478 ↓ 5.7 361,901 1

Hash Join (cost=9,952.28..17,138.35 rows=63,453 width=44) (actual time=657.028..1,797.478 rows=361,901 loops=1)

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

CTE Scan on all_dst_intervals (cost=0.00..5,076.26 rows=253,813 width=44) (actual time=162.219..1,102.938 rows=361,901 loops=1)

39. 72.839 494.709 ↓ 10.0 361,901 1

Hash (cost=9,409.43..9,409.43 rows=36,190 width=36) (actual time=494.709..494.709 rows=361,901 loops=1)

  • Buckets: 524,288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 19,640kB
40. 217.918 421.870 ↓ 10.0 361,901 1

HashAggregate (cost=9,047.53..9,409.43 rows=36,190 width=36) (actual time=337.707..421.870 rows=361,901 loops=1)

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

CTE Scan on dst_entities (cost=0.00..7,238.02 rows=361,901 width=36) (actual time=0.010..203.952 rows=361,901 loops=1)

42.          

CTE max_src_event

43. 0.002 0.024 ↑ 1.0 1 1

Result (cost=0.46..0.47 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)

44.          

Initplan (for Result)

45. 0.003 0.022 ↑ 1.0 1 1

Limit (cost=0.42..0.46 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)

46. 0.019 0.019 ↑ 348,184.0 1 1

Index Only Scan Backward using bag_pnd_613273a0ec2090693894cea102aa8c06 on bag_panden (cost=0.42..12,084.24 rows=348,184 width=4) (actual time=0.019..0.019 rows=1 loops=1)

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

CTE max_dst_event

48. 0.001 0.013 ↑ 1.0 1 1

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

49.          

Initplan (for Result)

50. 0.001 0.012 ↑ 1.0 1 1

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

51. 0.011 0.011 ↑ 361,901.0 1 1

Index Only Scan Backward using bag_ozk_613273a0ec2090693894cea102aa8c06 on bag_onderzoeken bag_onderzoeken_1 (cost=0.42..9,511.59 rows=361,901 width=4) (actual time=0.011..0.011 rows=1 loops=1)

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

CTE src_side

53. 0.611 11,910.976 ↓ 63.0 63 1

Nested Loop (cost=161.83..1,508.77 rows=1 width=482) (actual time=7,567.292..11,910.976 rows=63 loops=1)

54. 0.105 11,910.302 ↓ 63.0 63 1

Nested Loop (cost=161.83..1,508.65 rows=1 width=374) (actual time=7,567.264..11,910.302 rows=63 loops=1)

55. 0.412 11,910.134 ↓ 63.0 63 1

Nested Loop Left Join (cost=161.83..1,508.62 rows=1 width=370) (actual time=7,567.237..11,910.134 rows=63 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. 0.477 11,892.334 ↓ 63.0 63 1

Nested Loop (cost=136.41..1,478.11 rows=1 width=244) (actual time=7,566.608..11,892.334 rows=63 loops=1)

57. 0.927 5,493.977 ↓ 1,042.0 1,042 1

Hash Right Join (cost=131.10..774.80 rows=1 width=246) (actual time=5,492.133..5,493.977 rows=1,042 loops=1)

  • Hash Cond: (((src_bg._id)::text = (src_2._id)::text) AND (src_bg.volgnummer = src_2.volgnummer))
58. 5,475.709 5,475.709 ↑ 23.4 1,000 1

CTE Scan on src_volgnummer_begin_geldigheid src_bg (cost=0.00..468.14 rows=23,407 width=44) (actual time=5,474.783..5,475.709 rows=1,000 loops=1)

59. 0.311 17.341 ↓ 1,042.0 1,042 1

Hash (cost=131.08..131.08 rows=1 width=238) (actual time=17.341..17.341 rows=1,042 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 174kB
60. 1.006 17.030 ↓ 1,042.0 1,042 1

Merge Join (cost=109.78..131.08 rows=1 width=238) (actual time=15.271..17.030 rows=1,042 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. 0.630 13.076 ↓ 2.1 1,042 1

WindowAgg (cost=87.22..100.97 rows=500 width=118) (actual time=12.372..13.076 rows=1,042 loops=1)

62. 2.582 12.446 ↓ 2.1 1,042 1

Sort (cost=87.22..88.47 rows=500 width=110) (actual time=12.364..12.446 rows=1,042 loops=1)

  • Sort Key: src_entities_2._id, src_entities_2.volgnummer, ((json_arr_elm_1.item ->> 'bronwaarde'::text))
  • Sort Method: quicksort Memory: 134kB
63. 0.635 9.864 ↓ 2.1 1,042 1

HashAggregate (cost=58.56..64.81 rows=500 width=110) (actual time=9.674..9.864 rows=1,042 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. 0.333 9.229 ↓ 2.1 1,042 1

Nested Loop (cost=0.42..51.06 rows=500 width=110) (actual time=0.052..9.229 rows=1,042 loops=1)

65. 0.876 7.854 ↓ 208.4 1,042 1

Nested Loop Left Join (cost=0.42..38.56 rows=5 width=110) (actual time=0.041..7.854 rows=1,042 loops=1)

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

CTE Scan on src_entities src_entities_2 (cost=0.00..20.00 rows=5 width=172) (actual time=0.019..1.978 rows=1,000 loops=1)

  • Filter: (_date_deleted IS NULL)
67. 5.000 5.000 ↓ 0.0 0 1,000

Index Scan using bag_ozk_2c5e9e3edb055efb9ce5e2a9588c46f6 on bag_onderzoeken dst_2 (cost=0.42..3.69 rows=2 width=42) (actual time=0.005..0.005 rows=0 loops=1,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. 1.042 1.042 ↑ 100.0 1 1,042

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=1,042)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
69. 2.517 2.948 ↓ 208.4 1,042 1

Sort (cost=22.56..22.57 rows=5 width=188) (actual time=2.892..2.948 rows=1,042 loops=1)

  • Sort Key: src_2._id, src_2.volgnummer
  • Sort Method: quicksort Memory: 165kB
70. 0.431 0.431 ↓ 200.0 1,000 1

CTE Scan on src_entities src_2 (cost=0.00..22.50 rows=5 width=188) (actual time=0.002..0.431 rows=1,000 loops=1)

  • Filter: ((_application)::text = 'Neuron'::text)
71. 2,421.029 6,397.880 ↓ 0.0 0 1,042

Hash Right Join (cost=5.32..703.30 rows=1 width=72) (actual time=3.793..6.140 rows=0 loops=1,042)

  • Hash Cond: (((dst_bg._id)::text = (dst_1._id)::text) AND (dst_bg.volgnummer = dst_1.volgnummer))
72. 3,973.725 3,973.725 ↓ 14.3 361,901 63

CTE Scan on dst_volgnummer_begin_geldigheid dst_bg (cost=0.00..507.62 rows=25,381 width=44) (actual time=31.149..63.075 rows=361,901 loops=63)

73. 0.000 3.126 ↓ 0.0 0 1,042

Hash (cost=5.30..5.30 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=1,042)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
74. 0.412 3.126 ↓ 0.0 0 1,042

Nested Loop (cost=0.42..5.30 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=1,042)

  • Join Filter: (((json_arr_elm_1.item ->> 'bronwaarde'::text)) = (json_arr_elm.item ->> 'bronwaarde'::text))
75. 2.084 2.084 ↓ 0.0 0 1,042

Index Scan using bag_onderzoeken__id_volgnummer_key on bag_onderzoeken dst_1 (cost=0.42..2.55 rows=1 width=32) (actual time=0.001..0.002 rows=0 loops=1,042)

  • 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. 0.630 0.630 ↑ 100.0 1 63

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=63)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
77. 4.821 17.388 ↑ 1.0 1 63

Nested Loop (cost=25.42..30.48 rows=1 width=161) (actual time=0.213..0.276 rows=1 loops=63)

  • 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))
  • Rows Removed by Join Filter: 1,098
78. 1.449 1.449 ↓ 2.0 2 63

Index Scan using rel_bag_pnd_bag_ozk_fb10656a_c5625cb292cd152f07c13709330d1712 on rel_bag_pnd_bag_ozk_heeft_onderzoeken (cost=0.42..0.48 rows=1 width=161) (actual time=0.022..0.023 rows=2 loops=63)

  • Index Cond: (((dst_id)::text = (dst_1._id)::text) AND (dst_volgnummer = dst_1.volgnummer))
79. 10.810 11.118 ↓ 3.4 679 102

HashAggregate (cost=25.00..27.00 rows=200 width=36) (actual time=0.007..0.109 rows=679 loops=102)

  • Group Key: (src_entities_1._id)::text, src_entities_1.volgnummer
80. 0.308 0.308 ↑ 1.0 1,000 1

CTE Scan on src_entities src_entities_1 (cost=0.00..20.00 rows=1,000 width=36) (actual time=0.004..0.308 rows=1,000 loops=1)

81. 0.063 0.063 ↑ 1.0 1 63

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

82. 0.063 0.063 ↑ 1.0 1 63

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

Planning time : 17.253 ms
Execution time : 11,966.051 ms