explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dUxo

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 22,064.387 ↑ 30.5 174 1

Append (cost=1,592,404.50..6,848,086.69 rows=5,310 width=8) (actual time=9,553.403..22,064.387 rows=174 loops=1)

2.          

CTE event_objects

3. 0.476 0.476 ↓ 36.0 36 1

Index Scan using global_event_global_event_time_index on global_event ge (cost=0.44..10.68 rows=1 width=8) (actual time=0.091..0.476 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,310
4. 0.182 9,553.418 ↑ 31.1 57 1

HashAggregate (cost=1,592,393.82..1,592,411.52 rows=1,770 width=8) (actual time=9,553.402..9,553.418 rows=57 loops=1)

  • Group Key: ur.user_id
5. 2,349.556 9,553.236 ↑ 1,893.3 1,197 1

Hash Join (cost=420,964.48..1,586,728.20 rows=2,266,248 width=8) (actual time=8,109.506..9,553.236 rows=1,197 loops=1)

  • Hash Cond: (COALESCE(ucea.project_id, pppgpggroupchild.project_id, p.projectid) = pf.project_id)
6. 3,164.297 4,942.693 ↑ 2.1 30,908,575 1

Hash Join (cost=91,303.07..1,083,496.44 rows=64,749,953 width=32) (actual time=1,125.510..4,942.693 rows=30,908,575 loops=1)

  • Hash Cond: (ur.role_id = rpa.role_id)
7. 385.739 1,777.049 ↑ 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,124.157..1,777.049 rows=2,588,920 loops=1)

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

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

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

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

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

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

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

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

  • Merge Cond: (ucea.client_id = pg.clientid)
12. 1.817 1.817 ↑ 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.008..1.817 rows=6,385 loops=1)

13. 43.602 45.278 ↓ 248.7 849,184 1

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

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

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

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

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

16. 0.102 0.234 ↓ 1.0 806 1

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

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

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

18. 0.667 4.824 ↑ 114.9 3,428 1

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

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

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

20. 0.812 3.877 ↑ 114.9 3,428 1

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

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

CTE pgparents

22. 0.131 0.638 ↑ 81.9 955 1

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

23. 0.147 0.147 ↓ 1.0 806 1

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

24. 0.082 0.360 ↑ 154.8 50 3

