explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DTGp

Settings
# exclusive inclusive rows x rows loops node
1. 935.093 42,124.681 ↑ 1.0 8 1

GroupAggregate (cost=430,901.19..432,549.33 rows=8 width=134) (actual time=39,855.216..42,124.681 rows=8 loops=1)

  • Group Key: i.severityid, ((SubPlan 1))
2. 3,526.924 41,189.588 ↓ 4.4 720,163 1

Sort (cost=430,901.19..431,310.98 rows=163,916 width=134) (actual time=39,854.489..41,189.588 rows=720,163 loops=1)

  • Sort Key: i.severityid, ((SubPlan 1))
  • Sort Method: external merge Disk: 25,704kB
3. 3,796.550 37,662.664 ↓ 4.4 720,163 1

Hash Left Join (cost=12,144.80..416,703.93 rows=163,916 width=134) (actual time=1,028.689..37,662.664 rows=720,163 loops=1)

  • Hash Cond: (mf.masterfindingid = mfg.masterfindingid)
4. 8,718.722 14,420.114 ↓ 4.4 720,148 1

Gather (cost=11,944.27..232,778.58 rows=163,916 width=24) (actual time=1,027.051..14,420.114 rows=720,148 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 315.091 5,701.392 ↓ 3.5 240,049 3 / 3

Nested Loop (cost=10,944.27..215,386.98 rows=68,298 width=24) (actual time=1,174.319..5,701.392 rows=240,049 loops=3)

6. 576.584 2,559.397 ↓ 4.0 235,575 3 / 3

Merge Join (cost=10,944.27..199,932.05 rows=58,968 width=32) (actual time=1,174.289..2,559.397 rows=235,575 loops=3)

  • Merge Cond: (f.issueid = i.issueid)
7. 373.111 373.111 ↑ 1.3 252,409 3 / 3

Parallel Index Scan using issueid_id_final_remediated_state on finding f (cost=0.42..211,020.79 rows=318,342 width=24) (actual time=0.025..373.111 rows=252,409 loops=3)

8. 518.810 1,609.702 ↓ 11.3 252,271 3 / 3

Sort (cost=10,943.58..10,999.43 rows=22,341 width=24) (actual time=1,174.237..1,609.702 rows=252,271 loops=3)

  • Sort Key: i.issueid
  • Sort Method: quicksort Memory: 2,525kB
  • Worker 0: Sort Method: quicksort Memory: 2,525kB
  • Worker 1: Sort Method: quicksort Memory: 2,525kB
9. 44.084 1,090.892 ↓ 1.0 22,485 3 / 3

Hash Right Join (cost=9,262.24..9,329.73 rows=22,341 width=24) (actual time=1,046.900..1,090.892 rows=22,485 loops=3)

  • Hash Cond: (cpmv.masterfindingid = mf.masterfindingid)
10. 0.201 0.201 ↑ 1.0 2,131 3 / 3

Seq Scan on cve_prioritization_materialized_view cpmv (cost=0.00..35.31 rows=2,131 width=8) (actual time=0.032..0.201 rows=2,131 loops=3)

11. 4.181 1,046.607 ↑ 1.0 22,292 3 / 3

Hash (cost=8,982.98..8,982.98 rows=22,341 width=24) (actual time=1,046.607..1,046.607 rows=22,292 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,476kB
12. 41.883 1,042.426 ↑ 1.0 22,292 3 / 3

Hash Join (cost=5,952.17..8,982.98 rows=22,341 width=24) (actual time=574.913..1,042.426 rows=22,292 loops=3)

  • Hash Cond: (mf.master_finding_parent_id = mfp.id)
13. 80.500 911.708 ↑ 1.0 22,303 3 / 3

Hash Left Join (cost=5,220.66..8,192.81 rows=22,341 width=32) (actual time=485.954..911.708 rows=22,303 loops=3)

  • Hash Cond: (a.assetid = aa.asset_id)
14. 82.768 831.084 ↑ 1.0 21,925 3 / 3

Hash Join (cost=5,211.29..7,846.86 rows=22,341 width=40) (actual time=485.810..831.084 rows=21,925 loops=3)

  • Hash Cond: (i.masterfindingid = mf.masterfindingid)
15. 2.939 497.893 ↑ 1.0 21,925 3 / 3

Hash Left Join (cost=2,227.50..4,804.40 rows=22,341 width=32) (actual time=235.250..497.893 rows=21,925 loops=3)

  • Hash Cond: (a.assetid = apl.asset_id)
16. 4.281 494.916 ↑ 1.0 21,652 3 / 3

Hash Join (cost=2,225.89..4,718.88 rows=22,341 width=32) (actual time=235.192..494.916 rows=21,652 loops=3)

  • Hash Cond: (a.zoneid = z.zoneid)
17. 120.010 490.375 ↑ 1.0 21,652 3 / 3

Hash Join (cost=2,200.54..4,634.52 rows=22,342 width=40) (actual time=234.911..490.375 rows=21,652 loops=3)

  • Hash Cond: (i.assetid = a.assetid)
18. 135.806 136.765 ↑ 1.0 21,653 3 / 3

Bitmap Heap Scan on issue i (cost=417.98..2,793.29 rows=22,342 width=32) (actual time=1.072..136.765 rows=21,653 loops=3)

  • Recheck Cond: (is_published IS TRUE)
  • Filter: (issueremediationstate <> ALL ('{3,5,9,11}'::bigint[]))
  • Rows Removed by Filter: 316
  • Heap Blocks: exact=908
19. 0.959 0.959 ↓ 1.0 22,628 3 / 3

Bitmap Index Scan on issue_is_published (cost=0.00..412.39 rows=22,421 width=0) (actual time=0.959..0.959 rows=22,628 loops=3)

20. 109.719 233.600 ↓ 1.0 42,916 3 / 3

Hash (cost=1,246.14..1,246.14 rows=42,914 width=16) (actual time=233.600..233.600 rows=42,916 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,524kB
21. 123.881 123.881 ↓ 1.0 42,916 3 / 3

Seq Scan on asset a (cost=0.00..1,246.14 rows=42,914 width=16) (actual time=0.021..123.881 rows=42,916 loops=3)

22. 0.100 0.260 ↓ 1.0 686 3 / 3

Hash (cost=16.82..16.82 rows=682 width=16) (actual time=0.259..0.260 rows=686 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
23. 0.160 0.160 ↓ 1.0 686 3 / 3

Seq Scan on zone z (cost=0.00..16.82 rows=682 width=16) (actual time=0.031..0.160 rows=686 loops=3)

24. 0.008 0.038 ↑ 1.0 27 3 / 3

Hash (cost=1.27..1.27 rows=27 width=8) (actual time=0.038..0.038 rows=27 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.030 0.030 ↑ 1.0 27 3 / 3

Seq Scan on asset_physical_location_mav apl (cost=0.00..1.27 rows=27 width=8) (actual time=0.027..0.030 rows=27 loops=3)

26. 4.456 250.423 ↓ 1.0 19,737 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,231kB
27. 245.967 245.967 ↓ 1.0 19,737 3 / 3

Seq Scan on masterfinding mf (cost=0.00..2,738.91 rows=19,591 width=24) (actual time=0.014..245.967 rows=19,737 loops=3)

28. 0.042 0.124 ↓ 1.0 296 3 / 3

Hash (cost=5.83..5.83 rows=283 width=8) (actual time=0.123..0.124 rows=296 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
29. 0.082 0.082 ↓ 1.0 296 3 / 3

Seq Scan on application_asset aa (cost=0.00..5.83 rows=283 width=8) (actual time=0.034..0.082 rows=296 loops=3)

30. 86.589 88.835 ↓ 1.0 18,503 3 / 3

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 979kB
31. 2.246 2.246 ↓ 1.0 18,503 3 / 3

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.056..2.246 rows=18,503 loops=3)

  • Heap Fetches: 1,689
32. 2,826.904 2,826.904 ↑ 1.0 1 706,726 / 3

Index Scan using project_finding_finding_id_hash on project_finding pf (cost=0.00..0.25 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=706,726)

  • Index Cond: (finding_id = f.findingid)
  • Rows Removed by Index Recheck: 0
33. 0.782 1.599 ↑ 1.0 6,558 1

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

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

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

35.          

SubPlan (for Hash Left Join)

36. 14,403.260 19,444.401 ↑ 1.0 1 720,163

Limit (cost=0.00..1.11 rows=1 width=8) (actual time=0.026..0.027 rows=1 loops=720,163)

37. 5,041.141 5,041.141 ↑ 1.0 1 720,163

Seq Scan on severity s (cost=0.00..1.11 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=720,163)

  • Filter: (severityid = i.severityid)
  • Rows Removed by Filter: 4
Planning time : 4.696 ms
Execution time : 42,127.538 ms