explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j50E

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 96,553.271 ↑ 29.5 180 1

Append (cost=76,279,499.62..82,573,816.64 rows=5,310 width=8) (actual time=81,956.949..96,553.271 rows=180 loops=1)

2.          

CTE event_objects

3. 7.496 7.496 ↓ 36.0 36 1

Index Scan using global_event_global_event_time_index on global_event ge (cost=0.44..10.79 rows=1 width=8) (actual time=0.102..7.496 rows=36 loops=1)

  • Index Cond: (global_event_time > (now() - '12:00:00'::interval))
  • Filter: ((entityordinal = 1) AND (eventtypeordinal = 2) AND ((global_event_changeddata -> 'is_published'::text) = 't'::text))
  • Rows Removed by Filter: 1,303
4. 1.782 81,956.965 ↑ 29.5 60 1

HashAggregate (cost=76,279,488.83..76,279,506.53 rows=1,770 width=8) (actual time=81,956.948..81,956.965 rows=60 loops=1)

  • Group Key: ur.user_id
5. 34,625.308 81,955.183 ↑ 6,992.4 15,003 1

Hash Join (cost=423,097.46..76,017,222.11 rows=104,906,688 width=8) (actual time=64,884.526..81,955.183 rows=15,003 loops=1)

  • Hash Cond: (COALESCE(ucea.project_id, pppgpggroupchild.project_id, p.projectid) = pf.project_id)
6. 42,507.420 44,864.917 ↑ 12.0 420,994,109 1

Hash Join (cost=93,419.05..61,262,874.05 rows=5,050,507,710 width=32) (actual time=1,602.844..44,864.917 rows=420,994,109 loops=1)

  • Hash Cond: (ur.role_id = rpa.role_id)
7. 450.903 2,317.982 ↑ 4.6 2,588,920 1

Merge Left Join (cost=90,102.46..270,009.46 rows=11,982,252 width=40) (actual time=1,563.211..2,317.982 rows=2,588,920 loops=1)

  • Merge Cond: (ucea.project_group_uid = pppgpggroupchild.parent_project_group_uid)
8. 981.523 1,861.722 ↓ 74.6 2,585,186 1

Sort (cost=31,742.20..31,828.81 rows=34,644 width=48) (actual time=1,558.274..1,861.722 rows=2,585,186 loops=1)

  • Sort Key: ucea.project_group_uid
  • Sort Method: external merge Disk: 86,032kB
9. 499.956 880.199 ↓ 74.6 2,585,186 1

Hash Join (cost=27,255.67..29,129.98 rows=34,644 width=48) (actual time=15.824..880.199 rows=2,585,186 loops=1)

  • Hash Cond: (ucea.usergroup_id = ur.user_group_id)
10. 132.574 378.911 ↓ 96.2 853,329 1

Hash Left Join (cost=27,067.16..27,574.86 rows=8,871 width=40) (actual time=14.476..378.911 rows=853,329 loops=1)

  • Hash Cond: (ucea.project_id = pppg.project_id)
11. 134.166 241.507 ↓ 96.2 853,329 1

Merge Left Join (cost=378.28..817.78 rows=8,871 width=40) (actual time=9.222..241.507 rows=853,329 loops=1)

  • Merge Cond: (ucea.client_id = pg.clientid)
12. 1.964 1.964 ↑ 1.1 6,385 1

Index Scan using usergroup_client_entities_association_client_id_index on usergroup_client_entities_association ucea (cost=0.28..289.84 rows=6,751 width=40) (actual time=0.009..1.964 rows=6,385 loops=1)

13. 96.676 105.377 ↓ 248.7 849,184 1

Sort (cost=377.99..386.53 rows=3,415 width=16) (actual time=9.208..105.377 rows=849,184 loops=1)

  • Sort Key: pg.clientid
  • Sort Method: quicksort Memory: 254kB
14. 8.089 8.701 ↑ 1.0 3,368 1

Hash Join (cost=31.42..177.57 rows=3,415 width=16) (actual time=0.242..8.701 rows=3,368 loops=1)

  • Hash Cond: (p.projectgroupuid = pg.projectgroupuid)
15. 0.386 0.386 ↑ 1.0 3,368 1

Seq Scan on project p (cost=0.00..137.15 rows=3,415 width=24) (actual time=0.009..0.386 rows=3,368 loops=1)

16. 0.103 0.226 ↓ 1.0 806 1