Hash Join (cost=31.42..206.64 rows=7,740 width=40) (actual time=0.080..0.120 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.107 0.227 ↓ 1.0 806 1

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

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

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

28. 0.934 1.795 ↑ 1.0 3,368 1

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

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

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

30. 0.414 1.270 ↑ 21.0 3,717 1

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

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

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

32. 0.609 1.309 ↑ 1.1 4,834 1

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

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

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

34. 1.082 5.325 ↑ 49.2 7,996 1

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

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

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

36. 0.875 3.956 ↑ 114.9 3,428 1

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

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

CTE pgparents

38. 0.128 0.669 ↑ 81.9 955 1

Recursive Union (cost=0.00..3,651.66 rows=78,174 width=40) (actual time=0.010..0.669 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.090 0.381 ↑ 154.8 50 3

Hash Join (cost=31.42..206.64 rows=7,740 width=40) (actual time=0.086..0.127 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.113 0.240 ↓ 1.0 806 1

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

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

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

44. 0.879 1.769 ↑ 1.0 3,368 1

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

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

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

46. 0.417 1.312 ↑ 21.0 3,717 1

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

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

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

48. 0.272 1.347 ↓ 2.0 2,304 1

Hash (cost=1,186.49..1,186.49 rows=1,129 width=8) (actual time=1.347..1.347 rows=2,304 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 122kB
49. 0.254 1.075 ↓ 2.1 2,322 1

Nested Loop (cost=32.61..1,186.49 rows=1,129 width=8) (actual time=0.185..1.075 rows=2,322 loops=1)

50. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on tablemetadata t (cost=0.00..2.98 rows=1 width=8) (actual time=0.013..0.019 rows=1 loops=1)

  • Filter: ((modelname)::text = 'finding'::text)
  • Rows Removed by Filter: 78
51. 0.669 0.802 ↓ 2.2 2,322 1

Bitmap Heap Scan on role_permissions_association rpa (cost=32.61..1,172.95 rows=1,057 width=16) (actual time=0.167..0.802 rows=2,322 loops=1)

  • Recheck Cond: (entity_id = t.tablesequenceid)
  • Heap Blocks: exact=309
52. 0.133 0.133 ↓ 2.2 2,322 1

Bitmap Index Scan on role_permissions_association_entity_id_index (cost=0.00..32.34 rows=1,057 width=0) (actual time=0.132..0.133 rows=2,322 loops=1)

  • Index Cond: (entity_id = t.tablesequenceid)
53. 0.004 2,260.987 ↑ 2.3 3 1

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

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

Unique (cost=329,661.22..329,661.25 rows=7 width=8) (actual time=2,260.973..2,260.983 rows=3 loops=1)

55. 0.021 2,260.975 ↓ 9.9 69 1

Sort (cost=329,661.22..329,661.23 rows=7 width=8) (actual time=2,260.971..2,260.975 rows=69 loops=1)

  • Sort Key: pf.project_id
  • Sort Method: quicksort Memory: 28kB
56. 54.640 2,260.954 ↓ 9.9 69 1

Hash Semi Join (cost=234,963.17..329,661.12 rows=7 width=8) (actual time=2,260.331..2,260.954 rows=69 loops=1)

  • Hash Cond: (i.issueid = event_objects.objectid)
57. 1,144.939 2,205.824 ↑ 1.1 788,531 1

Hash Join (cost=234,963.14..327,338.24 rows=884,863 width=24) (actual time=821.826..2,205.824 rows=788,531 loops=1)

  • Hash Cond: (pf.finding_id = f.findingid)
58. 291.266 291.266 ↑ 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.006..291.266 rows=4,150,639 loops=1)

59. 165.585 769.619 ↓ 1.0 763,487 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 49,946kB
60. 197.549 604.034 ↓ 1.0 763,487 1

Merge Join (cost=24,933.82..225,444.59 rows=761,484 width=24) (actual time=216.149..604.034 rows=763,487 loops=1)

  • Merge Cond: (i.issueid = f.issueid)
61. 37.508 227.434 ↓ 1.0 122,745 1

Sort (cost=24,932.54..25,236.11 rows=121,430 width=8) (actual time=216.101..227.434 rows=122,745 loops=1)

  • Sort Key: i.issueid
  • Sort Method: quicksort Memory: 8,826kB
62. 16.295 189.926 ↓ 1.0 122,745 1

Hash Left Join (cost=13,476.21..14,677.92 rows=121,430 width=8) (actual time=157.799..189.926 rows=122,745 loops=1)

  • Hash Cond: (mf.masterfindingid = mfg.masterfindingid)
63. 15.811 172.089 ↓ 1.0 122,712 1

Hash Right Join (cost=13,275.68..13,463.76 rows=121,430 width=16) (actual time=156.240..172.089 rows=122,712 loops=1)

  • Hash Cond: (cpmv.masterfindingid = mf.masterfindingid)
64. 0.186 0.186 ↑ 1.0 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.015..0.186 rows=2,131 loops=1)

65. 15.367 156.092 ↓ 1.0 122,073 1

Hash (cost=11,757.81..11,757.81 rows=121,430 width=16) (actual time=156.092..156.092 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,747kB
66. 22.956 140.725 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,757.81 rows=121,430 width=16) (actual time=48.998..140.725 rows=122,073 loops=1)

  • Hash Cond: (mf.master_finding_parent_id = mfp.id)
67. 24.604 113.721 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,707.46 rows=121,430 width=24) (actual time=44.916..113.721 rows=122,173 loops=1)

  • Hash Cond: (i.masterfindingid = mf.masterfindingid)
68. 33.706 72.564 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,404.85 rows=121,430 width=16) (actual time=28.327..72.564 rows=122,173 loops=1)

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

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

70. 5.432 28.264 ↓ 1.0 43,047 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,194kB
71. 6.857 22.832 ↓ 1.0 43,047 1

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

  • Hash Cond: (a.zoneid = z.zoneid)
72. 4.680 15.764 ↓ 1.0 43,053 1

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

  • Merge Cond: (a.assetid = aa.asset_id)
73. 4.989 11.008 ↓ 1.0 42,937 1

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

  • Merge Cond: (a.assetid = apl.asset_id)
74. 6.002 6.002 ↓ 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.014..6.002 rows=42,927 loops=1)

  • Heap Fetches: 4,876
75. 0.017 0.017 ↑ 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.007..0.017 rows=27 loops=1)

  • Heap Fetches: 27
76. 0.076 0.076 ↓ 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.014..0.076 rows=317 loops=1)

  • Heap Fetches: 158
77. 0.082 0.211 ↓ 1.0 686 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
78. 0.129 0.129 ↓ 1.0 686 1

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

79. 3.017 16.553 ↓ 1.0 19,737 1

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

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

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

81. 2.036 4.048 ↓ 1.0 18,503 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
82. 2.012 2.012 ↓ 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.017..2.012 rows=18,503 loops=1)

  • Heap Fetches: 563
83. 0.772 1.542 ↑ 1.0 6,558 1

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

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

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

85. 179.051 179.051 ↓ 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.023..179.051 rows=763,562 loops=1)

86. 0.005 0.490 ↓ 36.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
87. 0.485 0.485 ↓ 36.0 36 1

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

88. 1.213 6,057.659 ↑ 31.1 57 1

HashAggregate (cost=728,488.03..728,505.73 rows=1,770 width=8) (actual time=6,057.645..6,057.659 rows=57 loops=1)

  • Group Key: ur_1.user_id
