explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Agr

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 14.738 ↓ 0.0 0 1

Sort (cost=493.92..494.00 rows=30 width=294) (actual time=14.738..14.738 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)) DESC NULLS LAST, (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])) DESC, issues.id
  • Sort Method: quicksort Memory: 25kB
2. 0.001 14.725 ↓ 0.0 0 1

WindowAgg (cost=401.86..493.19 rows=30 width=294) (actual time=14.725..14.725 rows=0 loops=1)

3. 4.322 14.724 ↓ 0.0 0 1

GroupAggregate (cost=401.86..492.44 rows=30 width=273) (actual time=14.724..14.724 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: 268
4. 1.233 10.402 ↓ 2.6 3,858 1

Merge Left Join (cost=401.86..421.73 rows=1,506 width=274) (actual time=8.868..10.402 rows=3,858 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. 4.723 9.157 ↓ 2.6 3,858 1

Sort (cost=383.33..387.10 rows=1,506 width=270) (actual time=8.847..9.157 rows=3,858 loops=1)

  • Sort Key: issues.id
  • Sort Method: quicksort Memory: 2078kB
6. 0.875 4.434 ↓ 2.6 3,858 1

Hash Left Join (cost=37.22..303.84 rows=1,506 width=270) (actual time=0.298..4.434 rows=3,858 loops=1)

  • Hash Cond: ((issues.organization_id = software_vulnerability_items.organization_id) AND (issues.vulnerability_id = software_vulnerability_items.vulnerability_id))
7. 1.106 3.544 ↓ 2.6 3,858 1

Hash Join (cost=35.59..286.31 rows=1,506 width=278) (actual time=0.267..3.544 rows=3,858 loops=1)

  • Hash Cond: (di.device_id = devices.id)
8. 1.455 2.423 ↓ 1.5 3,858 1

Hash Join (cost=32.50..271.05 rows=2,510 width=254) (actual time=0.232..2.423 rows=3,858 loops=1)

  • Hash Cond: (di.issue_id = issues.id)
9. 0.786 0.786 ↑ 1.0 3,858 1

Seq Scan on device_issues di (cost=0.00..228.32 rows=3,858 width=65) (actual time=0.028..0.786 rows=3,858 loops=1)

  • Filter: (NOT resolved)
  • Rows Removed by Filter: 174
10. 0.080 0.182 ↑ 1.0 268 1

Hash (cost=29.15..29.15 rows=268 width=197) (actual time=0.182..0.182 rows=268 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
11. 0.102 0.102 ↑ 1.0 268 1

Seq Scan on issues (cost=0.00..29.15 rows=268 width=197) (actual time=0.004..0.102 rows=268 loops=1)

  • Filter: ((NOT resolved) AND (organization_id = 1))
  • Rows Removed by Filter: 144
12. 0.003 0.015 ↑ 1.0 3 1

Hash (cost=3.05..3.05 rows=3 width=32) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.012 0.012 ↑ 1.0 3 1

Seq Scan on devices (cost=0.00..3.05 rows=3 width=32) (actual time=0.009..0.012 rows=3 loops=1)

  • Filter: (NOT archived)
  • Rows Removed by Filter: 2
14. 0.005 0.015 ↓ 1.2 28 1

Hash (cost=1.29..1.29 rows=23 width=16) (actual time=0.015..0.015 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.010 0.010 ↓ 1.2 28 1

Seq Scan on software_vulnerability_items (cost=0.00..1.29 rows=23 width=16) (actual time=0.005..0.010 rows=28 loops=1)

  • Filter: (organization_id = 1)
16. 0.005 0.012 ↓ 0.0 0 1

Sort (cost=18.52..18.53 rows=3 width=28) (actual time=0.012..0.012 rows=0 loops=1)

  • Sort Key: if.issue_id
  • Sort Method: quicksort Memory: 25kB
17. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: (action = 1)
18.          

SubPlan (for Merge Left Join)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on devices_labels dl (cost=0.00..1.10 rows=1 width=0) (never executed)

  • Filter: ((device_id = di.device_id) AND (label_id = if.label_id))
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on devices_labels dl_1 (cost=0.00..1.07 rows=7 width=16) (never executed)

Planning time : 1.102 ms
Execution time : 14.918 ms