Hash (cost=21.74..21.74 rows=774 width=24) (actual time=0.226..0.226 rows=806 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 53kB
17. 0.123 0.123 ↓ 1.0 806 1

Seq Scan on projectgroup pg (cost=0.00..21.74 rows=774 width=24) (actual time=0.005..0.123 rows=806 loops=1)

18. 0.671 4.830 ↑ 114.9 3,428 1

Hash (cost=21,766.97..21,766.97 rows=393,753 width=8) (actual time=4.830..4.830 rows=3,428 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 4,230kB
19. 0.275 4.159 ↑ 114.9 3,428 1

Subquery Scan on pppg (cost=11,906.07..21,766.97 rows=393,753 width=8) (actual time=2.631..4.159 rows=3,428 loops=1)

20. 0.826 3.884 ↑ 114.9 3,428 1

Merge Join (cost=11,906.07..17,829.44 rows=393,753 width=32) (actual time=2.630..3.884 rows=3,428 loops=1)

  • Merge Cond: (p_1.projectgroupuid = pgparents.projectgroupuid)
21.          

CTE pgparents

22. 0.135 0.658 ↑ 81.9 955 1

Recursive Union (cost=0.00..3,651.66 rows=78,174 width=40) (actual time=0.008..0.658 rows=955 loops=1)

23. 0.154 0.154 ↓ 1.0 806 1

Seq Scan on projectgroup pg_1 (cost=0.00..21.74 rows=774 width=40) (actual time=0.007..0.154 rows=806 loops=1)

24. 0.087 0.369 ↑ 154.8 50 3

Hash Join (cost=31.42..206.64 rows=7,740 width=40) (actual time=0.082..0.123 rows=50 loops=3)

  • Hash Cond: (pgparents_1_1.parentuid = pg2.projectgroupuid)
25. 0.051 0.051 ↑ 24.3 318 3

WorkTable Scan on pgparents pgparents_1_1 (cost=0.00..154.80 rows=7,740 width=32) (actual time=0.000..0.017 rows=318 loops=3)

26. 0.106 0.231 ↓ 1.0 806 1

Hash (cost=21.74..21.74 rows=774 width=40) (actual time=0.231..0.231 rows=806 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
27. 0.125 0.125 ↓ 1.0 806 1

Seq Scan on projectgroup pg2 (cost=0.00..21.74 rows=774 width=40) (actual time=0.005..0.125 rows=806 loops=1)

28. 0.944 1.772 ↑ 1.0 3,368 1

Sort (cost=337.57..346.11 rows=3,415 width=24) (actual time=1.519..1.772 rows=3,368 loops=1)

  • Sort Key: p_1.projectgroupuid
  • Sort Method: quicksort Memory: 360kB
29. 0.828 0.828 ↑ 1.0 3,368 1

Seq Scan on project p_1 (cost=0.00..137.15 rows=3,415 width=24) (actual time=0.009..0.828 rows=3,368 loops=1)

30. 0.403 1.286 ↑ 21.0 3,717 1

Sort (cost=7,916.84..8,112.27 rows=78,174 width=16) (actual time=1.108..1.286 rows=3,717 loops=1)

  • Sort Key: pgparents.projectgroupuid
  • Sort Method: quicksort Memory: 69kB
31. 0.883 0.883 ↑ 81.9 955 1

CTE Scan on pgparents (cost=0.00..1,563.48 rows=78,174 width=16) (actual time=0.010..0.883 rows=955 loops=1)

32. 0.624 1.332 ↑ 1.1 4,834 1

Hash (cost=124.34..124.34 rows=5,134 width=24) (actual time=1.332..1.332 rows=4,834 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 329kB
33. 0.708 0.708 ↑ 1.1 4,834 1

Seq Scan on users_roles ur (cost=0.00..124.34 rows=5,134 width=24) (actual time=0.011..0.708 rows=4,834 loops=1)

34. 1.118 5.357 ↑ 49.2 7,996 1

Sort (cost=58,360.27..59,344.65 rows=393,753 width=24) (actual time=4.927..5.357 rows=7,996 loops=1)

  • Sort Key: pppgpggroupchild.parent_project_group_uid
  • Sort Method: quicksort Memory: 364kB
35. 0.304 4.239 ↑ 114.9 3,428 1

Subquery Scan on pppgpggroupchild (cost=11,906.07..21,766.97 rows=393,753 width=24) (actual time=2.706..4.239 rows=3,428 loops=1)

36. 0.861 3.935 ↑ 114.9 3,428 1

Merge Join (cost=11,906.07..17,829.44 rows=393,753 width=32) (actual time=2.705..3.935 rows=3,428 loops=1)

  • Merge Cond: (p_2.projectgroupuid = pgparents_1.projectgroupuid)
37.          

CTE pgparents

38. 0.128 0.675 ↑ 81.9 955 1

Recursive Union (cost=0.00..3,651.66 rows=78,174 width=40) (actual time=0.009..0.675 rows=955 loops=1)

39. 0.160 0.160 ↓ 1.0 806 1

Seq Scan on projectgroup pg_2 (cost=0.00..21.74 rows=774 width=40) (actual time=0.008..0.160 rows=806 loops=1)

40. 0.089 0.387 ↑ 154.8 50 3

Hash Join (cost=31.42..206.64 rows=7,740 width=40) (actual time=0.088..0.129 rows=50 loops=3)

  • Hash Cond: (pgparents_1_2.parentuid = pg2_1.projectgroupuid)
41. 0.051 0.051 ↑ 24.3 318 3

WorkTable Scan on pgparents pgparents_1_2 (cost=0.00..154.80 rows=7,740 width=32) (actual time=0.001..0.017 rows=318 loops=3)

42. 0.114 0.247 ↓ 1.0 806 1

Hash (cost=21.74..21.74 rows=774 width=40) (actual time=0.247..0.247 rows=806 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
43. 0.133 0.133 ↓ 1.0 806 1

Seq Scan on projectgroup pg2_1 (cost=0.00..21.74 rows=774 width=40) (actual time=0.004..0.133 rows=806 loops=1)

44. 0.871 1.740 ↑ 1.0 3,368 1

Sort (cost=337.57..346.11 rows=3,415 width=24) (actual time=1.546..1.740 rows=3,368 loops=1)

  • Sort Key: p_2.projectgroupuid
  • Sort Method: quicksort Memory: 360kB
45. 0.869 0.869 ↑ 1.0 3,368 1

Seq Scan on project p_2 (cost=0.00..137.15 rows=3,415 width=24) (actual time=0.013..0.869 rows=3,368 loops=1)

46. 0.424 1.334 ↑ 21.0 3,717 1

Sort (cost=7,916.84..8,112.27 rows=78,174 width=32) (actual time=1.155..1.334 rows=3,717 loops=1)

  • Sort Key: pgparents_1.projectgroupuid
  • Sort Method: quicksort Memory: 74kB
47. 0.910 0.910 ↑ 81.9 955 1

CTE Scan on pgparents pgparents_1 (cost=0.00..1,563.48 rows=78,174 width=32) (actual time=0.011..0.910 rows=955 loops=1)

48. 3.446 39.515 ↑ 2.9 30,572 1

Hash (cost=2,215.86..2,215.86 rows=88,058 width=8) (actual time=39.515..39.515 rows=30,572 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 2,219kB
49. 17.761 36.069 ↑ 2.9 30,815 1

Hash Join (cost=3.76..2,215.86 rows=88,058 width=8) (actual time=0.050..36.069 rows=30,815 loops=1)

  • Hash Cond: (rpa.entity_id = t.tablesequenceid)
50. 18.278 18.278 ↑ 1.0 88,058 1

Seq Scan on role_permissions_association rpa (cost=0.00..1,976.58 rows=88,058 width=16) (actual time=0.012..18.278 rows=88,058 loops=1)

51. 0.011 0.030 ↓ 1.0 79 1

Hash (cost=2.78..2.78 rows=78 width=8) (actual time=0.030..0.030 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
52. 0.019 0.019 ↓ 1.0 79 1

Seq Scan on tablemetadata t (cost=0.00..2.78 rows=78 width=8) (actual time=0.005..0.019 rows=79 loops=1)

53. 0.004 2,464.958 ↑ 2.3 3 1

Hash (cost=329,678.33..329,678.33 rows=7 width=8) (actual time=2,464.958..2,464.958 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
54. 0.023 2,464.954 ↑ 2.3 3 1

HashAggregate (cost=329,678.26..329,678.33 rows=7 width=8) (actual time=2,464.954..2,464.954 rows=3 loops=1)

  • Group Key: pf.project_id
55. 52.712 2,464.931 ↓ 9.9 69 1

Hash Semi Join (cost=234,980.30..329,678.24 rows=7 width=8) (actual time=2,464.343..2,464.931 rows=69 loops=1)

  • Hash Cond: (i.issueid = event_objects.objectid)
56. 1,095.561 2,404.708 ↑ 1.1 788,531 1

Hash Join (cost=234,980.27..327,355.37 rows=884,863 width=24) (actual time=1,047.613..2,404.708 rows=788,531 loops=1)

  • Hash Cond: (pf.finding_id = f.findingid)
57. 314.764 314.764 ↑ 1.0 4,150,639 1

Seq Scan on project_finding pf (cost=0.00..67,959.07 rows=4,151,307 width=16) (actual time=0.007..314.764 rows=4,150,639 loops=1)

58. 228.838 994.383 ↓ 1.0 763,487 1

Hash (cost=225,461.72..225,461.72 rows=761,484 width=24) (actual time=994.383..994.383 rows=763,487 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 49,946kB
59. 213.424 765.545 ↓ 1.0 763,487 1

Merge Join (cost=24,951.24..225,461.72 rows=761,484 width=24) (actual time=290.351..765.545 rows=763,487 loops=1)

  • Merge Cond: (i.issueid = f.issueid)
60. 37.573 301.828 ↓ 1.0 122,745 1

Sort (cost=24,949.96..25,253.38 rows=121,371 width=8) (actual time=290.309..301.828 rows=122,745 loops=1)

  • Sort Key: i.issueid
  • Sort Method: quicksort Memory: 8,826kB
61. 34.566 264.255 ↓ 1.0 122,745 1

Hash Left Join (cost=13,472.76..14,700.74 rows=121,371 width=8) (actual time=214.284..264.255 rows=122,745 loops=1)

  • Hash Cond: (mf.masterfindingid = mfg.masterfindingid)
62. 15.410 228.141 ↓ 1.0 122,712 1

Hash Right Join (cost=13,272.23..13,487.08 rows=121,371 width=16) (actual time=212.721..228.141 rows=122,712 loops=1)

  • Hash Cond: (cpmv.masterfindingid = mf.masterfindingid)
63. 0.197 0.197 ↑ 1.1 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv (cost=0.00..40.35 rows=2,435 width=8) (actual time=0.011..0.197 rows=2,131 loops=1)

64. 15.299 212.534 ↓ 1.0 122,073 1

Hash (cost=11,755.10..11,755.10 rows=121,371 width=16) (actual time=212.534..212.534 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,747kB
65. 23.242 197.235 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,755.10 rows=121,371 width=16) (actual time=78.191..197.235 rows=122,073 loops=1)

  • Hash Cond: (mf.master_finding_parent_id = mfp.id)
66. 46.694 169.949 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,704.91 rows=121,371 width=24) (actual time=74.114..169.949 rows=122,173 loops=1)

  • Hash Cond: (i.masterfindingid = mf.masterfindingid)
67. 38.671 106.411 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,402.45 rows=121,371 width=16) (actual time=57.237..106.411 rows=122,173 loops=1)

  • Hash Cond: (i.assetid = a.assetid)
68. 10.558 10.558 ↑ 1.0 120,102 1

Seq Scan on issue i (cost=0.00..3,265.79 rows=121,379 width=24) (actual time=0.006..10.558 rows=120,102 loops=1)

69. 5.419 57.182 ↓ 1.0 43,047 1

Hash (cost=1,931.39..1,931.39 rows=42,911 width=8) (actual time=57.182..57.182 rows=43,047 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,194kB
70. 17.864 51.763 ↓ 1.0 43,047 1

Hash Join (cost=25.92..1,931.39 rows=42,911 width=8) (actual time=0.251..51.763 rows=43,047 loops=1)

  • Hash Cond: (a.zoneid = z.zoneid)
71. 4.612 33.690 ↓ 1.0 43,053 1

Merge Left Join (cost=0.57..1,792.69 rows=42,914 width=16) (actual time=0.038..33.690 rows=43,053 loops=1)

  • Merge Cond: (a.assetid = aa.asset_id)
72. 22.982 29.003 ↓ 1.0 42,937 1

Merge Left Join (cost=0.43..1,663.54 rows=42,914 width=16) (actual time=0.025..29.003 rows=42,937 loops=1)

  • Merge Cond: (a.assetid = apl.asset_id)
73. 6.005 6.005 ↓ 1.0 42,927 1

Index Only Scan using asset_assetid_zoneid_index on asset a (cost=0.29..1,543.38 rows=42,914 width=16) (actual time=0.016..6.005 rows=42,927 loops=1)

  • Heap Fetches: 4,876
74. 0.016 0.016 ↑ 1.0 27 1

Index Only Scan using asset_physical_location_mav_unique on asset_physical_location_mav apl (cost=0.14..12.54 rows=27 width=8) (actual time=0.006..0.016 rows=27 loops=1)

  • Heap Fetches: 27
75. 0.075 0.075 ↓ 1.1 317 1

Index Only Scan using fki_asset_id_fk on application_asset aa (cost=0.15..18.32 rows=283 width=8) (actual time=0.012..0.075 rows=317 loops=1)

  • Heap Fetches: 158
76. 0.081 0.209 ↓ 1.0 686 1

Hash (cost=16.82..16.82 rows=682 width=16) (actual time=0.209..0.209 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
77. 0.128 0.128 ↓ 1.0 686 1

Seq Scan on zone z (cost=0.00..16.82 rows=682 width=16) (actual time=0.006..0.128 rows=686 loops=1)

78. 3.013 16.844 ↓ 1.0 19,737 1

Hash (cost=2,738.91..2,738.91 rows=19,591 width=24) (actual time=16.844..16.844 rows=19,737 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
79. 13.831 13.831 ↓ 1.0 19,737 1

Seq Scan on masterfinding mf (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.008..13.831 rows=19,737 loops=1)

80. 2.023 4.044 ↓ 1.0 18,503 1

Hash (cost=500.51..500.51 rows=18,480 width=8) (actual time=4.044..4.044 rows=18,503 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
81. 2.021 2.021 ↓ 1.0 18,503 1

Index Only Scan using master_finding_parent_id_key on master_finding_parent mfp (cost=0.29..500.51 rows=18,480 width=8) (actual time=0.020..2.021 rows=18,503 loops=1)

  • Heap Fetches: 563
82. 0.761 1.548 ↑ 1.0 6,558 1

Hash (cost=116.90..116.90 rows=6,690 width=16) (actual time=1.548..1.548 rows=6,558 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 372kB
83. 0.787 0.787 ↓ 1.0 6,738 1

Seq Scan on masterfindingcategory mfg (cost=0.00..116.90 rows=6,690 width=16) (actual time=0.006..0.787 rows=6,738 loops=1)

84. 250.293 250.293 ↓ 1.0 763,562 1

Index Scan using issueid_id_final_remediated_state on finding f (cost=0.42..215,462.42 rows=761,534 width=24) (actual time=0.020..250.293 rows=763,562 loops=1)

85. 0.006 7.511 ↓ 36.0 36 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=7.511..7.511 rows=36 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
86. 7.505 7.505 ↓ 36.0 36 1

CTE Scan on event_objects (cost=0.00..0.02 rows=1 width=8) (actual time=0.103..7.505 rows=36 loops=1)

87. 13.923 7,327.526 ↑ 29.5 60 1

HashAggregate (cost=915,124.06..915,141.76 rows=1,770 width=8) (actual time=7,327.511..7,327.526 rows=60 loops=1)

  • Group Key: ur_1.user_id
88. 14.764 7,313.603 ↑ 69.6 119,835 1

Hash Join (cost=792,854.99..894,287.52 rows=8,334,616 width=8) (actual time=7,291.576..7,313.603 rows=119,835 loops=1)

  • Hash Cond: (ur_1.role_id = rpa_1.role_id)
89. 3.054 7,277.149 ↑ 30.1 656 1

Hash Left Join (cost=789,538.41..790,321.46 rows=19,774 width=16) (actual time=7,269.757..7,277.149 rows=656 loops=1)

  • Hash Cond: (ucea_1.client_id = ag.clientid)
  • Filter: ((hashed SubPlan 6) OR (hashed SubPlan 8))
  • Rows Removed by Filter: 20,332
90. 2.437 7.724 ↑ 2.4 10,830 1

Merge Join (cost=928.50..1,555.58 rows=26,365 width=40) (actual time=3.319..7.724 rows=10,830 loops=1)

  • Merge Cond: (ur_1.user_group_id = ucea_1.usergroup_id)
91. 1.225 1.225 ↑ 1.1 4,834 1

Index Scan using users_roles_user_group_id_index on users_roles ur_1 (cost=0.28..219.35 rows=5,134 width=24) (actual time=0.019..1.225 rows=4,834 loops=1)

92. 1.778 4.062 ↓ 2.1 14,083 1

Sort (cost=928.22..945.09 rows=6,751 width=32) (actual time=3.297..4.062 rows=14,083 loops=1)

  • Sort Key: ucea_1.usergroup_id
  • Sort Method: quicksort Memory: 498kB
93. 1.004 2.284 ↑ 1.0 6,513 1

Merge Left Join (cost=24.80..498.82 rows=6,751 width=32) (actual time=0.140..2.284 rows=6,513 loops=1)

  • Merge Cond: (ucea_1.application_group_id = agroupchild.applicationgroupid)
94. 1.143 1.143 ↑ 1.1 6,385 1

Index Scan using usergroup_client_entities_association_agid_index on usergroup_client_entities_association ucea_1 (cost=0.28..453.62 rows=6,751 width=32) (actual time=0.010..1.143 rows=6,385 loops=1)

95. 0.063 0.137 ↑ 1.5 215 1

Sort (cost=24.52..25.32 rows=320 width=16) (actual time=0.127..0.137 rows=215 loops=1)

  • Sort Key: agroupchild.applicationgroupid
  • Sort Method: quicksort Memory: 40kB
96. 0.074 0.074 ↓ 1.0 332 1

Seq Scan on application agroupchild (cost=0.00..11.20 rows=320 width=16) (actual time=0.010..0.074 rows=332 loops=1)

97. 0.040 0.341 ↓ 1.0 332 1

Hash (cost=32.94..32.94 rows=320 width=16) (actual time=0.341..0.341 rows=332 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
98. 0.089 0.301 ↓ 1.0 332 1

Hash Join (cost=20.89..32.94 rows=320 width=16) (actual time=0.189..0.301 rows=332 loops=1)

  • Hash Cond: (a_1.applicationgroupid = ag.applicationgroupid)
99. 0.035 0.035 ↓ 1.0 332 1

Seq Scan on application a_1 (cost=0.00..11.20 rows=320 width=16) (actual time=0.006..0.035 rows=332 loops=1)

100. 0.069 0.177 ↓ 1.0 587 1

Hash (cost=13.73..13.73 rows=573 width=16) (actual time=0.177..0.177 rows=587 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
101. 0.108 0.108 ↓ 1.0 587 1

Seq Scan on applicationgroup ag (cost=0.00..13.73 rows=573 width=16) (actual time=0.006..0.108 rows=587 loops=1)

102.          

SubPlan (for Hash Left Join)

103. 48.315 3,602.043 ↓ 9.9 69 1

Merge Semi Join (cost=25,955.59..394,287.43 rows=7 width=8) (actual time=3,601.996..3,602.043 rows=69 loops=1)

  • Merge Cond: (i_1.issueid = event_objects_1.objectid)
104. 219.601 3,553.706 ↑ 1.1 788,528 1

Merge Join (cost=25,955.56..392,075.17 rows=884,863 width=24) (actual time=238.804..3,553.706 rows=788,528 loops=1)

  • Merge Cond: (f_1.issueid = i_1.issueid)
105. 2,106.524 3,052.491 ↑ 1.1 782,318 1

Gather Merge (cost=1,000.45..397,567.96 rows=884,921 width=8) (actual time=18.823..3,052.491 rows=782,318 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
106. 78.184 945.967 ↑ 1.4 260,845 3 / 3

Nested Loop (cost=0.42..294,426.10 rows=368,717 width=8) (actual time=0.068..945.967 rows=260,845 loops=3)

107. 110.266 110.266 ↑ 1.3 252,506 3 / 3

Parallel Index Scan using issueid_id_final_remediated_state on finding f_1 (cost=0.42..211,020.14 rows=317,306 width=24) (actual time=0.027..110.266 rows=252,506 loops=3)

108. 757.517 757.517 ↑ 1.0 1 757,517 / 3

Index Scan using project_finding_finding_id_hash on project_finding pf_1 (cost=0.00..0.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=757,517)

  • Index Cond: (finding_id = f_1.findingid)
  • Rows Removed by Index Recheck: 0
109. 88.755 281.614 ↓ 7.3 889,251 1

Sort (cost=24,951.89..25,255.32 rows=121,371 width=16) (actual time=219.955..281.614 rows=889,251 loops=1)

  • Sort Key: i_1.issueid
  • Sort Method: quicksort Memory: 8,826kB
110. 16.757 192.859 ↓ 1.0 122,745 1

Hash Left Join (cost=13,474.69..14,702.68 rows=121,371 width=16) (actual time=160.256..192.859 rows=122,745 loops=1)

  • Hash Cond: (mf_1.masterfindingid = mfg_1.masterfindingid)
111. 15.795 174.530 ↓ 1.0 122,712 1

Hash Right Join (cost=13,274.17..13,489.02 rows=121,371 width=24) (actual time=158.670..174.530 rows=122,712 loops=1)

  • Hash Cond: (cpmv_1.masterfindingid = mf_1.masterfindingid)
112. 0.198 0.198 ↑ 1.1 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_1 (cost=0.00..40.35 rows=2,435 width=8) (actual time=0.012..0.198 rows=2,131 loops=1)

113. 16.173 158.537 ↓ 1.0 122,073 1

Hash (cost=11,757.03..11,757.03 rows=121,371 width=24) (actual time=158.537..158.537 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,861kB
114. 22.525 142.364 ↓ 1.0 122,073 1

Hash Join (cost=6,185.01..11,757.03 rows=121,371 width=24) (actual time=51.252..142.364 rows=122,073 loops=1)

  • Hash Cond: (mf_1.master_finding_parent_id = mfp_1.id)
115. 24.822 115.751 ↓ 1.0 122,173 1

Hash Join (cost=5,453.50..10,706.84 rows=121,371 width=32) (actual time=47.131..115.751 rows=122,173 loops=1)

  • Hash Cond: (i_1.masterfindingid = mf_1.masterfindingid)
116. 33.499 74.031 ↓ 1.0 122,173 1

Hash Join (cost=2,469.71..7,404.38 rows=121,371 width=24) (actual time=30.200..74.031 rows=122,173 loops=1)

  • Hash Cond: (i_1.assetid = a_2.assetid)
117. 10.397 10.397 ↑ 1.0 120,102 1

Seq Scan on issue i_1 (cost=0.00..3,265.79 rows=121,379 width=24) (actual time=0.007..10.397 rows=120,102 loops=1)

118. 5.806 30.135 ↓ 1.0 43,047 1

Hash (cost=1,933.32..1,933.32 rows=42,911 width=16) (actual time=30.135..30.135 rows=43,047 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,196kB
119. 7.145 24.329 ↓ 1.0 43,047 1

Hash Join (cost=25.92..1,933.32 rows=42,911 width=16) (actual time=0.254..24.329 rows=43,047 loops=1)

  • Hash Cond: (a_2.zoneid = z_1.zoneid)
120. 5.302 16.974 ↓ 1.0 43,053 1

Merge Left Join (cost=0.57..1,794.62 rows=42,914 width=24) (actual time=0.039..16.974 rows=43,053 loops=1)

  • Merge Cond: (a_2.assetid = aa_1.asset_id)
121. 5.424 11.594 ↓ 1.0 42,937 1

Merge Left Join (cost=0.43..1,663.54 rows=42,914 width=16) (actual time=0.028..11.594 rows=42,937 loops=1)

  • Merge Cond: (a_2.assetid = apl_1.asset_id)
122. 6.152 6.152 ↓ 1.0 42,927 1

Index Only Scan using asset_assetid_zoneid_index on asset a_2 (cost=0.29..1,543.38 rows=42,914 width=16) (actual time=0.017..6.152 rows=42,927 loops=1)

  • Heap Fetches: 4,876
123. 0.018 0.018 ↑ 1.0 27 1

Index Only Scan using asset_physical_location_mav_unique on asset_physical_location_mav apl_1 (cost=0.14..12.54 rows=27 width=8) (actual time=0.008..0.018 rows=27 loops=1)

  • Heap Fetches: 27
124. 0.078 0.078 ↓ 1.1 317 1

Index Scan using fki_asset_id_fk on application_asset aa_1 (cost=0.15..20.26 rows=283 width=16) (actual time=0.008..0.078 rows=317 loops=1)

125. 0.080 0.210 ↓ 1.0 686 1

Hash (cost=16.82..16.82 rows=682 width=16) (actual time=0.210..0.210 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
126. 0.130 0.130 ↓ 1.0 686 1

Seq Scan on zone z_1 (cost=0.00..16.82 rows=682 width=16) (actual time=0.007..0.130 rows=686 loops=1)

127. 3.181 16.898 ↓ 1.0 19,737 1

Hash (cost=2,738.91..2,738.91 rows=19,591 width=24) (actual time=16.898..16.898 rows=19,737 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
128. 13.717 13.717 ↓ 1.0 19,737 1

Seq Scan on masterfinding mf_1 (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.008..13.717 rows=19,737 loops=1)

129. 2.056 4.088 ↓ 1.0 18,503 1

Hash (cost=500.51..500.51 rows=18,480 width=8) (actual time=4.088..4.088 rows=18,503 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
130. 2.032 2.032 ↓ 1.0 18,503 1

Index Only Scan using master_finding_parent_id_key on master_finding_parent mfp_1 (cost=0.29..500.51 rows=18,480 width=8) (actual time=0.019..2.032 rows=18,503 loops=1)

  • Heap Fetches: 563
131. 0.765 1.572 ↑ 1.0 6,558 1

Hash (cost=116.90..116.90 rows=6,690 width=16) (actual time=1.572..1.572 rows=6,558 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 372kB
132. 0.807 0.807 ↓ 1.0 6,738 1

Seq Scan on masterfindingcategory mfg_1 (cost=0.00..116.90 rows=6,690 width=16) (actual time=0.007..0.807 rows=6,738 loops=1)

133. 0.017 0.022 ↓ 36.0 36 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.018..0.022 rows=36 loops=1)

  • Sort Key: event_objects_1.objectid
  • Sort Method: quicksort Memory: 26kB
134. 0.005 0.005 ↓ 36.0 36 1

CTE Scan on event_objects event_objects_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.005 rows=36 loops=1)

135. 48.732 3,663.987 ↓ 9.9 69 1

Merge Semi Join (cost=25,953.65..394,285.50 rows=7 width=8) (actual time=3,663.936..3,663.987 rows=69 loops=1)

  • Merge Cond: (i_2.issueid = event_objects_2.objectid)
136. 234.859 3,615.231 ↑ 1.1 788,528 1

Merge Join (cost=25,953.62..392,073.23 rows=884,863 width=24) (actual time=260.899..3,615.231 rows=788,528 loops=1)

  • Merge Cond: (f_2.issueid = i_2.issueid)
137. 2,131.647 3,076.949 ↑ 1.1 782,318 1

Gather Merge (cost=1,000.45..397,567.96 rows=884,921 width=8) (actual time=39.772..3,076.949 rows=782,318 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
138. 73.415 945.302 ↑ 1.4 260,845 3 / 3

Nested Loop (cost=0.42..294,426.10 rows=368,717 width=8) (actual time=0.057..945.302 rows=260,845 loops=3)

139. 114.370 114.370 ↑ 1.3 252,506 3 / 3

Parallel Index Scan using issueid_id_final_remediated_state on finding f_2 (cost=0.42..211,020.14 rows=317,306 width=24) (actual time=0.026..114.370 rows=252,506 loops=3)

140. 757.517 757.517 ↑ 1.0 1 757,517 / 3

Index Scan using project_finding_finding_id_hash on project_finding pf_2 (cost=0.00..0.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=757,517)

  • Index Cond: (finding_id = f_2.findingid)
  • Rows Removed by Index Recheck: 0
141. 109.421 303.423 ↓ 7.3 889,251 1

Sort (cost=24,949.96..25,253.38 rows=121,371 width=16) (actual time=221.101..303.423 rows=889,251 loops=1)

  • Sort Key: i_2.issueid
  • Sort Method: quicksort Memory: 8,826kB
142. 17.134 194.002 ↓ 1.0 122,745 1

Hash Left Join (cost=13,472.76..14,700.74 rows=121,371 width=16) (actual time=159.574..194.002 rows=122,745 loops=1)

  • Hash Cond: (mf_2.masterfindingid = mfg_2.masterfindingid)
143. 17.257 175.305 ↓ 1.0 122,712 1

Hash Right Join (cost=13,272.23..13,487.08 rows=121,371 width=24) (actual time=157.988..175.305 rows=122,712 loops=1)

  • Hash Cond: (cpmv_2.masterfindingid = mf_2.masterfindingid)
144. 0.194 0.194 ↑ 1.1 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_2 (cost=0.00..40.35 rows=2,435 width=8) (actual time=0.012..0.194 rows=2,131 loops=1)

145. 16.411 157.854 ↓ 1.0 122,073 1

Hash (cost=11,755.10..11,755.10 rows=121,371 width=24) (actual time=157.854..157.854 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,661kB
146. 22.705 141.443 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,755.10 rows=121,371 width=24) (actual time=50.030..141.443 rows=122,073 loops=1)

  • Hash Cond: (mf_2.master_finding_parent_id = mfp_2.id)
147. 24.817 114.669 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,704.91 rows=121,371 width=32) (actual time=45.930..114.669 rows=122,173 loops=1)

  • Hash Cond: (i_2.masterfindingid = mf_2.masterfindingid)
148. 33.723 73.206 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,402.45 rows=121,371 width=24) (actual time=29.250..73.206 rows=122,173 loops=1)

  • Hash Cond: (i_2.assetid = a_3.assetid)
149. 10.299 10.299 ↑ 1.0 120,102 1

Seq Scan on issue i_2 (cost=0.00..3,265.79 rows=121,379 width=24) (actual time=0.008..10.299 rows=120,102 loops=1)

150. 5.856 29.184 ↓ 1.0 43,047 1

Hash (cost=1,931.39..1,931.39 rows=42,911 width=16) (actual time=29.183..29.184 rows=43,047 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,512kB
151. 7.383 23.328 ↓ 1.0 43,047 1

Hash Join (cost=25.92..1,931.39 rows=42,911 width=16) (actual time=0.250..23.328 rows=43,047 loops=1)

  • Hash Cond: (a_3.zoneid = z_2.zoneid)
152. 4.693 15.738 ↓ 1.0 43,053 1

Merge Left Join (cost=0.57..1,792.69 rows=42,914 width=16) (actual time=0.038..15.738 rows=43,053 loops=1)

  • Merge Cond: (a_3.assetid = aa_2.asset_id)
153. 4.962 10.972 ↓ 1.0 42,937 1

Merge Left Join (cost=0.43..1,663.54 rows=42,914 width=16) (actual time=0.024..10.972 rows=42,937 loops=1)

  • Merge Cond: (a_3.assetid = apl_2.asset_id)
154. 5.995 5.995 ↓ 1.0 42,927 1

Index Only Scan using asset_assetid_zoneid_index on asset a_3 (cost=0.29..1,543.38 rows=42,914 width=16) (actual time=0.014..5.995 rows=42,927 loops=1)

  • Heap Fetches: 4,876
155. 0.015 0.015 ↑ 1.0 27 1

Index Only Scan using asset_physical_location_mav_unique on asset_physical_location_mav apl_2 (cost=0.14..12.54 rows=27 width=8) (actual time=0.006..0.015 rows=27 loops=1)

  • Heap Fetches: 27
156. 0.073 0.073 ↓ 1.1 317 1

Index Only Scan using fki_asset_id_fk on application_asset aa_2 (cost=0.15..18.32 rows=283 width=8) (actual time=0.012..0.073 rows=317 loops=1)

  • Heap Fetches: 158
157. 0.081 0.207 ↓ 1.0 686 1

Hash (cost=16.82..16.82 rows=682 width=16) (actual time=0.207..0.207 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
158. 0.126 0.126 ↓ 1.0 686 1

Seq Scan on zone z_2 (cost=0.00..16.82 rows=682 width=16) (actual time=0.007..0.126 rows=686 loops=1)

159. 3.161 16.646 ↓ 1.0 19,737 1

Hash (cost=2,738.91..2,738.91 rows=19,591 width=24) (actual time=16.646..16.646 rows=19,737 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
160. 13.485 13.485 ↓ 1.0 19,737 1

Seq Scan on masterfinding mf_2 (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.008..13.485 rows=19,737 loops=1)

161. 2.051 4.069 ↓ 1.0 18,503 1

Hash (cost=500.51..500.51 rows=18,480 width=8) (actual time=4.069..4.069 rows=18,503 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
162. 2.018 2.018 ↓ 1.0 18,503 1

Index Only Scan using master_finding_parent_id_key on master_finding_parent mfp_2 (cost=0.29..500.51 rows=18,480 width=8) (actual time=0.021..2.018 rows=18,503 loops=1)

  • Heap Fetches: 563
163. 0.774 1.563 ↑ 1.0 6,558 1

Hash (cost=116.90..116.90 rows=6,690 width=16) (actual time=1.563..1.563 rows=6,558 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 372kB
164. 0.789 0.789 ↓ 1.0 6,738 1

Seq Scan on masterfindingcategory mfg_2 (cost=0.00..116.90 rows=6,690 width=16) (actual time=0.006..0.789 rows=6,738 loops=1)

165. 0.018 0.024 ↓ 36.0 36 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.019..0.024 rows=36 loops=1)

  • Sort Key: event_objects_2.objectid
  • Sort Method: quicksort Memory: 26kB
166. 0.006 0.006 ↓ 36.0 36 1

CTE Scan on event_objects event_objects_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.006 rows=36 loops=1)

167. 3.399 21.690 ↑ 2.9 30,572 1

Hash (cost=2,215.86..2,215.86 rows=88,058 width=8) (actual time=21.689..21.690 rows=30,572 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 2,219kB
168. 10.846 18.291 ↑ 2.9 30,815 1

Hash Join (cost=3.76..2,215.86 rows=88,058 width=8) (actual time=0.067..18.291 rows=30,815 loops=1)

  • Hash Cond: (rpa_1.entity_id = tm.tablesequenceid)
169. 7.414 7.414 ↑ 1.0 88,058 1

Seq Scan on role_permissions_association rpa_1 (cost=0.00..1,976.58 rows=88,058 width=16) (actual time=0.025..7.414 rows=88,058 loops=1)

170. 0.011 0.031 ↓ 1.0 79 1

Hash (cost=2.78..2.78 rows=78 width=8) (actual time=0.031..0.031 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
171. 0.020 0.020 ↓ 1.0 79 1

Seq Scan on tablemetadata tm (cost=0.00..2.78 rows=78 width=8) (actual time=0.007..0.020 rows=79 loops=1)

172. 1.560 7,268.764 ↑ 29.5 60 1

HashAggregate (cost=5,379,060.22..5,379,077.92 rows=1,770 width=8) (actual time=7,268.747..7,268.764 rows=60 loops=1)

  • Group Key: ur_2.user_id
173. 1.266 7,267.204 ↑ 24,369.0 12,819 1

Hash Join (cost=805,843.71..4,598,093.85 rows=312,386,547 width=8) (actual time=7,264.420..7,267.204 rows=12,819 loops=1)

  • Hash Cond: (ur_2.role_id = rpa_2.role_id)
174. 2.342 7,244.617 ↑ 10,899.0 68 1

Merge Left Join (cost=802,527.13..822,422.47 rows=741,132 width=16) (actual time=7,242.988..7,244.617 rows=68 loops=1)

  • Merge Cond: (ucea_2.networksite_id = snetworkchild.parent_networksite_id)
  • Filter: ((hashed SubPlan 10) OR (hashed SubPlan 12))
  • Rows Removed by Filter: 10,903
175. 2.094 11.038 ↑ 2.5 10,636 1

Sort (cost=3,192.39..3,258.31 rows=26,365 width=48) (actual time=10.433..11.038 rows=10,636 loops=1)

  • Sort Key: ucea_2.networksite_id
  • Sort Method: quicksort Memory: 1,110kB
176. 1.721 8.944 ↑ 2.5 10,636 1

Hash Left Join (cost=45.13..1,256.37 rows=26,365 width=48) (actual time=0.499..8.944 rows=10,636 loops=1)

  • Hash Cond: (ucea_2.client_id = ns.clientid)
177. 2.534 6.762 ↑ 2.6 10,173 1

Merge Join (cost=0.56..1,007.97 rows=26,365 width=40) (actual time=0.031..6.762 rows=10,173 loops=1)

  • Merge Cond: (ur_2.user_group_id = ucea_2.usergroup_id)
178. 1.077 1.077 ↑ 1.1 4,834 1

Index Scan using users_roles_user_group_id_index on users_roles ur_2 (cost=0.28..219.35 rows=5,134 width=24) (actual time=0.014..1.077 rows=4,834 loops=1)

179. 1.822 3.151 ↓ 2.0 13,372 1

Materialize (cost=0.28..400.84 rows=6,751 width=32) (actual time=0.011..3.151 rows=13,372 loops=1)

180. 1.329 1.329 ↑ 1.1 6,385 1

Index Only Scan using usergroup_id_client_entities_association_incl on usergroup_client_entities_association ucea_2 (cost=0.28..383.96 rows=6,751 width=32) (actual time=0.008..1.329 rows=6,385 loops=1)

  • Heap Fetches: 2,024
181. 0.083 0.461 ↓ 1.0 625 1

Hash (cost=36.86..36.86 rows=616 width=16) (actual time=0.461..0.461 rows=625 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
182. 0.149 0.378 ↓ 1.0 626 1

Hash Join (cost=21.07..36.86 rows=616 width=16) (actual time=0.182..0.378 rows=626 loops=1)

  • Hash Cond: (s.networksiteid = ns.networksiteid)
183. 0.060 0.060 ↓ 1.0 626 1

Seq Scan on site s (cost=0.00..14.16 rows=616 width=16) (actual time=0.008..0.060 rows=626 loops=1)

184. 0.070 0.169 ↓ 1.0 594 1

Hash (cost=13.81..13.81 rows=581 width=16) (actual time=0.169..0.169 rows=594 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
185. 0.099 0.099 ↓ 1.0 594 1

Seq Scan on networksite ns (cost=0.00..13.81 rows=581 width=16) (actual time=0.006..0.099 rows=594 loops=1)

186. 0.164 1.056 ↑ 40.1 1,568 1

Sort (cost=10,763.70..10,920.86 rows=62,865 width=16) (actual time=0.976..1.056 rows=1,568 loops=1)

  • Sort Key: snetworkchild.parent_networksite_id
  • Sort Method: quicksort Memory: 54kB
187. 0.054 0.892 ↑ 100.4 626 1

Subquery Scan on snetworkchild (cost=2,735.64..5,753.37 rows=62,865 width=16) (actual time=0.186..0.892 rows=626 loops=1)

188. 0.144 0.838 ↑ 100.4 626 1

Hash Join (cost=2,735.64..5,124.72 rows=62,865 width=24) (actual time=0.185..0.838 rows=626 loops=1)

  • Hash Cond: (siteparents.networksiteid = s_1.networksiteid)
189.          

CTE siteparents

190. 0.075 0.403 ↑ 98.8 594 1

Recursive Union (cost=0.00..2,713.78 rows=58,681 width=24) (actual time=0.008..0.403 rows=594 loops=1)

191. 0.092 0.092 ↓ 1.0 594 1

Seq Scan on networksite ns_1 (cost=0.00..13.81 rows=581 width=24) (actual time=0.007..0.092 rows=594 loops=1)

192. 0.036 0.236 ↓ 0.0 0 1

Hash Join (cost=21.07..152.63 rows=5,810 width=24) (actual time=0.236..0.236 rows=0 loops=1)

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
193. 0.049 0.049 ↑ 9.8 594 1

WorkTable Scan on siteparents siteparents_1 (cost=0.00..116.20 rows=5,810 width=16) (actual time=0.001..0.049 rows=594 loops=1)

194. 0.073 0.151 ↓ 1.0 594 1

Hash (cost=13.81..13.81 rows=581 width=24) (actual time=0.151..0.151 rows=594 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
195. 0.078 0.078 ↓ 1.0 594 1

Seq Scan on networksite ns2 (cost=0.00..13.81 rows=581 width=24) (actual time=0.004..0.078 rows=594 loops=1)

196. 0.525 0.525 ↑ 98.8 594 1

CTE Scan on siteparents (cost=0.00..1,173.62 rows=58,681 width=16) (actual time=0.009..0.525 rows=594 loops=1)

197. 0.074 0.169 ↓ 1.0 626 1

Hash (cost=14.16..14.16 rows=616 width=16) (actual time=0.169..0.169 rows=626 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
198. 0.095 0.095 ↓ 1.0 626 1

Seq Scan on site s_1 (cost=0.00..14.16 rows=616 width=16) (actual time=0.005..0.095 rows=626 loops=1)

199.          

SubPlan (for Merge Left Join)

200. 44.706 3,623.720 ↓ 9.9 69 1

Merge Semi Join (cost=25,953.65..394,285.50 rows=7 width=8) (actual time=3,623.673..3,623.720 rows=69 loops=1)

  • Merge Cond: (i_3.issueid = event_objects_3.objectid)
201. 203.914 3,578.992 ↑ 1.1 788,528 1

Merge Join (cost=25,953.62..392,073.23 rows=884,863 width=24) (actual time=245.722..3,578.992 rows=788,528 loops=1)

  • Merge Cond: (f_3.issueid = i_3.issueid)
202. 2,155.399 3,084.724 ↑ 1.1 782,318 1

Gather Merge (cost=1,000.45..397,567.96 rows=884,921 width=8) (actual time=18.930..3,084.724 rows=782,318 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
203. 64.848 929.325 ↑ 1.4 260,845 3 / 3

Nested Loop (cost=0.42..294,426.10 rows=368,717 width=8) (actual time=0.063..929.325 rows=260,845 loops=3)

204. 106.960 106.960 ↑ 1.3 252,506 3 / 3

Parallel Index Scan using issueid_id_final_remediated_state on finding f_3 (cost=0.42..211,020.14 rows=317,306 width=24) (actual time=0.027..106.960 rows=252,506 loops=3)

205. 757.517 757.517 ↑ 1.0 1 757,517 / 3

Index Scan using project_finding_finding_id_hash on project_finding pf_3 (cost=0.00..0.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=757,517)

  • Index Cond: (finding_id = f_3.findingid)
  • Rows Removed by Index Recheck: 0
206. 93.273 290.354 ↓ 7.3 889,251 1

Sort (cost=24,949.96..25,253.38 rows=121,371 width=16) (actual time=226.769..290.354 rows=889,251 loops=1)

  • Sort Key: i_3.issueid
  • Sort Method: quicksort Memory: 8,826kB
207. 16.880 197.081 ↓ 1.0 122,745 1

Hash Left Join (cost=13,472.76..14,700.74 rows=121,371 width=16) (actual time=163.105..197.081 rows=122,745 loops=1)

  • Hash Cond: (mf_3.masterfindingid = mfg_3.masterfindingid)
208. 17.085 178.646 ↓ 1.0 122,712 1

Hash Right Join (cost=13,272.23..13,487.08 rows=121,371 width=24) (actual time=161.529..178.646 rows=122,712 loops=1)

  • Hash Cond: (cpmv_3.masterfindingid = mf_3.masterfindingid)
209. 0.195 0.195 ↑ 1.1 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_3 (cost=0.00..40.35 rows=2,435 width=8) (actual time=0.012..0.195 rows=2,131 loops=1)

210. 17.094 161.366 ↓ 1.0 122,073 1

Hash (cost=11,755.10..11,755.10 rows=121,371 width=24) (actual time=161.365..161.366 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,700kB
211. 23.095 144.272 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,755.10 rows=121,371 width=24) (actual time=50.176..144.272 rows=122,073 loops=1)

  • Hash Cond: (mf_3.master_finding_parent_id = mfp_3.id)
212. 25.405 117.061 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,704.91 rows=121,371 width=32) (actual time=46.023..117.061 rows=122,173 loops=1)

  • Hash Cond: (i_3.masterfindingid = mf_3.masterfindingid)
213. 35.271 74.912 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,402.45 rows=121,371 width=24) (actual time=29.242..74.912 rows=122,173 loops=1)

  • Hash Cond: (i_3.assetid = a_4.assetid)
214. 10.469 10.469 ↑ 1.0 120,102 1

Seq Scan on issue i_3 (cost=0.00..3,265.79 rows=121,379 width=24) (actual time=0.007..10.469 rows=120,102 loops=1)

215. 5.988 29.172 ↓ 1.0 43,047 1

Hash (cost=1,931.39..1,931.39 rows=42,911 width=16) (actual time=29.172..29.172 rows=43,047 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,530kB
216. 7.349 23.184 ↓ 1.0 43,047 1

Hash Join (cost=25.92..1,931.39 rows=42,911 width=16) (actual time=0.264..23.184 rows=43,047 loops=1)

  • Hash Cond: (a_4.zoneid = z_3.zoneid)
217. 4.622 15.614 ↓ 1.0 43,053 1

Merge Left Join (cost=0.57..1,792.69 rows=42,914 width=16) (actual time=0.037..15.614 rows=43,053 loops=1)

  • Merge Cond: (a_4.assetid = aa_3.asset_id)
218. 4.908 10.921 ↓ 1.0 42,937 1

Merge Left Join (cost=0.43..1,663.54 rows=42,914 width=16) (actual time=0.024..10.921 rows=42,937 loops=1)

  • Merge Cond: (a_4.assetid = apl_3.asset_id)
219. 5.997 5.997 ↓ 1.0 42,927 1

Index Only Scan using asset_assetid_zoneid_index on asset a_4 (cost=0.29..1,543.38 rows=42,914 width=16) (actual time=0.014..5.997 rows=42,927 loops=1)

  • Heap Fetches: 4,876
220. 0.016 0.016 ↑ 1.0 27 1

Index Only Scan using asset_physical_location_mav_unique on asset_physical_location_mav apl_3 (cost=0.14..12.54 rows=27 width=8) (actual time=0.007..0.016 rows=27 loops=1)

  • Heap Fetches: 27
221. 0.071 0.071 ↓ 1.1 317 1

Index Only Scan using fki_asset_id_fk on application_asset aa_3 (cost=0.15..18.32 rows=283 width=8) (actual time=0.011..0.071 rows=317 loops=1)

  • Heap Fetches: 158
222. 0.092 0.221 ↓ 1.0 686 1

Hash (cost=16.82..16.82 rows=682 width=24) (actual time=0.221..0.221 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
223. 0.129 0.129 ↓ 1.0 686 1

Seq Scan on zone z_3 (cost=0.00..16.82 rows=682 width=24) (actual time=0.006..0.129 rows=686 loops=1)

224. 3.334 16.744 ↓ 1.0 19,737 1

Hash (cost=2,738.91..2,738.91 rows=19,591 width=24) (actual time=16.744..16.744 rows=19,737 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
225. 13.410 13.410 ↓ 1.0 19,737 1

Seq Scan on masterfinding mf_3 (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.008..13.410 rows=19,737 loops=1)

226. 2.100 4.116 ↓ 1.0 18,503 1

Hash (cost=500.51..500.51 rows=18,480 width=8) (actual time=4.116..4.116 rows=18,503 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
227. 2.016 2.016 ↓ 1.0 18,503 1

Index Only Scan using master_finding_parent_id_key on master_finding_parent mfp_3 (cost=0.29..500.51 rows=18,480 width=8) (actual time=0.020..2.016 rows=18,503 loops=1)

  • Heap Fetches: 563
228. 0.764 1.555 ↑ 1.0 6,558 1

Hash (cost=116.90..116.90 rows=6,690 width=16) (actual time=1.555..1.555 rows=6,558 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 372kB
229. 0.791 0.791 ↓ 1.0 6,738 1

Seq Scan on masterfindingcategory mfg_3 (cost=0.00..116.90 rows=6,690 width=16) (actual time=0.007..0.791 rows=6,738 loops=1)

230. 0.015 0.022 ↓ 36.0 36 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.018..0.022 rows=36 loops=1)

  • Sort Key: event_objects_3.objectid
  • Sort Method: quicksort Memory: 26kB
231. 0.007 0.007 ↓ 36.0 36 1

CTE Scan on event_objects event_objects_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.007 rows=36 loops=1)

232. 44.933 3,606.461 ↓ 9.9 69 1

Merge Semi Join (cost=25,953.65..394,285.50 rows=7 width=8) (actual time=3,606.413..3,606.461 rows=69 loops=1)

  • Merge Cond: (i_4.issueid = event_objects_4.objectid)
233. 188.716 3,561.505 ↑ 1.1 788,528 1

Merge Join (cost=25,953.62..392,073.23 rows=884,863 width=24) (actual time=273.918..3,561.505 rows=788,528 loops=1)

  • Merge Cond: (f_4.issueid = i_4.issueid)
234. 2,141.753 3,069.374 ↑ 1.1 782,318 1

Gather Merge (cost=1,000.45..397,567.96 rows=884,921 width=8) (actual time=35.196..3,069.374 rows=782,318 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
235. 55.738 927.621 ↑ 1.4 260,845 3 / 3

Nested Loop (cost=0.42..294,426.10 rows=368,717 width=8) (actual time=0.053..927.621 rows=260,845 loops=3)

236. 114.366 114.366 ↑ 1.3 252,506 3 / 3

Parallel Index Scan using issueid_id_final_remediated_state on finding f_4 (cost=0.42..211,020.14 rows=317,306 width=24) (actual time=0.020..114.366 rows=252,506 loops=3)

237. 757.517 757.517 ↑ 1.0 1 757,517 / 3

Index Scan using project_finding_finding_id_hash on project_finding pf_4 (cost=0.00..0.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=757,517)

  • Index Cond: (finding_id = f_4.findingid)
  • Rows Removed by Index Recheck: 0
238. 98.405 303.415 ↓ 7.3 889,251 1

Sort (cost=24,949.96..25,253.38 rows=121,371 width=16) (actual time=238.700..303.415 rows=889,251 loops=1)

  • Sort Key: i_4.issueid
  • Sort Method: quicksort Memory: 8,826kB
239. 16.868 205.010 ↓ 1.0 122,745 1

Hash Left Join (cost=13,472.76..14,700.74 rows=121,371 width=16) (actual time=171.849..205.010 rows=122,745 loops=1)

  • Hash Cond: (mf_4.masterfindingid = mfg_4.masterfindingid)
240. 16.812 186.319 ↓ 1.0 122,712 1

Hash Right Join (cost=13,272.23..13,487.08 rows=121,371 width=24) (actual time=169.966..186.319 rows=122,712 loops=1)

  • Hash Cond: (cpmv_4.masterfindingid = mf_4.masterfindingid)
241. 0.201 0.201 ↑ 1.1 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_4 (cost=0.00..40.35 rows=2,435 width=8) (actual time=0.011..0.201 rows=2,131 loops=1)

242. 22.336 169.306 ↓ 1.0 122,073 1

Hash (cost=11,755.10..11,755.10 rows=121,371 width=24) (actual time=169.306..169.306 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,661kB
243. 21.061 146.970 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,755.10 rows=121,371 width=24) (actual time=53.230..146.970 rows=122,073 loops=1)

  • Hash Cond: (mf_4.master_finding_parent_id = mfp_4.id)
244. 26.217 121.877 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,704.91 rows=121,371 width=32) (actual time=49.163..121.877 rows=122,173 loops=1)

  • Hash Cond: (i_4.masterfindingid = mf_4.masterfindingid)
245. 35.586 78.321 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,402.45 rows=121,371 width=24) (actual time=31.662..78.321 rows=122,173 loops=1)

  • Hash Cond: (i_4.assetid = a_5.assetid)
246. 11.143 11.143 ↑ 1.0 120,102 1

Seq Scan on issue i_4 (cost=0.00..3,265.79 rows=121,379 width=24) (actual time=0.007..11.143 rows=120,102 loops=1)

247. 7.968 31.592 ↓ 1.0 43,047 1

Hash (cost=1,931.39..1,931.39 rows=42,911 width=16) (actual time=31.592..31.592 rows=43,047 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,512kB
248. 7.257 23.624 ↓ 1.0 43,047 1

Hash Join (cost=25.92..1,931.39 rows=42,911 width=16) (actual time=0.264..23.624 rows=43,047 loops=1)

  • Hash Cond: (a_5.zoneid = z_4.zoneid)
249. 4.774 16.147 ↓ 1.0 43,053 1

Merge Left Join (cost=0.57..1,792.69 rows=42,914 width=16) (actual time=0.038..16.147 rows=43,053 loops=1)

  • Merge Cond: (a_5.assetid = aa_4.asset_id)
250. 5.069 11.296 ↓ 1.0 42,937 1

Merge Left Join (cost=0.43..1,663.54 rows=42,914 width=16) (actual time=0.024..11.296 rows=42,937 loops=1)

  • Merge Cond: (a_5.assetid = apl_4.asset_id)
251. 6.210 6.210 ↓ 1.0 42,927 1

Index Only Scan using asset_assetid_zoneid_index on asset a_5 (cost=0.29..1,543.38 rows=42,914 width=16) (actual time=0.014..6.210 rows=42,927 loops=1)

  • Heap Fetches: 4,876
252. 0.017 0.017 ↑ 1.0 27 1

Index Only Scan using asset_physical_location_mav_unique on asset_physical_location_mav apl_4 (cost=0.14..12.54 rows=27 width=8) (actual time=0.007..0.017 rows=27 loops=1)

  • Heap Fetches: 27
253. 0.077 0.077 ↓ 1.1 317 1

Index Only Scan using fki_asset_id_fk on application_asset aa_4 (cost=0.15..18.32 rows=283 width=8) (actual time=0.012..0.077 rows=317 loops=1)

  • Heap Fetches: 158
254. 0.082 0.220 ↓ 1.0 686 1

Hash (cost=16.82..16.82 rows=682 width=16) (actual time=0.220..0.220 rows=686 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
255. 0.138 0.138 ↓ 1.0 686 1

Seq Scan on zone z_4 (cost=0.00..16.82 rows=682 width=16) (actual time=0.007..0.138 rows=686 loops=1)

256. 4.039 17.339 ↓ 1.0 19,737 1

Hash (cost=2,738.91..2,738.91 rows=19,591 width=24) (actual time=17.339..17.339 rows=19,737 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
257. 13.300 13.300 ↓ 1.0 19,737 1

Seq Scan on masterfinding mf_4 (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.009..13.300 rows=19,737 loops=1)

258. 2.045 4.032 ↓ 1.0 18,503 1

Hash (cost=500.51..500.51 rows=18,480 width=8) (actual time=4.032..4.032 rows=18,503 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
259. 1.987 1.987 ↓ 1.0 18,503 1

Index Only Scan using master_finding_parent_id_key on master_finding_parent mfp_4 (cost=0.29..500.51 rows=18,480 width=8) (actual time=0.020..1.987 rows=18,503 loops=1)

  • Heap Fetches: 563
260. 1.025 1.823 ↑ 1.0 6,558 1

Hash (cost=116.90..116.90 rows=6,690 width=16) (actual time=1.823..1.823 rows=6,558 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 372kB
261. 0.798 0.798 ↓ 1.0 6,738 1

Seq Scan on masterfindingcategory mfg_4 (cost=0.00..116.90 rows=6,690 width=16) (actual time=0.006..0.798 rows=6,738 loops=1)

262. 0.017 0.023 ↓ 36.0 36 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.018..0.023 rows=36 loops=1)

  • Sort Key: event_objects_4.objectid
  • Sort Method: quicksort Memory: 26kB
263. 0.006 0.006 ↓ 36.0 36 1

CTE Scan on event_objects event_objects_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.006 rows=36 loops=1)

264. 3.413 21.321 ↑ 2.9 30,572 1

Hash (cost=2,215.86..2,215.86 rows=88,058 width=8) (actual time=21.321..21.321 rows=30,572 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 2,219kB
265. 10.628 17.908 ↑ 2.9 30,815 1

Hash Join (cost=3.76..2,215.86 rows=88,058 width=8) (actual time=0.055..17.908 rows=30,815 loops=1)

  • Hash Cond: (rpa_2.entity_id = t_1.tablesequenceid)
266. 7.250 7.250 ↑ 1.0 88,058 1

Seq Scan on role_permissions_association rpa_2 (cost=0.00..1,976.58 rows=88,058 width=16) (actual time=0.013..7.250 rows=88,058 loops=1)

267. 0.011 0.030 ↓ 1.0 79 1

Hash (cost=2.78..2.78 rows=78 width=8) (actual time=0.029..0.030 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
268. 0.019 0.019 ↓ 1.0 79 1

Seq Scan on tablemetadata t_1 (cost=0.00..2.78 rows=78 width=8) (actual time=0.007..0.019 rows=79 loops=1)

Planning time : 123.334 ms
Execution time : 96,576.965 ms