explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2TTG

Settings
# exclusive inclusive rows x rows loops node
1. 5,973.836 5,973.836 ↓ 165.0 165 1

CTE Scan on src_side (cost=4,627,860.63..4,627,860.65 rows=1 width=536) (actual time=5,969.372..5,973.836 rows=165 loops=1)

2.          

CTE all_src_intervals

3. 427.043 5,761.891 ↓ 1.5 349,657 1

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

4. 0.000 519.851 ↑ 1.2 191,939 1

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

  • Workers Planned: 5
  • Workers Launched: 5
5. 32.483 683.767 ↑ 1.5 31,990 6 / 6

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

6. 68.522 68.522 ↑ 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.014..68.522 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,035.540 4,814.997 ↓ 574.0 78,060 11

Merge Join (cost=423,972.96..444,123.48 rows=136 width=56) (actual time=343.364..437.727 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,359.820 1,401.576 ↑ 162.9 14,339 11

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

  • Sort Key: intv.eind_geldigheid, intv._id
  • Sort Method: quicksort Memory: 25kB
10. 41.756 41.756 ↑ 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.002..3.796 rows=31,787 loops=11)

11. 2,130.030 2,377.881 ↓ 1.3 469,419 11

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

  • Sort Key: src.begin_geldigheid, src._id
  • Sort Method: quicksort Memory: 39,605kB
12. 247.851 247.851 ↑ 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.016..247.851 rows=349,657 loops=1)

  • Filter: (begin_geldigheid IS NOT NULL)
13.          

CTE src_entities

14. 0.090 0.886 ↑ 1.0 1,000 1

Limit (cost=0.00..280.01 rows=1,000 width=816) (actual time=0.013..0.886 rows=1,000 loops=1)

15. 0.796 0.796 ↑ 349.7 1,000 1

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

16.          

CTE src_volgnummer_begin_geldigheid

17. 1.051 5,950.752 ↑ 23.5 1,000 1

HashAggregate (cost=7,124.06..7,359.06 rows=23,500 width=44) (actual time=5,950.360..5,950.752 rows=1,000 loops=1)

  • Group Key: all_src_intervals._id, all_src_intervals.volgnummer
18. 68.353 5,949.701 ↑ 58.8 1,000 1

Hash Join (cost=30.00..6,683.44 rows=58,750 width=44) (actual time=2.023..5,949.701 rows=1,000 loops=1)

  • Hash Cond: (((all_src_intervals._id)::text = (src_entities._id)::text) AND (all_src_intervals.volgnummer = src_entities.volgnummer))
19. 5,880.419 5,880.419 ↓ 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.080..5,880.419 rows=349,657 loops=1)

20. 0.161 0.929 ↓ 5.0 1,000 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
21. 0.439 0.768 ↓ 5.0 1,000 1

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

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

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

23.          

CTE max_src_event

24. 0.001 0.020 ↑ 1.0 1 1

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

25.          

Initplan (for Result)

26. 0.003 0.019 ↑ 1.0 1 1

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

27. 0.016 0.016 ↑ 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.016..0.016 rows=1 loops=1)

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

CTE max_dst_event

29. 0.002 0.023 ↑ 1.0 1 1

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

30.          

Initplan (for Result)

31. 0.000 0.021 ↑ 1.0 1 1

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

32. 0.021 0.021 ↑ 361,928.0 1 1

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

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

CTE src_side

34. 0.308 5,973.725 ↓ 165.0 165 1

Nested Loop (cost=156.67..813.01 rows=1 width=484) (actual time=5,969.369..5,973.725 rows=165 loops=1)

35. 0.100 5,973.417 ↓ 165.0 165 1

Nested Loop (cost=156.67..812.89 rows=1 width=368) (actual time=5,969.335..5,973.417 rows=165 loops=1)

36. 0.005 5,973.317 ↓ 165.0 165 1

Nested Loop Left Join (cost=156.67..812.86 rows=1 width=364) (actual time=5,969.311..5,973.317 rows=165 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))
37. 0.097 5,973.147 ↓ 165.0 165 1

Nested Loop (cost=131.53..782.67 rows=1 width=228) (actual time=5,969.303..5,973.147 rows=165 loops=1)

38. 0.324 5,971.940 ↓ 1,110.0 1,110 1

