explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vx4g

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 2,533.968 ↓ 0.0 0 1

Sort (cost=71,021.43..71,210.94 rows=75,804 width=326) (actual time=2,533.968..2,533.968 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
  • (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • loops=1)
2. 0.001 2,533.961 ↓ 0.0 0 1

WindowAgg (cost=46,462.66..53,474.53 rows=75,804 width=326) (actual time=2,533.961..2,533.961 rows=0 loops=1)

3. 478.118 2,533.960 ↓ 0.0 0 1

GroupAggregate (cost=46,462.66..51,579.43 rows=75,804 width=326) (actual time=2,533.960..2,533.960 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,841.393 2,055.842 ↓ 2.6 199,601 1

Sort (cost=46,462.66..46,652.17 rows=75,804 width=326) (actual time=1,553.885..2,055.842 rows=199,601 loops=1)

  • Sort Key: issues.id, vulnerabilities.cve_id
  • Sort Method: external merge Disk: 61208kB
  • -> Hash Left Join (cost=1161.91..28915.75 rows=75804 width=326) (actual time=12.264..364.172 rows=199601loops=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
  • -> Hash Join (cost=1143.62..24751.32 rows=75804 width=322) (actual time=12.239..297.586 rows=199601
  • Hash Cond: (di.issue_id = issues.id)
5. 48.339 214.449 ↓ 1.2 101,296 1

Hash Join (cost=704.71..23,140.13 rows=82,864 width=89) (actual time=6.705..214.449 rows=101,296 loops=1)

  • Hash Cond: (di.device_id = devices.id)
6. 159.459 159.459 ↑ 1.0 101,296 1

Seq Scan on device_issues di (cost=0.00..21,094.39 rows=102,477 width=65) (actual time=0.020..159.459 rows=101,296 loops=1)

  • Filter: (NOT resolved)
  • Rows Removed by Filter: 345464
7. 0.545 6.651 ↑ 1.0 1,597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
8. 6.106 6.106 ↑ 1.0 1,597 1

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