explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7ym9r

Settings
# exclusive inclusive rows x rows loops node
1. 5.831 216,850.907 ↑ 1.0 1 1

Aggregate (cost=288,577.66..288,577.67 rows=1 width=8) (actual time=216,850.906..216,850.907 rows=1 loops=1)

2. 432.210 216,845.076 ↓ 9,031.0 9,031 1

Unique (cost=288,577.64..288,577.65 rows=1 width=14) (actual time=215,947.100..216,845.076 rows=9,031 loops=1)

3. 1,516.240 216,412.866 ↓ 652,634.0 652,634 1

Sort (cost=288,577.64..288,577.64 rows=1 width=14) (actual time=215,947.097..216,412.866 rows=652,634 loops=1)

  • Sort Key: vr.vuln_id
  • Sort Method: external merge Disk: 15,864kB
4. 37,000.853 214,896.626 ↓ 652,634.0 652,634 1

Nested Loop (cost=255,451.75..288,577.63 rows=1 width=14) (actual time=4,957.917..214,896.626 rows=652,634 loops=1)

  • Join Filter: (vr.vuln_id = vbrw.vuln_id)
  • Rows Removed by Join Filter: 49,150,594
5. 3,899.095 12,868.372 ↓ 10,375.2 684,761 1

Merge Join (cost=255,451.32..267,227.05 rows=66 width=22) (actual time=4,955.523..12,868.372 rows=684,761 loops=1)

  • Merge Cond: ((vr.producer_release_id = vbc.release_id) AND (vb.id = vbc.version_bom_id))
  • Join Filter: ((vr.producer_channel_release_id = vbc.channel_release_id) OR ((vr.producer_channel_release_id = '00000000-0000-4000-0000-000000000000'::uuid) AND (vbc.channel_release_id IS NULL)))
  • Rows Removed by Join Filter: 2,487,098
6. 2,334.790 4,744.916 ↓ 1.0 825,157 1

Sort (cost=169,481.05..171,488.61 rows=803,022 width=54) (actual time=3,759.381..4,744.916 rows=825,157 loops=1)

  • Sort Key: vr.producer_release_id, vb.id
  • Sort Method: external merge Disk: 52,272kB
7. 1,173.135 2,410.126 ↓ 1.0 825,157 1

Hash Join (cost=69.43..63,273.85 rows=803,022 width=54) (actual time=3.509..2,410.126 rows=825,157 loops=1)

  • Hash Cond: (vr.consumer_release_id = vb.release_id)
8. 1,233.518 1,233.518 ↓ 1.0 825,157 1

Seq Scan on vuln_remediation vr (cost=0.00..52,162.87 rows=803,022 width=62) (actual time=0.021..1,233.518 rows=825,157 loops=1)

  • Filter: ((vuln_source = ANY ('{NVD,BDSA}'::text[])) AND (status = ANY ('{NEW,NEEDS_REVIEW,REMEDIATION_REQUIRED}'::text[])))
  • Rows Removed by Filter: 116,810
9. 2.000 3.473 ↑ 1.0 2,019 1

Hash (cost=44.19..44.19 rows=2,019 width=24) (actual time=3.472..3.473 rows=2,019 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 127kB
10. 1.473 1.473 ↑ 1.0 2,019 1

Seq Scan on version_bom vb (cost=0.00..44.19 rows=2,019 width=24) (actual time=0.008..1.473 rows=2,019 loops=1)

11. 2,608.979 4,224.361 ↓ 7.2 3,586,206 1

Materialize (cost=85,954.01..88,450.17 rows=499,232 width=48) (actual time=1,196.049..4,224.361 rows=3,586,206 loops=1)

12. 1,222.062 1,615.382 ↑ 1.0 496,800 1

Sort (cost=85,954.01..87,202.09 rows=499,232 width=48) (actual time=1,196.043..1,615.382 rows=496,800 loops=1)

  • Sort Key: vbc.release_id, vbc.version_bom_id
  • Sort Method: external merge Disk: 28,000kB
13. 393.320 393.320 ↑ 1.0 498,303 1

Seq Scan on version_bom_component vbc (cost=0.00..23,345.32 rows=499,232 width=48) (actual time=0.014..393.320 rows=498,303 loops=1)

14. 165,027.401 165,027.401 ↑ 2.0 73 684,761

Index Scan using idx_version_bom_risk_warning_version_bom_component_id on version_bom_risk_warning vbrw (cost=0.43..321.67 rows=146 width=22) (actual time=0.052..0.241 rows=73 loops=684,761)

  • Index Cond: (version_bom_component_id = vbc.id)
  • Filter: (category = 'VULNERABILITY'::text)
  • Rows Removed by Filter: 4
Planning time : 60.073 ms
Execution time : 216,860.125 ms