89. 0.938 6,056.446 ↑ 10.9 9,837 1

Hash Join (cost=726,097.35..728,220.89 rows=106,854 width=8) (actual time=6,048.200..6,056.446 rows=9,837 loops=1)

  • Hash Cond: (ur_1.role_id = rpa_1.role_id)
90. 2.983 6,054.057 ↑ 30.1 656 1

Hash Left Join (cost=724,896.74..725,679.80 rows=19,774 width=16) (actual time=6,046.733..6,054.057 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
91. 2.447 7.746 ↑ 2.4 10,830 1

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

  • Merge Cond: (ur_1.user_group_id = ucea_1.usergroup_id)
92. 1.196 1.196 ↑ 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.015..1.196 rows=4,834 loops=1)

93. 1.783 4.103 ↓ 2.1 14,083 1

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

  • Sort Key: ucea_1.usergroup_id
  • Sort Method: quicksort Memory: 498kB
94. 1.030 2.320 ↑ 1.0 6,513 1

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

  • Merge Cond: (ucea_1.application_group_id = agroupchild.applicationgroupid)
95. 1.153 1.153 ↑ 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.153 rows=6,385 loops=1)

96. 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
97. 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.007..0.074 rows=332 loops=1)

98. 0.040 0.329 ↓ 1.0 332 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
99. 0.085 0.289 ↓ 1.0 332 1

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

  • Hash Cond: (a_1.applicationgroupid = ag.applicationgroupid)
100. 0.034 0.034 ↓ 1.0 332 1

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

101. 0.070 0.170 ↓ 1.0 587 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
102. 0.100 0.100 ↓ 1.0 587 1

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

103.          

SubPlan (for Hash Left Join)

104. 45.300 3,652.385 ↓ 9.9 69 1

Merge Semi Join (cost=25,938.17..394,270.01 rows=7 width=8) (actual time=3,652.092..3,652.385 rows=69 loops=1)

  • Merge Cond: (i_1.issueid = event_objects_1.objectid)
105. 209.070 3,607.055 ↑ 1.1 788,528 1

Merge Join (cost=25,938.14..392,057.75 rows=884,863 width=24) (actual time=246.678..3,607.055 rows=788,528 loops=1)

  • Merge Cond: (f_1.issueid = i_1.issueid)
106. 2,164.031 3,107.716 ↑ 1.1 782,318 1

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

  • Workers Planned: 2
  • Workers Launched: 2
107. 74.299 943.685 ↑ 1.4 260,844 3 / 3

Nested Loop (cost=0.42..294,426.10 rows=368,717 width=8) (actual time=0.062..943.685 rows=260,844 loops=3)

108. 111.872 111.872 ↑ 1.3 252,505 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.026..111.872 rows=252,505 loops=3)

109. 757.514 757.514 ↑ 1.0 1 757,514 / 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,514)

  • Index Cond: (finding_id = f_1.findingid)
  • Rows Removed by Index Recheck: 0
110. 93.144 290.269 ↓ 7.3 889,251 1

Sort (cost=24,934.47..25,238.05 rows=121,430 width=16) (actual time=224.370..290.269 rows=889,251 loops=1)

  • Sort Key: i_1.issueid
  • Sort Method: quicksort Memory: 8,826kB
111. 16.875 197.125 ↓ 1.0 122,745 1

Hash Left Join (cost=13,478.14..14,679.85 rows=121,430 width=16) (actual time=163.562..197.125 rows=122,745 loops=1)

  • Hash Cond: (mf_1.masterfindingid = mfg_1.masterfindingid)
112. 16.649 178.595 ↓ 1.0 122,712 1

Hash Right Join (cost=13,277.61..13,465.69 rows=121,430 width=24) (actual time=161.890..178.595 rows=122,712 loops=1)

  • Hash Cond: (cpmv_1.masterfindingid = mf_1.masterfindingid)
113. 0.188 0.188 ↑ 1.0 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_1 (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.015..0.188 rows=2,131 loops=1)

114. 16.146 161.758 ↓ 1.0 122,073 1

Hash (cost=11,759.74..11,759.74 rows=121,430 width=24) (actual time=161.758..161.758 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,861kB
115. 22.743 145.612 ↓ 1.0 122,073 1

Hash Join (cost=6,185.01..11,759.74 rows=121,430 width=24) (actual time=52.094..145.612 rows=122,073 loops=1)

  • Hash Cond: (mf_1.master_finding_parent_id = mfp_1.id)
116. 24.757 118.777 ↓ 1.0 122,173 1

Hash Join (cost=5,453.50..10,709.40 rows=121,430 width=32) (actual time=47.971..118.777 rows=122,173 loops=1)

  • Hash Cond: (i_1.masterfindingid = mf_1.masterfindingid)
117. 35.695 76.461 ↓ 1.0 122,173 1

Hash Join (cost=2,469.71..7,406.78 rows=121,430 width=24) (actual time=30.378..76.461 rows=122,173 loops=1)

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

Seq Scan on issue i_1 (cost=0.00..3,267.38 rows=121,438 width=24) (actual time=0.008..10.466 rows=120,102 loops=1)

119. 5.846 30.300 ↓ 1.0 43,047 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,196kB
120. 7.217 24.454 ↓ 1.0 43,047 1

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

  • Hash Cond: (a_2.zoneid = z_1.zoneid)
121. 5.492 17.027 ↓ 1.0 43,053 1

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

  • Merge Cond: (a_2.assetid = aa_1.asset_id)
122. 5.462 11.462 ↓ 1.0 42,937 1

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

  • Merge Cond: (a_2.assetid = apl_1.asset_id)
123. 5.985 5.985 ↓ 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.014..5.985 rows=42,927 loops=1)

  • Heap Fetches: 4,876
