explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GN6D : a_prod.sql

Settings
# exclusive inclusive rows x rows loops node
1. 0.387 1,737.046 ↑ 1.2 6 1

HashAggregate (cost=107,322.08..107,322.15 rows=7 width=10) (actual time=1,737.045..1,737.046 rows=6 loops=1)

  • Group Key: vulnerability_occurrences.severity
  • Buffers: shared hit=751351 read=31
  • I/O Timings: read=18.630
2. 0.949 1,736.659 ↑ 11.0 1,617 1

Nested Loop (cost=97,799.05..107,232.92 rows=17,833 width=2) (actual time=1,706.741..1,736.659 rows=1,617 loops=1)

  • Buffers: shared hit=751351 read=31
  • I/O Timings: read=18.630
3. 0.272 1,709.838 ↑ 11.0 1,617 1

Nested Loop (cost=97,798.63..99,019.12 rows=17,833 width=8) (actual time=1,706.711..1,709.838 rows=1,617 loops=1)

  • Buffers: shared hit=744897 read=1
  • I/O Timings: read=0.037
4. 0.023 1,706.710 ↑ 1.8 28 1

HashAggregate (cost=97,798.19..97,798.70 rows=51 width=4) (actual time=1,706.696..1,706.710 rows=28 loops=1)

  • Group Key: max(ci_pipelines.id)
  • Buffers: shared hit=743343 read=1
  • I/O Timings: read=0.037
5. 0.950 1,706.687 ↑ 1.8 28 1

GroupAggregate (cost=97,796.66..97,797.56 rows=51 width=8) (actual time=1,705.968..1,706.687 rows=28 loops=1)

  • Group Key: ci_pipelines.project_id
  • Buffers: shared hit=743343 read=1
  • I/O Timings: read=0.037
6. 2.966 1,705.737 ↓ 141.8 7,232 1

Sort (cost=97,796.66..97,796.79 rows=51 width=8) (actual time=1,705.151..1,705.737 rows=7,232 loops=1)

  • Sort Key: ci_pipelines.project_id
  • Sort Method: quicksort Memory: 532kB
  • Buffers: shared hit=743343 read=1
  • I/O Timings: read=0.037
7. 99.402 1,702.771 ↓ 141.8 7,232 1

Nested Loop Semi Join (cost=1,865.15..97,795.22 rows=51 width=8) (actual time=43.673..1,702.771 rows=7,232 loops=1)

  • Buffers: shared hit=743340 read=1
  • I/O Timings: read=0.037
8. 34.887 1,231.227 ↑ 1.1 186,071 1

Nested Loop (cost=1,864.72..9,054.17 rows=197,197 width=8) (actual time=18.588..1,231.227 rows=186,071 loops=1)

  • Join Filter: (ci_pipelines.project_id = rs.source_id)
  • Buffers: shared hit=184461
9. 0.546 18.836 ↑ 1.8 496 1

HashAggregate (cost=1,864.15..1,873.02 rows=887 width=8) (actual time=18.481..18.836 rows=496 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=2576
10. 0.365 18.290 ↑ 1.8 496 1

Nested Loop (cost=1.00..1,861.93 rows=887 width=8) (actual time=0.260..18.290 rows=496 loops=1)

  • Buffers: shared hit=2576
11. 7.013 7.013 ↑ 1.9 496 1

Index Scan using index_routes_on_path_text_pattern_ops on routes rs (cost=0.56..3.59 rows=924 width=4) (actual time=0.164..7.013 rows=496 loops=1)

  • Index Cond: (((path)::text ~>=~ 'gitlab-org/'::text) AND ((path)::text ~<~ 'gitlab-org0'::text))
  • Filter: (((path)::text ~~ 'gitlab-org/%'::text) AND ((source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 40
  • Buffers: shared hit=538
12. 10.912 10.912 ↑ 1.0 1 496

Index Only Scan using projects_pkey on projects (cost=0.43..2.00 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=496)

  • Index Cond: (id = rs.source_id)
  • Heap Fetches: 120
  • Buffers: shared hit=2038
13. 1,177.504 1,177.504 ↓ 1.7 375 496

Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on ci_pipelines (cost=0.56..5.32 rows=222 width=8) (actual time=0.023..2.374 rows=375 loops=496)

  • Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
  • Buffers: shared hit=181885
14. 372.142 372.142 ↓ 0.0 0 186,071

Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.43..6.56 rows=350 width=4) (actual time=0.002..0.002 rows=0 loops=186,071)

  • Index Cond: (pipeline_id = ci_pipelines.id)
  • Heap Fetches: 235
  • Buffers: shared hit=558879 read=1
  • I/O Timings: read=0.037
15. 2.856 2.856 ↑ 6.0 58 28

Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.43..20.43 rows=350 width=12) (actual time=0.011..0.102 rows=58 loops=28)

  • Index Cond: (pipeline_id = (max(ci_pipelines.id)))
  • Buffers: shared hit=1554
16. 25.872 25.872 ↑ 1.0 1 1,617

Index Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.42..0.45 rows=1 width=10) (actual time=0.016..0.016 rows=1 loops=1,617)

  • Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
  • Buffers: shared hit=6454 read=30
  • I/O Timings: read=18.593