explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B8bY

Settings
# exclusive inclusive rows x rows loops node
1. 2.377 18,297.097 ↑ 3.0 4,213 1

Unique (cost=15,588.00..15,931.86 rows=12,504 width=187) (actual time=18,294.430..18,297.097 rows=4,213 loops=1)

2. 12.828 18,294.720 ↑ 3.0 4,213 1

Sort (cost=15,588.00..15,619.26 rows=12,504 width=187) (actual time=18,294.428..18,294.720 rows=4,213 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: quicksort Memory: 809kB
3. 6.108 18,281.892 ↑ 3.0 4,213 1

GroupAggregate (cost=10,992.96..14,060.59 rows=12,504 width=187) (actual time=18,265.980..18,281.892 rows=4,213 loops=1)

  • Group Key: dependency.id
4. 3.585 18,275.784 ↑ 5.3 9,456 1

Nested Loop Anti Join (cost=10,992.96..13,406.92 rows=49,737 width=159) (actual time=18,265.926..18,275.784 rows=9,456 loops=1)

  • Join Filter: (cve_filter_project.cve_id = cve.id)
5. 4.877 18,272.199 ↑ 5.3 9,456 1

Merge Left Join (cost=10,991.70..11,910.66 rows=49,762 width=159) (actual time=18,265.878..18,272.199 rows=9,456 loops=1)

  • Merge Cond: (dependency.id = dependency_cve_1.dependency_id)
6. 6.801 18,251.106 ↑ 8.2 4,213 1

Sort (cost=9,139.40..9,225.67 rows=34,507 width=155) (actual time=18,250.435..18,251.106 rows=4,213 loops=1)

  • Sort Key: dependency.id
  • Sort Method: quicksort Memory: 777kB
7. 131.663 18,244.305 ↑ 8.2 4,213 1

Hash Right Join (cost=832.63..3,356.91 rows=34,507 width=155) (actual time=472.265..18,244.305 rows=4,213 loops=1)

  • Hash Cond: (image_dependency_1.dependency_id = dependency.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (NOT (SubPlan 3)))
  • Rows Removed by Filter: 45,874
8. 15.622 29.501 ↓ 1.0 46,128 1

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

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

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

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

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

  • Heap Fetches: 98
11. 0.338 0.617 ↑ 1.1 355 1

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

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

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

13. 12.444 12.444 ↓ 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.568..12.444 rows=46,128 loops=1)

  • Heap Fetches: 0
14. 32.099 43.049 ↑ 1.1 11,502 1

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

  • Buckets: 8,192 Batches: 4 Memory Usage: 439kB
15. 10.950 10.950 ↑ 1.1 11,502 1

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

16.          

SubPlan (for Hash Right Join)

17. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3.76 rows=1 width=0) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_project_image on project_image (cost=0.27..1.39 rows=1 width=4) (never executed)

  • Index Cond: ((project_id = 1) AND (image_id = image.id))
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_project on project (cost=0.14..2.36 rows=1 width=4) (never executed)

  • Index Cond: (id = 1)
  • Heap Fetches: 0
20. 0.004 0.546 ↑ 1.0 1 1

Nested Loop (cost=0.42..3.76 rows=1 width=4) (actual time=0.544..0.546 rows=1 loops=1)

21. 0.489 0.489 ↑ 1.0 1 1

Index Only Scan using pk_project_image on project_image project_image_1 (cost=0.27..1.39 rows=1 width=8) (actual time=0.489..0.489 rows=1 loops=1)

  • Index Cond: (project_id = 1)
  • Heap Fetches: 1
22. 0.053 0.053 ↑ 1.0 1 1

Index Only Scan using pk_project on project project_1 (cost=0.14..2.36 rows=1 width=4) (actual time=0.051..0.053 rows=1 loops=1)

  • Index Cond: (id = 1)
  • Heap Fetches: 1
23. 4,114.316 18,039.546 ↑ 6.0 1 49,833

Nested Loop (cost=0.56..334.55 rows=6 width=0) (actual time=0.362..0.362 rows=1 loops=49,833)

24. 1,893.654 1,893.654 ↑ 2.9 121 49,833

Index Only Scan using pk_image on image image_1 (cost=0.27..11.07 rows=353 width=4) (actual time=0.004..0.038 rows=121 loops=49,833)

  • Heap Fetches: 1,413,644
25. 12,031.576 12,031.576 ↓ 0.0 0 6,015,788

Index Only Scan using pk_image_dependency on image_dependency (cost=0.29..0.92 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=6,015,788)

  • Index Cond: ((image_id = image_1.id) AND (dependency_id = dependency.id))
  • Heap Fetches: 0
26. 6.106 16.216 ↑ 1.3 13,581 1

Sort (cost=1,852.29..1,897.37 rows=18,032 width=8) (actual time=15.278..16.216 rows=13,581 loops=1)

  • Sort Key: dependency_cve_1.dependency_id
  • Sort Method: quicksort Memory: 978kB
27. 5.188 10.110 ↑ 1.3 13,581 1

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

  • Hash Cond: (dependency_cve_1.cve_id = cve.id)
28. 2.741 2.741 ↑ 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.040..2.741 rows=13,581 loops=1)

29. 1.176 2.181 ↓ 1.1 4,358 1

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

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 218kB
30. 1.005 1.005 ↓ 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.038..1.005 rows=4,358 loops=1)

  • Heap Fetches: 132
31. 0.000 0.000 ↓ 0.0 0 9,456

Materialize (cost=1.26..3.41 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=9,456)

32. 0.009 0.032 ↓ 0.0 0 1

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

  • Recheck Cond: (project_id = 1)
33. 0.023 0.023 ↓ 0.0 0 1

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

  • Index Cond: (project_id = 1)