124. 0.015 0.015 ↑ 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.005..0.015 rows=27 loops=1)

  • Heap Fetches: 27
125. 0.073 0.073 ↓ 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.007..0.073 rows=317 loops=1)

126. 0.083 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
127. 0.127 0.127 ↓ 1.0 686 1

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

128. 3.149 17.559 ↓ 1.0 19,737 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
129. 14.410 14.410 ↓ 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..14.410 rows=19,737 loops=1)

130. 2.016 4.092 ↓ 1.0 18,503 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
131. 2.076 2.076 ↓ 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.076 rows=18,503 loops=1)

  • Heap Fetches: 563
132. 0.778 1.655 ↑ 1.0 6,558 1

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

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

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

134. 0.018 0.030 ↓ 36.0 36 1

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

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

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

136. 0.006 2,390.614 ↑ 3.5 2 1

Unique (cost=329,661.22..329,661.25 rows=7 width=8) (actual time=2,390.605..2,390.614 rows=2 loops=1)

137. 0.024 2,390.608 ↓ 9.9 69 1

Sort (cost=329,661.22..329,661.23 rows=7 width=8) (actual time=2,390.604..2,390.608 rows=69 loops=1)

  • Sort Key: z_2.clientid
  • Sort Method: quicksort Memory: 28kB
138. 54.779 2,390.584 ↓ 9.9 69 1

Hash Semi Join (cost=234,963.17..329,661.12 rows=7 width=8) (actual time=2,389.944..2,390.584 rows=69 loops=1)

  • Hash Cond: (i_2.issueid = event_objects_2.objectid)
139. 1,249.346 2,335.795 ↑ 1.1 788,531 1

Hash Join (cost=234,963.14..327,338.24 rows=884,863 width=24) (actual time=849.314..2,335.795 rows=788,531 loops=1)

  • Hash Cond: (pf_2.finding_id = f_2.findingid)
140. 293.709 293.709 ↑ 1.0 4,150,639 1

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

141. 178.566 792.740 ↓ 1.0 763,487 1

Hash (cost=225,444.59..225,444.59 rows=761,484 width=32) (actual time=792.740..792.740 rows=763,487 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,896kB
142. 197.747 614.174 ↓ 1.0 763,487 1

Merge Join (cost=24,933.82..225,444.59 rows=761,484 width=32) (actual time=225.173..614.174 rows=763,487 loops=1)

  • Merge Cond: (i_2.issueid = f_2.issueid)
143. 39.914 237.483 ↓ 1.0 122,745 1

Sort (cost=24,932.54..25,236.11 rows=121,430 width=16) (actual time=225.125..237.483 rows=122,745 loops=1)

  • Sort Key: i_2.issueid
  • Sort Method: quicksort Memory: 8,826kB
144. 16.903 197.569 ↓ 1.0 122,745 1

Hash Left Join (cost=13,476.21..14,677.92 rows=121,430 width=16) (actual time=163.286..197.569 rows=122,745 loops=1)

  • Hash Cond: (mf_2.masterfindingid = mfg_2.masterfindingid)
145. 17.367 179.082 ↓ 1.0 122,712 1

Hash Right Join (cost=13,275.68..13,463.76 rows=121,430 width=24) (actual time=161.684..179.082 rows=122,712 loops=1)

  • Hash Cond: (cpmv_2.masterfindingid = mf_2.masterfindingid)
146. 0.182 0.182 ↑ 1.0 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_2 (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.014..0.182 rows=2,131 loops=1)

147. 16.793 161.533 ↓ 1.0 122,073 1

Hash (cost=11,757.81..11,757.81 rows=121,430 width=24) (actual time=161.533..161.533 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,661kB
148. 23.108 144.740 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,757.81 rows=121,430 width=24) (actual time=51.091..144.740 rows=122,073 loops=1)

  • Hash Cond: (mf_2.master_finding_parent_id = mfp_2.id)
149. 24.941 117.506 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,707.46 rows=121,430 width=32) (actual time=46.929..117.506 rows=122,173 loops=1)

  • Hash Cond: (i_2.masterfindingid = mf_2.masterfindingid)
