explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4bbs

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 2,118.312 ↓ 0.0 0 1

Sort (cost=69,772.43..69,962.94 rows=76,201 width=326) (actual time=2,118.312..2,118.312 rows=0 loops=1)

  • Sort Key: (round(((max(GREATEST(di.risc_score_c, di.risc_score_i, di.risc_score_a)) FILTER (WHERE (if.action IS NULL))))::numeric, 0)), (round((max(GREATEST(ARRAY[di.risc_score_c, di.issue_risc_c], ARRAY[di.risc_score_i, di.issue_risc_i], ARRAY[di.risc_score_a, di.issue_risc_a])) FILTER (WHERE (if.action IS NULL)))[2])), issues.id
  • Sort Method: quicksort Memory: 25kB
2. 0.002 2,118.306 ↓ 0.0 0 1

WindowAgg (cost=45,082.38..52,130.98 rows=76,201 width=326) (actual time=2,118.306..2,118.306 rows=0 loops=1)

3. 417.167 2,118.304 ↓ 0.0 0 1

GroupAggregate (cost=45,082.38..50,225.95 rows=76,201 width=326) (actual time=2,118.304..2,118.304 rows=0 loops=1)

  • Group Key: issues.id, vulnerabilities.cve_id
  • Filter: ((round((max(GREATEST(di.risc_score_c, di.risc_score_i, di.risc_score_a)) FILTER (WHERE (if.action IS NULL)))::numeric, 0) >= 1::numeric) AND (round((max(GREATEST(di.risc_score_c, di.risc_score_i, di.risc_score_a)) FILTER (WHERE (if.action IS NULL)))::numeric, 0) <= 1::numeric))
  • Rows Removed by Filter: 677
4. 1,479.360 1,701.137 ↓ 2.6 199,560 1

Sort (cost=45,082.38..45,272.89 rows=76,201 width=326) (actual time=1,227.795..1,701.137 rows=199,560 loops=1)

  • Sort Key: issues.id, vulnerabilities.cve_id
  • Sort Method: external merge Disk: 61200kB
5. 42.728 221.777 ↓ 2.6 199,560 1

Hash Left Join (cost=3,090.16..27,440.93 rows=76,201 width=326) (actual time=14.179..221.777 rows=199,560 loops=1)

  • Hash Cond: (issues.id = if.issue_id)
  • Join Filter: (((if.device_id IS NULL) OR (if.device_id = di.device_id)) AND ((if.label_id IS NULL) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
6. 73.109 179.048 ↓ 2.6 199,560 1

Hash Join (cost=3,071.87..23,254.13 rows=76,201 width=322) (actual time=14.168..179.048 rows=199,560 loops=1)

  • Hash Cond: (di.issue_id = issues.id)
7. 39.151 103.596 ↓ 1.2 101,255 1

Hash Join (cost=2,632.79..21,637.92 rows=83,016 width=89) (actual time=11.818..103.596 rows=101,255 loops=1)

  • Hash Cond: (di.device_id = devices.id)
8. 54.330 62.434 ↑ 1.0 101,255 1

Bitmap Heap Scan on device_issues di (cost=1,928.08..19,589.73 rows=102,665 width=65) (actual time=9.791..62.434 rows=101,255 loops=1)

  • Filter: (NOT resolved)
  • Heap Blocks: exact=10461
9. 8.104 8.104 ↑ 1.0 101,299 1

Bitmap Index Scan on device_issues_resolved_idx (cost=0.00..1,902.41 rows=102,665 width=0) (actual time=8.104..8.104 rows=101,299 loops=1)

  • Index Cond: (resolved = false)
10. 0.254 2.011 ↑ 1.0 1,597 1

Hash (cost=684.75..684.75 rows=1,597 width=32) (actual time=2.011..2.011 rows=1,597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
11. 1.757 1.757 ↑ 1.0 1,597 1

Seq Scan on devices (cost=0.00..684.75 rows=1,597 width=32) (actual time=0.004..1.757 rows=1,597 loops=1)

  • Filter: (NOT archived)
  • Rows Removed by Filter: 378
12. 0.396 2.343 ↓ 1.2 1,378 1

Hash (cost=424.96..424.96 rows=1,130 width=241) (actual time=2.343..2.343 rows=1,378 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 363kB
13. 0.612 1.947 ↓ 1.2 1,378 1

Hash Right Join (cost=365.96..424.96 rows=1,130 width=241) (actual time=1.160..1.947 rows=1,378 loops=1)

  • Hash Cond: ((software_vulnerability_items.organization_id = issues.organization_id) AND (software_vulnerability_items.vulnerability_id = vulnerabilities.id))
14. 0.183 0.183 ↑ 1.0 1,185 1

Seq Scan on software_vulnerability_items (cost=0.00..38.81 rows=1,185 width=16) (actual time=0.003..0.183 rows=1,185 loops=1)

  • Filter: (organization_id = 1)
15. 0.284 1.152 ↑ 1.0 677 1

Hash (cost=355.80..355.80 rows=677 width=257) (actual time=1.152..1.152 rows=677 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 176kB
16. 0.171 0.868 ↑ 1.0 677 1

Hash Left Join (cost=107.97..355.80 rows=677 width=257) (actual time=0.295..0.868 rows=677 loops=1)

  • Hash Cond: (issues.vulnerability_id = vulnerabilities.id)
17. 0.414 0.414 ↑ 1.0 677 1

Seq Scan on issues (cost=0.00..241.39 rows=677 width=235) (actual time=0.007..0.414 rows=677 loops=1)

  • Filter: ((NOT resolved) AND (organization_id = 1))
  • Rows Removed by Filter: 554
18. 0.093 0.283 ↑ 1.0 710 1

Hash (cost=99.10..99.10 rows=710 width=22) (actual time=0.283..0.283 rows=710 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
19. 0.190 0.190 ↑ 1.0 710 1

Seq Scan on vulnerabilities (cost=0.00..99.10 rows=710 width=22) (actual time=0.003..0.190 rows=710 loops=1)

20. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=18.25..18.25 rows=3 width=28) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
21. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on issue_filters if (cost=0.00..18.25 rows=3 width=28) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (action = 1)
22.          

SubPlan (for Hash Left Join)

23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using devices_labels_organization_id_device_id_label_id_idx on devices_labels dl (cost=0.15..20.86 rows=1 width=0) (never executed)

  • Index Cond: ((device_id = di.device_id) AND (label_id = if.label_id))
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on devices_labels dl_1 (cost=0.00..22.70 rows=1,270 width=16) (never executed)

Planning time : 1.269 ms
Execution time : 2,127.754 ms