explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UWG

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7.575 ↑ 1.0 1 1

Aggregate (cost=450,612.34..450,612.35 rows=1 width=8) (actual time=7.575..7.575 rows=1 loops=1)

  • Buffers: shared hit=12 read=18
  • I/O Timings: read=6.843
2. 0.000 7.572 ↓ 0.0 0 1

Nested Loop (cost=308,108.43..450,369.78 rows=97,021 width=0) (actual time=7.572..7.572 rows=0 loops=1)

  • Buffers: shared hit=12 read=18
  • I/O Timings: read=6.843
3. 0.012 7.572 ↓ 0.0 0 1

Hash Join (cost=308,107.86..390,397.03 rows=97,021 width=8) (actual time=7.572..7.572 rows=0 loops=1)

  • Hash Cond: (vulnerability_occurrence_pipelines.pipeline_id = "ANY_subquery".id)
  • Buffers: shared hit=12 read=18
  • I/O Timings: read=6.843
4. 0.004 7.527 ↑ 194,041.0 1 1

Nested Loop (cost=1.40..81,554.98 rows=194,041 width=4) (actual time=7.527..7.527 rows=1 loops=1)

  • Buffers: shared hit=8 read=18
  • I/O Timings: read=6.843
5. 0.141 2.616 ↑ 10,045.0 1 1

Merge Anti Join (cost=0.84..21,077.98 rows=10,045 width=8) (actual time=2.616..2.616 rows=1 loops=1)

  • Merge Cond: (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
  • Buffers: shared hit=6 read=15
  • I/O Timings: read=1.961
6. 1.750 1.750 ↑ 11,533.0 1 1

Index Scan using index_vulnerability_occurrences_on_unique_keys on public.vulnerability_occurrences (cost=0.56..20,423.01 rows=11,533 width=35) (actual time=1.750..1.750 rows=1 loops=1)

  • Index Cond: (vulnerability_occurrences.project_id = ANY ('{17450826,15369510,14853550,13922331,13150952,13017938,11625232,11139665,10861561,10846951,10476326,9450197,9450195,9450192,9396716,9358979,7660893,7145793,6535935,6392811,6237091,6237088,6237085,6126012}'::integer[]))
  • Buffers: shared hit=3 read=2
  • I/O Timings: read=1.697
7. 0.228 0.725 ↑ 5.0 972 1

Materialize (cost=0.28..254.22 rows=4,863 width=46) (actual time=0.015..0.725 rows=972 loops=1)

  • Buffers: shared hit=3 read=13
  • I/O Timings: read=0.264
8. 0.497 0.497 ↑ 5.0 972 1

Index Only Scan using vulnerability_feedback_unique_idx on public.vulnerability_feedback (cost=0.28..242.06 rows=4,863 width=46) (actual time=0.010..0.497 rows=972 loops=1)

  • Index Cond: (vulnerability_feedback.feedback_type = 0)
  • Heap Fetches: 0
  • Buffers: shared hit=3 read=13
  • I/O Timings: read=0.264
9. 4.907 4.907 ↑ 127.0 1 1

Index Only Scan using vulnerability_occurrence_pipelines_on_unique_keys on public.vulnerability_occurrence_pipelines (cost=0.56..4.75 rows=127 width=12) (actual time=4.907..4.907 rows=1 loops=1)

  • Index Cond: (vulnerability_occurrence_pipelines.occurrence_id = vulnerability_occurrences.id)
  • Heap Fetches: 0
  • Buffers: shared hit=2 read=3
  • I/O Timings: read=4.882
10. 0.000 0.033 ↓ 0.0 0 1

Hash (cost=308,103.96..308,103.96 rows=200 width=4) (actual time=0.033..0.033 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=4
11. 0.002 0.033 ↓ 0.0 0 1

HashAggregate (cost=308,101.96..308,103.96 rows=200 width=4) (actual time=0.033..0.033 rows=0 loops=1)

  • Group Key: "ANY_subquery".id
  • Buffers: shared hit=4
12. 0.000 0.031 ↓ 0.0 0 1

Subquery Scan on ANY_subquery (cost=308,095.96..308,101.46 rows=200 width=4) (actual time=0.031..0.031 rows=0 loops=1)

  • Buffers: shared hit=4
13. 0.002 0.032 ↓ 0.0 0 1

Aggregate (cost=308,095.96..308,099.46 rows=200 width=8) (actual time=0.031..0.032 rows=0 loops=1)

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared hit=4
14. 0.007 0.030 ↓ 0.0 0 1

Sort (cost=308,095.96..308,096.46 rows=200 width=8) (actual time=0.030..0.030 rows=0 loops=1)

  • Sort Key: ci_pipelines_1.project_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
15. 0.000 0.023 ↓ 0.0 0 1

Nested Loop Semi Join (cost=3,127.85..308,088.31 rows=200 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Buffers: shared hit=4
16. 0.000 0.023 ↓ 0.0 0 1

Nested Loop (cost=3,127.28..20,632.32 rows=493,470 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Buffers: shared hit=4
17. 0.004 0.023 ↓ 0.0 0 1

HashAggregate (cost=3,126.72..3,138.99 rows=1,227 width=8) (actual time=0.023..0.023 rows=0 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=4
18. 0.000 0.019 ↓ 0.0 0 1

Nested Loop (cost=1.00..3,123.65 rows=1,227 width=8) (actual time=0.019..0.019 rows=0 loops=1)

  • Buffers: shared hit=4
19. 0.019 0.019 ↓ 0.0 0 1

Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..4.59 rows=1,265 width=4) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (((rs.path)::text ~>=~ 'security-products/analyzers/'::text) AND ((rs.path)::text ~<~ 'security-products/analyzers0'::text))
  • Filter: (((rs.path)::text ~~ 'security-products/analyzers/%'::text) AND ((rs.source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
20. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using projects_pkey on public.projects (cost=0.43..2.46 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects.id = rs.source_id)
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on public.ci_pipelines ci_pipelines_1 (cost=0.57..9.23 rows=402 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((ci_pipelines_1.project_id = projects.id) AND ((ci_pipelines_1.status)::text = 'success'::text))
22. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on public.vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.56..18.05 rows=999 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (vulnerability_occurrence_pipelines_1.pipeline_id = ci_pipelines_1.id)
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using ci_pipelines_pkey on public.ci_pipelines (cost=0.57..0.61 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (ci_pipelines.id = vulnerability_occurrence_pipelines.pipeline_id)
  • Heap Fetches: 0