explain.depesz.com

PostgreSQL's explain analyze made readable

Result: flVS

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 336,456.310 ↓ 0.0 0 1

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

  • Buffers: shared hit=1861882 read=577778
  • I/O Timings: read=293373.801
2. 0.051 336,456.308 ↓ 0.0 0 1

Sort (cost=19,276,568.03..19,329,759.96 rows=21,276,769 width=1,131) (actual time=336,456.308..336,456.308 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=1861882 read=577778
  • I/O Timings: read=293373.801
3. 16,431.456 336,456.257 ↓ 0.0 0 1

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

  • Hash Cond: (vulnerability_occurrence_pipelines.occurrence_id = vulnerability_occurrences.id)
  • Buffers: shared hit=1861882 read=577778
  • I/O Timings: read=293373.801
4. 1.783 21.362 ↓ 0.0 0 1

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

  • Hash Cond: (vulnerability_occurrence_pipelines.pipeline_id = ci_pipelines.id)
  • Buffers: shared read=9
  • I/O Timings: read=19.305
5. 8.523 8.523 ↑ 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=8.522..8.523 rows=1 loops=1)

  • Heap Fetches: 0
  • Buffers: shared read=5
  • I/O Timings: read=8.469
6. 0.002 11.056 ↓ 0.0 0 1

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

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

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

  • Buffers: shared read=4
  • I/O Timings: read=10.836
8. 0.036 11.052 ↓ 0.0 0 1

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

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

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

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared read=4
  • I/O Timings: read=10.836
10. 0.102 11.013 ↓ 0.0 0 1

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

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

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

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

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

  • Buffers: shared read=4
  • I/O Timings: read=10.836
13. 0.010 10.909 ↓ 0.0 0 1

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

  • Group Key: projects.id
  • Buffers: shared read=4
  • I/O Timings: read=10.836
14. 0.001 10.899 ↓ 0.0 0 1

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

  • Buffers: shared read=4
  • I/O Timings: read=10.836
15. 10.898 10.898 ↓ 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=10.898..10.898 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=10.836
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. 15,074.544 320,003.439 ↓ 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=320,003.439..320,003.439 rows=2,524,958 loops=1)

  • Buckets: 16384 Batches: 256 Memory Usage: 11037kB
  • Buffers: shared hit=1861882 read=577769
  • I/O Timings: read=293354.496
21. 4,036.981 304,928.895 ↓ 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.034..304,928.895 rows=2,524,958 loops=1)

  • Merge Cond: (vulnerability_occurrences.project_id = vulnerability_feedback.project_id)
  • Buffers: shared hit=1861882 read=577769
  • I/O Timings: read=293354.496
22. 300,577.084 300,577.084 ↓ 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.008..300,577.084 rows=2,531,143 loops=1)

  • Buffers: shared hit=1861866 read=577710
  • I/O Timings: read=293289.739
23. 246.440 314.830 ↓ 652.7 3,173,839 1

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

  • Buffers: shared hit=16 read=59
  • I/O Timings: read=64.757
24. 68.390 68.390 ↑ 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.021..68.390 rows=4,765 loops=1)

  • Index Cond: (vulnerability_feedback.feedback_type = 0)
  • Heap Fetches: 0
  • Buffers: shared hit=16 read=59
  • I/O Timings: read=64.757