150. 35.321 75.660 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,404.85 rows=121,430 width=24) (actual time=29.986..75.660 rows=122,173 loops=1)

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

Seq Scan on issue i_2 (cost=0.00..3,267.38 rows=121,438 width=24) (actual time=0.007..10.422 rows=120,102 loops=1)

152. 6.272 29.917 ↓ 1.0 43,047 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,512kB
153. 7.510 23.645 ↓ 1.0 43,047 1

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

  • Hash Cond: (a_3.zoneid = z_2.zoneid)
154. 4.602 15.926 ↓ 1.0 43,053 1

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

  • Merge Cond: (a_3.assetid = aa_2.asset_id)
155. 5.179 11.247 ↓ 1.0 42,937 1

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

  • Merge Cond: (a_3.assetid = apl_2.asset_id)
156. 6.051 6.051 ↓ 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..6.051 rows=42,927 loops=1)

  • Heap Fetches: 4,876
157. 0.017 0.017 ↑ 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.007..0.017 rows=27 loops=1)

  • Heap Fetches: 27
158. 0.077 0.077 ↓ 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.011..0.077 rows=317 loops=1)

  • Heap Fetches: 158
159. 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
160. 0.128 0.128 ↓ 1.0 686 1

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

161. 3.172 16.905 ↓ 1.0 19,737 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
162. 13.733 13.733 ↓ 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.010..13.733 rows=19,737 loops=1)

163. 2.111 4.126 ↓ 1.0 18,503 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
164. 2.015 2.015 ↓ 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.024..2.015 rows=18,503 loops=1)

  • Heap Fetches: 563
165. 0.766 1.584 ↑ 1.0 6,558 1

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

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

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

167. 178.944 178.944 ↓ 1.0 763,562 1

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

168. 0.005 0.010 ↓ 36.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
169. 0.005 0.005 ↓ 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.005 rows=36 loops=1)

170. 0.276 1.451 ↓ 2.0 2,304 1

