explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rpke

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 43,879.487 ↓ 0.0 0 1

Limit (cost=19,276,568.03..19,276,568.08 rows=20 width=1,131) (actual time=43,879.487..43,879.487 rows=0 loops=1)

  • Buffers: shared hit=1861708 read=577958 written=468
  • I/O Timings: read=11204.247 write=11.468
2. 0.074 43,879.485 ↓ 0.0 0 1

Sort (cost=19,276,568.03..19,329,759.96 rows=21,276,769 width=1,131) (actual time=43,879.485..43,879.485 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=1861708 read=577958 written=468
  • I/O Timings: read=11204.247 write=11.468
3. 14,212.347 43,879.411 ↓ 0.0 0 1

Hash Join (cost=5,705,853.57..18,710,400.86 rows=21,276,769 width=1,131) (actual time=43,879.411..43,879.411 rows=0 loops=1)

  • Hash Cond: (vulnerability_occurrence_pipelines.occurrence_id = vulnerability_occurrences.id)
  • Buffers: shared hit=1861702 read=577958 written=468
  • I/O Timings: read=11204.247 write=11.468
4. 1.605 7.377 ↓ 0.0 0 1

Hash Join (cost=3,412,533.37..12,968,959.44 rows=24,429,104 width=49) (actual time=7.377..7.377 rows=0 loops=1)

  • Hash Cond: (vulnerability_occurrence_pipelines.pipeline_id = ci_pipelines.id)
  • Buffers: shared read=9
  • I/O Timings: read=5.621
5. 4.352 4.352 ↑ 48,858,208.0 1 1

Index Only Scan using vulnerability_occurrence_pipelines_on_unique_keys on public.vulnerability_occurrence_pipelines (cost=0.56..1,265,952.47 rows=48,858,208 width=12) (actual time=4.352..4.352 rows=1 loops=1)

  • Heap Fetches: 0
  • Buffers: shared read=5
  • I/O Timings: read=4.295
6. 0.000 1.420 ↓ 0.0 0 1

Hash (cost=309,022.96..309,022.96 rows=60,189,268 width=49) (actual time=1.420..1.420 rows=0 loops=1)

  • Buckets: 262144 Batches: 512 Memory Usage: 2100kB
  • Buffers: shared read=4
  • I/O Timings: read=1.326
7. 0.001 1.420 ↓ 0.0 0 1

Nested Loop (cost=308,102.52..309,022.96 rows=60,189,268 width=49) (actual time=1.420..1.420 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.326
8. 0.005 1.419 ↓ 0.0 0 1

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

  • Group Key: max(ci_pipelines_1.id)
  • Buffers: shared read=4
  • I/O Timings: read=1.326
9. 0.001 1.414 ↓ 0.0 0 1

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

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared read=4
  • I/O Timings: read=1.326
10. 0.014 1.413 ↓ 0.0 0 1

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

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

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

  • Buffers: shared read=4
  • I/O Timings: read=1.326
12. 0.001 1.363 ↓ 0.0 0 1

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

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

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

  • Group Key: projects.id
  • Buffers: shared read=4
  • I/O Timings: read=1.326
14. 0.002 1.357 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=1.326
15. 1.355 1.355 ↓ 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=1.355..1.355 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=1.326
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 Scan using ci_pipelines_pkey on public.ci_pipelines (cost=0.57..4.58 rows=1 width=45) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (ci_pipelines.id = (max(ci_pipelines_1.id)))
20. 11,037.145 29,659.687 ↓ 1.1 2,524,958 1

Hash (cost=1,061,104.74..1,061,104.74 rows=2,202,837 width=1,090) (actual time=29,659.687..29,659.687 rows=2,524,958 loops=1)

  • Buckets: 16384 Batches: 256 Memory Usage: 11037kB
  • Buffers: shared hit=1861702 read=577949 written=468
  • I/O Timings: read=11198.626 write=11.468
21. 3,060.820 18,622.542 ↓ 1.1 2,524,958 1

Merge Anti Join (cost=0.84..1,061,104.74 rows=2,202,837 width=1,090) (actual time=0.026..18,622.542 rows=2,524,958 loops=1)

  • Merge Cond: (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
  • Buffers: shared hit=1861702 read=577949 written=468
  • I/O Timings: read=11198.626 write=11.468
22. 15,335.676 15,335.676 ↓ 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=1,090) (actual time=0.006..15,335.676 rows=2,531,143 loops=1)

  • Buffers: shared hit=1861699 read=577877 written=468
  • I/O Timings: read=11196.373 write=11.468
23. 221.299 226.046 ↓ 652.7 3,173,839 1

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

  • Buffers: shared hit=3 read=72
  • I/O Timings: read=2.253
24. 4.747 4.747 ↑ 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.747 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=2.253