explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e8jq

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

Sort (cost=36,168.82..36,170.51 rows=677 width=312) (actual time=1,023.374..1,023.374 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.001 1,023.368 ↓ 0.0 0 1

WindowAgg (cost=32,407.32..36,136.99 rows=677 width=312) (actual time=1,023.368..1,023.368 rows=0 loops=1)

3. 327.473 1,023.367 ↓ 0.0 0 1

GroupAggregate (cost=32,407.32..36,120.07 rows=677 width=312) (actual time=1,023.367..1,023.367 rows=0 loops=1)

  • Group Key: issues.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. 63.572 695.894 ↓ 4.4 199,551 1

Merge Left Join (cost=32,407.32..34,953.28 rows=45,656 width=312) (actual time=557.903..695.894 rows=199,551 loops=1)

  • Merge 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)))
5. 356.480 632.312 ↓ 4.4 199,551 1

Sort (cost=32,389.05..32,503.19 rows=45,656 width=308) (actual time=557.891..632.312 rows=199,551 loops=1)

  • Sort Key: issues.id
  • Sort Method: external merge Disk: 60800kB
6. 102.137 275.832 ↓ 4.4 199,551 1

Hash Join (cost=2,949.98..22,300.11 rows=45,656 width=308) (actual time=18.644..275.832 rows=199,551 loops=1)

  • Hash Cond: (di.device_id = devices.id)
7. 71.067 168.907 ↓ 3.5 199,551 1

Hash Join (cost=2,245.26..20,856.53 rows=56,462 width=284) (actual time=13.850..168.907 rows=199,551 loops=1)

  • Hash Cond: (di.issue_id = issues.id)
8. 85.630 94.911 ↑ 1.0 101,246 1

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

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

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

  • Index Cond: (resolved = false)
10. 0.399 2.929 ↓ 2.0 1,378 1

Hash (cost=308.72..308.72 rows=677 width=227) (actual time=2.929..2.929 rows=1,378 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 348kB
11. 0.577 2.530 ↓ 2.0 1,378 1

Hash Right Join (cost=251.54..308.72 rows=677 width=227) (actual time=1.705..2.530 rows=1,378 loops=1)

  • Hash Cond: ((software_vulnerability_items.organization_id = issues.organization_id) AND (software_vulnerability_items.vulnerability_id = issues.vulnerability_id))
12. 0.264 0.264 ↑ 1.0 1,185 1

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

  • Filter: (organization_id = 1)
13. 0.266 1.689 ↑ 1.0 677 1

Hash (cost=241.39..241.39 rows=677 width=235) (actual time=1.689..1.689 rows=677 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 167kB
14. 1.423 1.423 ↑ 1.0 677 1

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

  • Filter: ((NOT resolved) AND (organization_id = 1))
  • Rows Removed by Filter: 554
15. 0.292 4.788 ↑ 1.0 1,597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
16. 4.496 4.496 ↑ 1.0 1,597 1

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

  • Filter: (NOT archived)
  • Rows Removed by Filter: 378
17. 0.009 0.010 ↓ 0.0 0 1

Sort (cost=18.27..18.28 rows=3 width=28) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: if.issue_id
  • Sort Method: quicksort Memory: 25kB
18. 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)
19.          

SubPlan (for Merge Left Join)

20. 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
21. 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.434 ms
Execution time : 1,032.012 ms