Hash (cost=1,186.49..1,186.49 rows=1,129 width=8) (actual time=1.451..1.451 rows=2,304 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 122kB
171. 0.267 1.175 ↓ 2.1 2,322 1

Nested Loop (cost=32.61..1,186.49 rows=1,129 width=8) (actual time=0.206..1.175 rows=2,322 loops=1)

172. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on tablemetadata tm (cost=0.00..2.98 rows=1 width=8) (actual time=0.029..0.036 rows=1 loops=1)

  • Filter: ((modelname)::text = 'finding'::text)
  • Rows Removed by Filter: 78
173. 0.741 0.872 ↓ 2.2 2,322 1

Bitmap Heap Scan on role_permissions_association rpa_1 (cost=32.61..1,172.95 rows=1,057 width=16) (actual time=0.170..0.872 rows=2,322 loops=1)

  • Recheck Cond: (entity_id = tm.tablesequenceid)
  • Heap Blocks: exact=309
174. 0.131 0.131 ↓ 2.2 2,322 1

Bitmap Index Scan on role_permissions_association_entity_id_index (cost=0.00..32.34 rows=1,057 width=0) (actual time=0.131..0.131 rows=2,322 loops=1)

  • Index Cond: (entity_id = tm.tablesequenceid)
175. 1.644 6,453.293 ↑ 29.5 60 1

HashAggregate (cost=4,527,061.41..4,527,079.11 rows=1,770 width=8) (actual time=6,453.281..6,453.293 rows=60 loops=1)

  • Group Key: ur_2.user_id
176. 58.549 6,451.649 ↑ 16,350.1 12,819 1

Merge Join (cost=2,695,399.41..4,003,082.77 rows=209,591,458 width=8) (actual time=6,310.917..6,451.649 rows=12,819 loops=1)

  • Merge Cond: ((rpa_2.entity_id = t_1.tablesequenceid) AND (ur_2.user_group_id = ucea_2.usergroup_id))
177. 1,126.220 1,349.908 ↑ 3.0 721,832 1

Sort (cost=301,420.77..306,830.71 rows=2,163,976 width=24) (actual time=1,268.413..1,349.908 rows=721,832 loops=1)

  • Sort Key: rpa_2.entity_id, ur_2.user_group_id
  • Sort Method: external merge Disk: 53,960kB
178. 200.201 223.688 ↑ 1.1 1,898,285 1

Hash Join (cost=3,077.31..29,333.66 rows=2,163,976 width=24) (actual time=22.788..223.688 rows=1,898,285 loops=1)

  • Hash Cond: (ur_2.role_id = rpa_2.role_id)
179. 0.830 0.830 ↑ 1.1 4,834 1

Seq Scan on users_roles ur_2 (cost=0.00..124.34 rows=5,134 width=24) (actual time=0.012..0.830 rows=4,834 loops=1)

180. 10.049 22.657 ↑ 1.0 87,469 1

Hash (cost=1,976.58..1,976.58 rows=88,058 width=16) (actual time=22.656..22.657 rows=87,469 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,680kB
181. 12.608 12.608 ↑ 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.011..12.608 rows=88,058 loops=1)

182. 0.662 5,043.192 ↑ 752.9 13,191 1

Materialize (cost=2,393,913.73..2,443,571.19 rows=9,931,491 width=16) (actual time=5,042.494..5,043.192 rows=13,191 loops=1)

183. 0.223 5,042.530 ↑ 19,397.4 512 1

Sort (cost=2,393,913.73..2,418,742.46 rows=9,931,491 width=16) (actual time=5,042.488..5,042.530 rows=512 loops=1)

  • Sort Key: t_1.tablesequenceid, ucea_2.usergroup_id
  • Sort Method: quicksort Memory: 50kB
184. 64.062 5,042.307 ↑ 17,959.3 553 1

Nested Loop (cost=670,759.04..1,069,967.76 rows=9,931,491 width=16) (actual time=2,477.261..5,042.307 rows=553 loops=1)

  • Join Filter: ((((t_1.modelname)::text = 'finding'::text) AND (hashed SubPlan 10)) OR (hashed SubPlan 12))
  • Rows Removed by Join Filter: 526,614
185. 0.114 0.114 ↓ 1.0 79 1

Index Scan using tablemetadata_pkey on tablemetadata t_1 (cost=0.14..17.12 rows=78 width=23) (actual time=0.017..0.114 rows=79 loops=1)

186. 26.047 31.679 ↑ 37.9 6,673 79

Materialize (cost=11,436.36..16,530.75 rows=253,032 width=40) (actual time=0.052..0.401 rows=6,673 loops=79)

187. 1.064 5.632 ↑ 37.9 6,673 1

Merge Left Join (cost=11,436.36..15,265.59 rows=253,032 width=40) (actual time=4.132..5.632 rows=6,673 loops=1)

  • Merge Cond: (ucea_2.networksite_id = snetworkchild.parent_networksite_id)
188. 1.190 3.555 ↑ 1.0 6,459 1

Sort (cost=672.66..689.54 rows=6,751 width=40) (actual time=3.186..3.555 rows=6,459 loops=1)

  • Sort Key: ucea_2.networksite_id
  • Sort Method: quicksort Memory: 551kB
189. 1.336 2.365 ↑ 1.0 6,459 1

Hash Left Join (cost=44.56..243.27 rows=6,751 width=40) (actual time=0.503..2.365 rows=6,459 loops=1)

  • Hash Cond: (ucea_2.client_id = ns.clientid)
190. 0.541 0.541 ↑ 1.1 6,385 1

Seq Scan on usergroup_client_entities_association ucea_2 (cost=0.00..146.51 rows=6,751 width=32) (actual time=0.008..0.541 rows=6,385 loops=1)

191. 0.076 0.488 ↓ 1.0 625 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
192. 0.171 0.412 ↓ 1.0 626 1

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

  • Hash Cond: (s.networksiteid = ns.networksiteid)
193. 0.068 0.068 ↓ 1.0 626 1

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

194. 0.073 0.173 ↓ 1.0 594 1

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

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

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

196. 0.152 1.013 ↑ 44.6 1,409 1

Sort (cost=10,763.70..10,920.86 rows=62,865 width=16) (actual time=0.942..1.013 rows=1,409 loops=1)

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

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

198. 0.140 0.807 ↑ 100.4 626 1

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

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

CTE siteparents

200. 0.075 0.378 ↑ 98.8 594 1

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

201. 0.090 0.090 ↓ 1.0 594 1

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

202. 0.036 0.213 ↓ 0.0 0 1

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

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
203. 0.031 0.031 ↑ 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.031 rows=594 loops=1)

204. 0.072 0.146 ↓ 1.0 594 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
205. 0.074 0.074 ↓ 1.0 594 1

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

206. 0.495 0.495 ↑ 98.8 594 1

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

207. 0.081 0.172 ↓ 1.0 626 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
208. 0.091 0.091 ↓ 1.0 626 1

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

209.          

SubPlan (for Nested Loop)

210. 0.007 2,475.100 ↑ 3.5 2 1

Unique (cost=329,661.22..329,661.25 rows=7 width=8) (actual time=2,475.091..2,475.100 rows=2 loops=1)

211. 0.028 2,475.093 ↓ 9.9 69 1

Sort (cost=329,661.22..329,661.23 rows=7 width=8) (actual time=2,475.089..2,475.093 rows=69 loops=1)

  • Sort Key: z_3.siteid
  • Sort Method: quicksort Memory: 28kB
212. 52.974 2,475.065 ↓ 9.9 69 1

Hash Semi Join (cost=234,963.17..329,661.12 rows=7 width=8) (actual time=2,474.396..2,475.065 rows=69 loops=1)

  • Hash Cond: (i_3.issueid = event_objects_3.objectid)