Nested Loop (cost=131.11..780.12 rows=1 width=206) (actual time=5,969.220..5,971.940 rows=1,110 loops=1)

  • Join Filter: (((json_arr_elm_1.item ->> 'bronwaarde'::text)) = (json_arr_elm.item ->> 'bronwaarde'::text))
39. 0.577 5,970.506 ↓ 1,110.0 1,110 1

Hash Right Join (cost=131.10..777.36 rows=1 width=238) (actual time=5,969.199..5,970.506 rows=1,110 loops=1)

  • Hash Cond: (((src_bg._id)::text = (src_2._id)::text) AND (src_bg.volgnummer = src_2.volgnummer))
40. 5,951.102 5,951.102 ↑ 23.5 1,000 1

CTE Scan on src_volgnummer_begin_geldigheid src_bg (cost=0.00..470.00 rows=23,500 width=36) (actual time=5,950.363..5,951.102 rows=1,000 loops=1)

41. 0.336 18.827 ↓ 1,110.0 1,110 1

Hash (cost=131.09..131.09 rows=1 width=238) (actual time=18.827..18.827 rows=1,110 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 193kB
42. 0.929 18.491 ↓ 1,110.0 1,110 1

Merge Join (cost=109.79..131.09 rows=1 width=238) (actual time=16.714..18.491 rows=1,110 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)
43. 0.708 14.465 ↓ 2.2 1,110 1

WindowAgg (cost=87.23..100.98 rows=500 width=118) (actual time=13.669..14.465 rows=1,110 loops=1)

44. 2.987 13.757 ↓ 2.2 1,110 1

Sort (cost=87.23..88.48 rows=500 width=110) (actual time=13.660..13.757 rows=1,110 loops=1)

  • Sort Key: src_entities_2._id, src_entities_2.volgnummer, ((json_arr_elm_1.item ->> 'bronwaarde'::text))
  • Sort Method: quicksort Memory: 146kB
45. 0.721 10.770 ↓ 2.2 1,110 1

HashAggregate (cost=58.57..64.82 rows=500 width=110) (actual time=10.534..10.770 rows=1,110 loops=1)

  • Group Key: src_entities_2._id, dst_1._id, (json_arr_elm_1.item ->> 'bronwaarde'::text), src_entities_2._source, src_entities_2.volgnummer
46. 0.395 10.049 ↓ 2.2 1,110 1

Nested Loop (cost=0.42..51.07 rows=500 width=110) (actual time=0.046..10.049 rows=1,110 loops=1)

47. 0.312 8.544 ↓ 222.0 1,110 1

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

  • Join Filter: ((src_entities_2._application)::text = 'Neuron'::text)
48. 2.232 2.232 ↓ 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.017..2.232 rows=1,000 loops=1)

  • Filter: (_date_deleted IS NULL)
49. 6.000 6.000 ↓ 0.0 0 1,000

Index Scan using bag_ozk_2c5e9e3edb055efb9ce5e2a9588c46f6 on bag_onderzoeken dst_1 (cost=0.42..3.69 rows=2 width=43) (actual time=0.006..0.006 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
50. 1.110 1.110 ↑ 100.0 1 1,110

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,110)

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
51. 2.663 3.097 ↓ 222.0 1,110 1

Sort (cost=22.56..22.57 rows=5 width=188) (actual time=3.037..3.097 rows=1,110 loops=1)

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

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

  • Filter: ((_application)::text = 'Neuron'::text)
53. 1.110 1.110 ↑ 100.0 1 1,110

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

  • Filter: ((item ->> 'bronwaarde'::text) IS NOT NULL)
54. 1.110 1.110 ↓ 0.0 0 1,110

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

  • Index Cond: (((_id)::text = (dst_1._id)::text) AND (_id IS NOT NULL) AND (volgnummer = (max(dst_1.volgnummer))))
  • Filter: (_date_deleted IS NULL)
55. 0.165 0.165 ↓ 0.0 0 165

Nested Loop (cost=25.14..30.17 rows=1 width=204) (actual time=0.001..0.001 rows=0 loops=165)

  • 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))
56. 0.000 0.000 ↓ 0.0 0 165

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.000..0.000 rows=0 loops=165)

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

HashAggregate (cost=25.00..27.00 rows=200 width=36) (never executed)

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

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

59. 0.000 0.000 ↑ 1.0 1 165

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

60. 0.000 0.000 ↑ 1.0 1 165

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

Planning time : 9.754 ms
Execution time : 5,993.786 ms