explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PPNB

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 16,948.506 ↑ 1.0 1 1

Aggregate (cost=9,178,776.39..9,178,776.40 rows=1 width=8) (actual time=16,948.506..16,948.506 rows=1 loops=1)

  • Buffers: shared hit=1861956 read=577704
  • I/O Timings: read=9487.391
2. 812.272 16,948.499 ↓ 0.0 0 1

Hash Join (cost=2,377,208.75..9,125,584.47 rows=21,276,769 width=0) (actual time=16,948.499..16,948.499 rows=0 loops=1)

  • Hash Cond: (vulnerability_occurrence_pipelines.occurrence_id = vulnerability_occurrences.id)
  • Buffers: shared hit=1861956 read=577704
  • I/O Timings: read=9487.391
3. 0.000 0.832 ↓ 0.0 0 1

Merge Join (cost=1,254,148.55..6,869,775.04 rows=24,429,104 width=8) (actual time=0.832..0.832 rows=0 loops=1)

  • Merge Cond: (vulnerability_occurrence_pipelines.pipeline_id = ci_pipelines.id)
  • Buffers: shared read=9
  • I/O Timings: read=0.749
4. 0.002 0.832 ↓ 0.0 0 1

Merge Join (cost=308,112.17..3,151,369.67 rows=24,429,104 width=16) (actual time=0.832..0.832 rows=0 loops=1)

  • Merge Cond: (vulnerability_occurrence_pipelines.pipeline_id = "ANY_subquery".id)
  • Buffers: shared read=9
  • I/O Timings: read=0.749
5. 0.371 0.371 ↑ 48,858,208.0 1 1

Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on public.vulnerability_occurrence_pipelines (cost=0.56..2,718,114.14 rows=48,858,208 width=12) (actual time=0.370..0.371 rows=1 loops=1)

  • Buffers: shared read=5
  • I/O Timings: read=0.350
6. 0.020 0.459 ↓ 0.0 0 1

Sort (cost=308,111.60..308,112.10 rows=200 width=4) (actual time=0.459..0.459 rows=0 loops=1)

  • Sort Key: "ANY_subquery".id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=4
  • I/O Timings: read=0.399
7. 0.002 0.439 ↓ 0.0 0 1

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

  • Group Key: "ANY_subquery".id
  • Buffers: shared read=4
  • I/O Timings: read=0.399
8. 0.002 0.437 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=0.399
9. 0.001 0.435 ↓ 0.0 0 1

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

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared read=4
  • I/O Timings: read=0.399
10. 0.005 0.434 ↓ 0.0 0 1

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

  • Sort Key: ci_pipelines_1.project_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=4
  • I/O Timings: read=0.399
11. 0.002 0.429 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=0.399
12. 0.000 0.427 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=0.399
13. 0.005 0.427 ↓ 0.0 0 1

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

  • Group Key: projects.id
  • Buffers: shared read=4
  • I/O Timings: read=0.399
14. 0.000 0.422 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=0.399
15. 0.422 0.422 ↓ 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.421..0.422 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 read=4
  • I/O Timings: read=0.399
16. 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
17. 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))
18. 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
19. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using ci_pipelines_pkey on public.ci_pipelines (cost=0.57..3,117,901.32 rows=120,378,535 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Heap Fetches: 0
20. 944.989 16,135.395 ↓ 1.1 2,524,958 1

Hash (cost=1,061,104.74..1,061,104.74 rows=2,202,837 width=8) (actual time=16,135.395..16,135.395 rows=2,524,958 loops=1)

  • Buckets: 524288 Batches: 16 Memory Usage: 12289kB
  • Buffers: shared hit=1861956 read=577695
  • I/O Timings: read=9486.642
21. 2,136.292 15,190.406 ↓ 1.1 2,524,958 1

Merge Anti Join (cost=0.84..1,061,104.74 rows=2,202,837 width=8) (actual time=0.029..15,190.406 rows=2,524,958 loops=1)

  • Merge Cond: (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
  • Buffers: shared hit=1861956 read=577695
  • I/O Timings: read=9486.642
22. 12,842.030 12,842.030 ↓ 1.0 2,531,143 1

Index Scan using index_vulnerability_occurrences_on_unique_keys on public.vulnerability_occurrences (cost=0.56..970,313.75 rows=2,529,206 width=35) (actual time=0.011..12,842.030 rows=2,531,143 loops=1)

  • Buffers: shared hit=1861953 read=577623
  • I/O Timings: read=9484.695
23. 208.052 212.084 ↓ 652.7 3,173,839 1

Materialize (cost=0.28..254.22 rows=4,863 width=46) (actual time=0.014..212.084 rows=3,173,839 loops=1)

  • Buffers: shared hit=3 read=72
  • I/O Timings: read=1.947
24. 4.032 4.032 ↑ 1.0 4,765 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.011..4.032 rows=4,765 loops=1)

  • Index Cond: (vulnerability_feedback.feedback_type = 0)
  • Heap Fetches: 0
  • Buffers: shared hit=3 read=72
  • I/O Timings: read=1.947