213. 1,274.111 2,422.077 ↑ 1.1 788,531 1

Hash Join (cost=234,963.14..327,338.24 rows=884,863 width=24) (actual time=912.000..2,422.077 rows=788,531 loops=1)

  • Hash Cond: (pf_3.finding_id = f_3.findingid)
214. 296.243 296.243 ↑ 1.0 4,150,639 1

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

215. 211.975 851.723 ↓ 1.0 763,487 1

Hash (cost=225,444.59..225,444.59 rows=761,484 width=32) (actual time=851.723..851.723 rows=763,487 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,910kB
216. 195.104 639.748 ↓ 1.0 763,487 1

Merge Join (cost=24,933.82..225,444.59 rows=761,484 width=32) (actual time=245.904..639.748 rows=763,487 loops=1)

  • Merge Cond: (i_3.issueid = f_3.issueid)
217. 45.971 257.746 ↓ 1.0 122,745 1

Sort (cost=24,932.54..25,236.11 rows=121,430 width=16) (actual time=245.860..257.746 rows=122,745 loops=1)

  • Sort Key: i_3.issueid
  • Sort Method: quicksort Memory: 8,826kB
218. 16.926 211.775 ↓ 1.0 122,745 1

Hash Left Join (cost=13,476.21..14,677.92 rows=121,430 width=16) (actual time=177.932..211.775 rows=122,745 loops=1)

  • Hash Cond: (mf_3.masterfindingid = mfg_3.masterfindingid)
219. 17.461 193.005 ↓ 1.0 122,712 1

Hash Right Join (cost=13,275.68..13,463.76 rows=121,430 width=24) (actual time=176.032..193.005 rows=122,712 loops=1)

  • Hash Cond: (cpmv_3.masterfindingid = mf_3.masterfindingid)
220. 0.188 0.188 ↑ 1.0 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_3 (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.015..0.188 rows=2,131 loops=1)

221. 22.791 175.356 ↓ 1.0 122,073 1

Hash (cost=11,757.81..11,757.81 rows=121,430 width=24) (actual time=175.356..175.356 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,700kB
222. 22.476 152.565 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,757.81 rows=121,430 width=24) (actual time=54.470..152.565 rows=122,073 loops=1)

  • Hash Cond: (mf_3.master_finding_parent_id = mfp_3.id)
223. 27.336 125.316 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,707.46 rows=121,430 width=32) (actual time=49.515..125.316 rows=122,173 loops=1)

  • Hash Cond: (i_3.masterfindingid = mf_3.masterfindingid)
224. 37.704 80.623 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,404.85 rows=121,430 width=24) (actual time=31.977..80.623 rows=122,173 loops=1)

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

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

226. 7.997 31.631 ↓ 1.0 43,047 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,530kB
227. 7.377 23.634 ↓ 1.0 43,047 1

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

  • Hash Cond: (a_4.zoneid = z_3.zoneid)
228. 4.661 16.003 ↓ 1.0 43,053 1

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

  • Merge Cond: (a_4.assetid = aa_3.asset_id)
229. 4.993 11.243 ↓ 1.0 42,937 1

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

  • Merge Cond: (a_4.assetid = apl_3.asset_id)
230. 6.212 6.212 ↓ 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.029..6.212 rows=42,927 loops=1)

  • Heap Fetches: 4,876
231. 0.038 0.038 ↑ 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.017..0.038 rows=27 loops=1)

  • Heap Fetches: 27
232. 0.099 0.099 ↓ 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.023..0.099 rows=317 loops=1)

  • Heap Fetches: 158
233. 0.123 0.254 ↓ 1.0 686 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
234. 0.131 0.131 ↓ 1.0 686 1

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

235. 4.004 17.357 ↓ 1.0 19,737 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
236. 13.353 13.353 ↓ 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.009..13.353 rows=19,737 loops=1)

237. 2.732 4.773 ↓ 1.0 18,503 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
238. 2.041 2.041 ↓ 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.032..2.041 rows=18,503 loops=1)

  • Heap Fetches: 563
239. 1.045 1.844 ↑ 1.0 6,558 1

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

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

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

241. 186.898 186.898 ↓ 1.0 763,562 1

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

242. 0.009 0.014 ↓ 36.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
243. 0.005 0.005 ↓ 36.0 36 1

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

244. 0.006 2,471.352 ↑ 3.5 2 1

Unique (cost=329,661.22..329,661.25 rows=7 width=8) (actual time=2,471.344..2,471.352 rows=2 loops=1)

245. 0.019 2,471.346 ↓ 9.9 69 1

Sort (cost=329,661.22..329,661.23 rows=7 width=8) (actual time=2,471.342..2,471.346 rows=69 loops=1)

  • Sort Key: z_4.clientid
  • Sort Method: quicksort Memory: 28kB
246. 52.921 2,471.327 ↓ 9.9 69 1

