explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jalx

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.647 ↓ 0.0 0 1

Limit (cost=465,607.58..465,607.63 rows=20 width=1,131) (actual time=0.647..0.647 rows=0 loops=1)

  • Buffers: shared hit=30
2. 0.006 0.645 ↓ 0.0 0 1

Sort (cost=465,607.58..465,850.14 rows=97,021 width=1,131) (actual time=0.645..0.645 rows=0 loops=1)

  • Sort Key: vulnerability_occurrences.severity DESC, vulnerability_occurrences.confidence DESC, vulnerability_occurrences.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=30
3. 0.000 0.639 ↓ 0.0 0 1

Nested Loop (cost=308,108.43..463,025.89 rows=97,021 width=1,131) (actual time=0.639..0.639 rows=0 loops=1)

  • Buffers: shared hit=30
4. 0.008 0.639 ↓ 0.0 0 1

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

  • Hash Cond: (vulnerability_occurrence_pipelines.pipeline_id = "ANY_subquery".id)
  • Buffers: shared hit=30
5. 0.001 0.605 ↑ 194,041.0 1 1

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

  • Buffers: shared hit=26
6. 0.124 0.582 ↑ 10,045.0 1 1

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

  • Merge Cond: (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
  • Buffers: shared hit=21
7. 0.015 0.015 ↑ 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=1,090) (actual time=0.015..0.015 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=5
8. 0.232 0.443 ↑ 5.0 972 1

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

  • Buffers: shared hit=16
9. 0.211 0.211 ↑ 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.009..0.211 rows=972 loops=1)

  • Index Cond: (vulnerability_feedback.feedback_type = 0)
  • Heap Fetches: 0
  • Buffers: shared hit=16
10. 0.022 0.022 ↑ 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=0.022..0.022 rows=1 loops=1)

  • Index Cond: (vulnerability_occurrence_pipelines.occurrence_id = vulnerability_occurrences.id)
  • Heap Fetches: 0
  • Buffers: shared hit=5
11. 0.000 0.026 ↓ 0.0 0 1

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

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

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

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

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

  • Buffers: shared hit=4
14. 0.001 0.024 ↓ 0.0 0 1

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

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared hit=4
15. 0.003 0.023 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=4
17. 0.001 0.020 ↓ 0.0 0 1

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

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

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

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

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

  • Buffers: shared hit=4
20. 0.016 0.016 ↓ 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.016..0.016 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
21. 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
22. 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))
23. 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
24. 0.000 0.000 ↓ 0.0 0 0

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

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