explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 57kO

Settings
# exclusive inclusive rows x rows loops node
1. 2.313 181.220 ↑ 1.0 6,980 1

Unique (cost=8,144.94..8,336.89 rows=6,980 width=202) (actual time=178.521..181.220 rows=6,980 loops=1)

2. 14.053 178.907 ↑ 1.0 6,980 1

Sort (cost=8,144.94..8,162.39 rows=6,980 width=202) (actual time=178.519..178.907 rows=6,980 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: 2,012kB
3. 17.855 164.854 ↑ 1.0 6,980 1

GroupAggregate (cost=6,019.56..7,699.30 rows=6,980 width=202) (actual time=102.335..164.854 rows=6,980 loops=1)

  • Group Key: dependency.id
4. 20.230 146.999 ↓ 2.7 72,925 1

Nested Loop Anti Join (cost=6,019.56..7,342.86 rows=26,919 width=174) (actual time=102.271..146.999 rows=72,925 loops=1)

  • Join Filter: (cve_filter_project.cve_id = cve.id)
5. 17.525 126.769 ↓ 2.7 72,925 1

Merge Left Join (cost=6,015.40..6,525.00 rows=26,945 width=174) (actual time=102.262..126.769 rows=72,925 loops=1)

  • Merge Cond: (dependency.id = dependency_cve_1.dependency_id)
6. 24.674 101.416 ↓ 1.4 30,109 1

Sort (cost=5,015.09..5,067.80 rows=21,085 width=170) (actual time=97.384..101.416 rows=30,109 loops=1)

  • Sort Key: dependency.id
  • Sort Method: external merge Disk: 4,320kB
7. 12.419 76.742 ↓ 1.4 30,109 1

Hash Left Join (cost=355.31..1,698.27 rows=21,085 width=170) (actual time=18.758..76.742 rows=30,109 loops=1)

  • Hash Cond: (image.id = imagetag.image_id)
8. 25.158 64.109 ↓ 1.4 29,714 1

Hash Right Join (cost=340.77..1,420.67 rows=21,085 width=174) (actual time=18.518..64.109 rows=29,714 loops=1)

  • Hash Cond: (image_dependency_1.dependency_id = dependency.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (NOT (alternatives: SubPlan 3 or hashed SubPlan 4)))
9. 9.086 12.709 ↑ 1.0 28,041 1

Hash Join (cost=16.73..554.14 rows=28,113 width=8) (actual time=0.323..12.709 rows=28,041 loops=1)

  • Hash Cond: (image_dependency_1.image_id = image.id)
10. 3.347 3.347 ↑ 1.0 28,041 1

Seq Scan on image_dependency image_dependency_1 (cost=0.00..462.13 rows=28,113 width=8) (actual time=0.022..3.347 rows=28,041 loops=1)

11. 0.104 0.276 ↑ 1.0 210 1

Hash (cost=14.10..14.10 rows=210 width=4) (actual time=0.276..0.276 rows=210 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
12. 0.172 0.172 ↑ 1.0 210 1

Seq Scan on image (cost=0.00..14.10 rows=210 width=4) (actual time=0.013..0.172 rows=210 loops=1)

13. 9.520 17.831 ↑ 1.0 6,980 1

Hash (cost=236.80..236.80 rows=6,980 width=170) (actual time=17.831..17.831 rows=6,980 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 1,115kB
14. 8.311 8.311 ↑ 1.0 6,980 1

Seq Scan on dependency (cost=0.00..236.80 rows=6,980 width=170) (actual time=0.039..8.311 rows=6,980 loops=1)

15.          

SubPlan (for Hash Right Join)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..4.71 rows=1 width=0) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on project_image (cost=0.00..3.65 rows=1 width=4) (never executed)

  • Filter: ((image.id = image_id) AND (project_id = 1))
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on project (cost=0.00..1.05 rows=1 width=4) (never executed)

  • Filter: (id = 1)
19. 0.075 0.175 ↓ 1.2 210 1

Nested Loop (cost=0.00..6.03 rows=177 width=4) (actual time=0.026..0.175 rows=210 loops=1)

20. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on project project_1 (cost=0.00..1.05 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: (id = 1)
21. 0.088 0.088 ↓ 1.2 210 1

Seq Scan on project_image project_image_1 (cost=0.00..3.21 rows=177 width=8) (actual time=0.013..0.088 rows=210 loops=1)

  • Filter: (project_id = 1)
22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..562.23 rows=5 width=0) (never executed)

  • Join Filter: (image_dependency.image_id = image_1.id)
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on image image_1 (cost=0.00..14.10 rows=210 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..532.44 rows=5 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on image_dependency (cost=0.00..532.41 rows=5 width=4) (never executed)

  • Filter: (dependency.id = dependency_id)
26. 5.898 8.236 ↑ 1.0 28,041 1

Hash Join (cost=16.73..554.14 rows=28,113 width=4) (actual time=0.091..8.236 rows=28,041 loops=1)

  • Hash Cond: (image_dependency_2.image_id = image_2.id)
27. 2.263 2.263 ↑ 1.0 28,041 1

Seq Scan on image_dependency image_dependency_2 (cost=0.00..462.13 rows=28,113 width=8) (actual time=0.008..2.263 rows=28,041 loops=1)

28. 0.027 0.075 ↑ 1.0 210 1

Hash (cost=14.10..14.10 rows=210 width=4) (actual time=0.075..0.075 rows=210 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
29. 0.048 0.048 ↑ 1.0 210 1

Seq Scan on image image_2 (cost=0.00..14.10 rows=210 width=4) (actual time=0.005..0.048 rows=210 loops=1)

30. 0.099 0.214 ↓ 1.4 212 1

Hash (cost=12.57..12.57 rows=157 width=4) (actual time=0.214..0.214 rows=212 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
31. 0.115 0.115 ↓ 1.4 212 1

Seq Scan on imagetag (cost=0.00..12.57 rows=157 width=4) (actual time=0.011..0.115 rows=212 loops=1)

32. 4.136 7.828 ↓ 5.8 51,408 1

Sort (cost=1,000.31..1,022.61 rows=8,920 width=8) (actual time=4.871..7.828 rows=51,408 loops=1)

  • Sort Key: dependency_cve_1.dependency_id
  • Sort Method: quicksort Memory: 808kB
33. 2.183 3.692 ↓ 1.0 9,043 1

Hash Join (cost=214.37..415.03 rows=8,920 width=8) (actual time=0.728..3.692 rows=9,043 loops=1)

  • Hash Cond: (dependency_cve_1.cve_id = cve.id)
34. 0.798 0.798 ↓ 1.0 9,043 1

Seq Scan on dependency_cve dependency_cve_1 (cost=0.00..177.20 rows=8,920 width=8) (actual time=0.009..0.798 rows=9,043 loops=1)

35. 0.267 0.711 ↑ 1.0 2,061 1

Hash (cost=188.61..188.61 rows=2,061 width=4) (actual time=0.711..0.711 rows=2,061 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 105kB
36. 0.444 0.444 ↑ 1.0 2,061 1

Seq Scan on cve (cost=0.00..188.61 rows=2,061 width=4) (actual time=0.003..0.444 rows=2,061 loops=1)

37. 0.000 0.000 ↓ 0.0 0 72,925

Materialize (cost=4.16..9.51 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=72,925)

38. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on cve_filter_project (cost=4.16..9.50 rows=2 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (project_id = 1)
39. 0.005 0.005 ↓ 0.0 0 1

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

  • Index Cond: (project_id = 1)