Hash Semi Join (cost=234,963.17..329,661.12 rows=7 width=8) (actual time=2,470.662..2,471.327 rows=69 loops=1)

  • Hash Cond: (i_4.issueid = event_objects_4.objectid)
247. 1,276.415 2,418.397 ↑ 1.1 788,531 1

Hash Join (cost=234,963.14..327,338.24 rows=884,863 width=24) (actual time=905.841..2,418.397 rows=788,531 loops=1)

  • Hash Cond: (pf_4.finding_id = f_4.findingid)
248. 294.383 294.383 ↑ 1.0 4,150,639 1

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

249. 222.144 847.599 ↓ 1.0 763,487 1

Hash (cost=225,444.59..225,444.59 rows=761,484 width=32) (actual time=847.599..847.599 rows=763,487 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,896kB
250. 194.897 625.455 ↓ 1.0 763,487 1

Merge Join (cost=24,933.82..225,444.59 rows=761,484 width=32) (actual time=233.376..625.455 rows=763,487 loops=1)

  • Merge Cond: (i_4.issueid = f_4.issueid)
251. 45.257 245.208 ↓ 1.0 122,745 1

Sort (cost=24,932.54..25,236.11 rows=121,430 width=16) (actual time=233.331..245.208 rows=122,745 loops=1)

  • Sort Key: i_4.issueid
  • Sort Method: quicksort Memory: 8,826kB
252. 16.854 199.951 ↓ 1.0 122,745 1

Hash Left Join (cost=13,476.21..14,677.92 rows=121,430 width=16) (actual time=165.927..199.951 rows=122,745 loops=1)

  • Hash Cond: (mf_4.masterfindingid = mfg_4.masterfindingid)
253. 17.135 181.489 ↓ 1.0 122,712 1

Hash Right Join (cost=13,275.68..13,463.76 rows=121,430 width=24) (actual time=164.303..181.489 rows=122,712 loops=1)

  • Hash Cond: (cpmv_4.masterfindingid = mf_4.masterfindingid)
254. 0.184 0.184 ↑ 1.0 2,131 1

Seq Scan on cve_prioritization_materialized_view cpmv_4 (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.012..0.184 rows=2,131 loops=1)

255. 17.163 164.170 ↓ 1.0 122,073 1

Hash (cost=11,757.81..11,757.81 rows=121,430 width=24) (actual time=164.170..164.170 rows=122,073 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,661kB
256. 23.358 147.007 ↓ 1.0 122,073 1

Hash Join (cost=6,183.08..11,757.81 rows=121,430 width=24) (actual time=50.764..147.007 rows=122,073 loops=1)

  • Hash Cond: (mf_4.master_finding_parent_id = mfp_4.id)
257. 25.436 119.571 ↓ 1.0 122,173 1

Hash Join (cost=5,451.57..10,707.46 rows=121,430 width=32) (actual time=46.655..119.571 rows=122,173 loops=1)

  • Hash Cond: (i_4.masterfindingid = mf_4.masterfindingid)
258. 36.984 77.199 ↓ 1.0 122,173 1

Hash Join (cost=2,467.77..7,404.85 rows=121,430 width=24) (actual time=29.680..77.199 rows=122,173 loops=1)

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

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

260. 6.032 29.613 ↓ 1.0 43,047 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,512kB
261. 7.369 23.581 ↓ 1.0 43,047 1

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

  • Hash Cond: (a_5.zoneid = z_4.zoneid)
262. 4.643 15.994 ↓ 1.0 43,053 1

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

  • Merge Cond: (a_5.assetid = aa_4.asset_id)
263. 5.129 11.276 ↓ 1.0 42,937 1

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

  • Merge Cond: (a_5.assetid = apl_4.asset_id)
264. 6.132 6.132 ↓ 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.132 rows=42,927 loops=1)

  • Heap Fetches: 4,876
265. 0.015 0.015 ↑ 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.005..0.015 rows=27 loops=1)

  • Heap Fetches: 27
266. 0.075 0.075 ↓ 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.011..0.075 rows=317 loops=1)

  • Heap Fetches: 158
267. 0.082 0.218 ↓ 1.0 686 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
268. 0.136 0.136 ↓ 1.0 686 1

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

269. 3.251 16.936 ↓ 1.0 19,737 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
270. 13.685 13.685 ↓ 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.011..13.685 rows=19,737 loops=1)

271. 2.050 4.078 ↓ 1.0 18,503 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
272. 2.028 2.028 ↓ 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.027..2.028 rows=18,503 loops=1)

  • Heap Fetches: 563
273. 0.784 1.608 ↑ 1.0 6,558 1

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

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

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

275. 185.350 185.350 ↓ 1.0 763,562 1

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

276. 0.004 0.009 ↓ 36.0 36 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
277. 0.005 0.005 ↓ 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.005 rows=36 loops=1)

Planning time : 62.554 ms
Execution time : 22,080.403 ms