explain.depesz.com

PostgreSQL's explain analyze made readable

Result: muF

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9.857 9,283.105 ↓ 2.7 7,994 1

Unique (cost=18,688.06..18,769.33 rows=2,955 width=187) (actual time=9,257.787..9,283.105 rows=7,994 loops=1)

2. 49.003 9,273.248 ↓ 2.7 7,994 1

Sort (cost=18,688.06..18,695.45 rows=2,955 width=187) (actual time=9,257.785..9,273.248 rows=7,994 loops=1)

  • Sort Key: dependency.name, dependency.version, dependency.id, dependency.normalized_name, dependency.location, dependency.maven_version, dependency.origin, dependency.license, dependency.type, (string_agg((cve.id)::
  • Sort Method: external merge Disk: 5,088kB
3. 803.706 9,224.245 ↓ 2.7 7,994 1

GroupAggregate (cost=18,443.85..18,517.72 rows=2,955 width=187) (actual time=7,980.518..9,224.245 rows=7,994 loops=1)

  • Group Key: dependency.id
4. 3,478.852 8,420.539 ↓ 633.2 1,871,153 1

Sort (cost=18,443.85..18,451.23 rows=2,955 width=159) (actual time=7,976.709..8,420.539 rows=1,871,153 loops=1)

  • Sort Key: dependency.id
  • Sort Method: external merge Disk: 246,928kB
5. 619.363 4,941.687 ↓ 633.2 1,871,153 1

Hash Anti Join (cost=7,436.74..18,273.51 rows=2,955 width=159) (actual time=323.918..4,941.687 rows=1,871,153 loops=1)

  • Hash Cond: (cve.id = cve_filter_project.cve_id)
6. 1,750.647 4,322.314 ↓ 633.0 1,871,153 1

Hash Left Join (cost=7,433.32..18,232.77 rows=2,956 width=159) (actual time=323.899..4,322.314 rows=1,871,153 loops=1)

  • Hash Cond: (image.id = project_image_1.image_id)
  • Filter: ((project.id = 2) OR (image_dependency.image_id IS NULL))
  • Rows Removed by Filter: 2,124,982
7. 2,225.138 2,571.130 ↓ 14.7 3,571,554 1

Hash Right Join (cost=7,403.73..13,663.99 rows=242,818 width=167) (actual time=323.346..2,571.130 rows=3,571,554 loops=1)

  • Hash Cond: (image_dependency_1.dependency_id = dependency.id)
8. 15.554 28.013 ↓ 1.0 46,128 1

Merge Join (cost=40.29..1,562.79 rows=46,008 width=8) (actual time=0.548..28.013 rows=46,128 loops=1)

  • Merge Cond: (image.id = image_dependency_1.image_id)
9. 0.398 1.091 ↑ 1.0 355 1

Merge Left Join (cost=40.00..57.09 rows=355 width=4) (actual time=0.509..1.091 rows=355 loops=1)

  • Merge Cond: (image.id = imagetag.image_id)
10. 0.155 0.155 ↑ 1.0 353 1

Index Only Scan using pk_image on image (cost=0.27..11.07 rows=353 width=4) (actual time=0.043..0.155 rows=353 loops=1)

  • Heap Fetches: 0
11. 0.317 0.538 ↑ 1.1 355 1

Sort (cost=39.72..40.66 rows=374 width=4) (actual time=0.459..0.538 rows=355 loops=1)

  • Sort Key: imagetag.image_id
  • Sort Method: quicksort Memory: 41kB
12. 0.221 0.221 ↑ 1.1 355 1

Seq Scan on imagetag (cost=0.00..23.74 rows=374 width=4) (actual time=0.022..0.221 rows=355 loops=1)

13. 11.368 11.368 ↓ 1.0 46,128 1

Index Only Scan using pk_image_dependency on image_dependency image_dependency_1 (cost=0.29..924.32 rows=45,762 width=8) (actual time=0.031..11.368 rows=46,128 loops=1)

  • Heap Fetches: 0
14. 93.737 317.979 ↓ 1.5 96,565 1

Hash (cost=4,991.53..4,991.53 rows=65,993 width=163) (actual time=317.979..317.979 rows=96,565 loops=1)

  • Buckets: 8,192 (originally 8192) Batches: 64 (originally 16) Memory Usage: 961kB
15. 115.503 224.242 ↓ 1.5 96,565 1

Hash Right Join (cost=2,471.96..4,991.53 rows=65,993 width=163) (actual time=92.327..224.242 rows=96,565 loops=1)

  • Hash Cond: (image_dependency.dependency_id = dependency.id)
16. 16.523 16.523 ↑ 1.0 45,762 1

Index Only Scan using pk_image_dependency on image_dependency (cost=0.29..924.32 rows=45,762 width=8) (actual time=0.049..16.523 rows=45,762 loops=1)

  • Heap Fetches: 0
17. 19.674 92.216 ↓ 1.3 23,379 1

Hash (cost=1,840.27..1,840.27 rows=18,032 width=159) (actual time=92.216..92.216 rows=23,379 loops=1)

  • Buckets: 8,192 Batches: 4 Memory Usage: 878kB
18. 41.064 72.542 ↓ 1.3 23,379 1

Hash Right Join (cost=921.24..1,840.27 rows=18,032 width=159) (actual time=22.881..72.542 rows=23,379 loops=1)

  • Hash Cond: (dependency_cve_1.dependency_id = dependency.id)
19. 7.021 11.766 ↑ 1.3 13,581 1

Hash Join (cost=128.90..577.58 rows=18,032 width=8) (actual time=2.305..11.766 rows=13,581 loops=1)

  • Hash Cond: (dependency_cve_1.cve_id = cve.id)
20. 2.476 2.476 ↑ 1.3 13,581 1

Seq Scan on dependency_cve dependency_cve_1 (cost=0.00..401.32 rows=18,032 width=8) (actual time=0.014..2.476 rows=13,581 loops=1)

21. 1.216 2.269 ↓ 1.1 4,358 1

Hash (cost=78.44..78.44 rows=4,037 width=4) (actual time=2.269..2.269 rows=4,358 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 218kB
22. 1.053 1.053 ↓ 1.1 4,358 1

Index Only Scan using pk_cve on cve (cost=0.28..78.44 rows=4,037 width=4) (actual time=0.026..1.053 rows=4,358 loops=1)

  • Heap Fetches: 114
23. 12.499 19.712 ↑ 1.1 11,502 1

Hash (cost=355.04..355.04 rows=12,504 width=155) (actual time=19.712..19.712 rows=11,502 loops=1)

  • Buckets: 8,192 Batches: 4 Memory Usage: 436kB
24. 7.213 7.213 ↑ 1.1 11,502 1

Seq Scan on dependency (cost=0.00..355.04 rows=12,504 width=155) (actual time=0.025..7.213 rows=11,502 loops=1)

25. 0.129 0.537 ↑ 1.0 389 1

Hash (cost=24.72..24.72 rows=390 width=8) (actual time=0.537..0.537 rows=389 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
26. 0.187 0.408 ↑ 1.0 389 1

Hash Join (cost=12.30..24.72 rows=390 width=8) (actual time=0.050..0.408 rows=389 loops=1)

  • Hash Cond: (project_image_1.project_id = project.id)
27. 0.202 0.202 ↑ 1.0 389 1

Index Only Scan using pk_project_image on project_image project_image_1 (cost=0.27..11.62 rows=390 width=8) (actual time=0.021..0.202 rows=389 loops=1)

  • Heap Fetches: 389
28. 0.006 0.019 ↑ 10.0 9 1

Hash (cost=10.90..10.90 rows=90 width=4) (actual time=0.019..0.019 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.013 0.013 ↑ 10.0 9 1

Seq Scan on project (cost=0.00..10.90 rows=90 width=4) (actual time=0.010..0.013 rows=9 loops=1)

30. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=3.40..3.40 rows=2 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
31. 0.003 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on cve_filter_project (cost=1.26..3.40 rows=2 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: (project_id = 2)
32. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on pk_cve_filter_project (cost=0.00..1.26 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (project_id = 2)