explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KjmB : c_prod.sql

Settings
# exclusive inclusive rows x rows loops node
1. 0.136 1,677.348 ↑ 1.0 1 1

Aggregate (cost=97,488.54..97,488.55 rows=1 width=8) (actual time=1,677.348..1,677.348 rows=1 loops=1)

  • Buffers: shared hit=750095 read=14
  • I/O Timings: read=0.996
2. 0.153 1,677.212 ↓ 323.4 1,617 1

Limit (cost=96,878.06..97,488.48 rows=5 width=4) (actual time=1,669.075..1,677.212 rows=1,617 loops=1)

  • Buffers: shared hit=750095 read=14
  • I/O Timings: read=0.996
3. 0.586 1,677.059 ↓ 323.4 1,617 1

Nested Loop (cost=96,878.06..97,488.48 rows=5 width=4) (actual time=1,669.073..1,677.059 rows=1,617 loops=1)

  • Buffers: shared hit=750095 read=14
  • I/O Timings: read=0.996
4. 0.270 1,673.239 ↓ 323.4 1,617 1

Nested Loop (cost=96,877.64..97,486.22 rows=5 width=8) (actual time=1,669.039..1,673.239 rows=1,617 loops=1)

  • Buffers: shared hit=744952 read=13
  • I/O Timings: read=0.975
5. 0.025 1,670.589 ↑ 1.8 28 1

Nested Loop (cost=96,877.20..96,983.26 rows=50 width=8) (actual time=1,669.025..1,670.589 rows=28 loops=1)

  • Buffers: shared hit=743398 read=13
  • I/O Timings: read=0.975
6. 0.019 1,668.968 ↑ 1.8 28 1

HashAggregate (cost=96,876.64..96,877.14 rows=50 width=4) (actual time=1,668.959..1,668.968 rows=28 loops=1)

  • Group Key: max(ci_pipelines_1.id)
  • Buffers: shared hit=743268
7. 0.962 1,668.949 ↑ 1.8 28 1

GroupAggregate (cost=96,875.14..96,876.01 rows=50 width=8) (actual time=1,668.224..1,668.949 rows=28 loops=1)

  • Group Key: ci_pipelines_1.project_id
  • Buffers: shared hit=743268
8. 2.544 1,667.987 ↓ 144.6 7,232 1

Sort (cost=96,875.14..96,875.26 rows=50 width=8) (actual time=1,667.432..1,667.987 rows=7,232 loops=1)

  • Sort Key: ci_pipelines_1.project_id
  • Sort Method: quicksort Memory: 532kB
  • Buffers: shared hit=743268
9. 86.843 1,665.443 ↓ 144.6 7,232 1

Nested Loop Semi Join (cost=1,806.63..96,873.73 rows=50 width=8) (actual time=44.647..1,665.443 rows=7,232 loops=1)

  • Buffers: shared hit=743265
10. 33.597 1,206.448 ↑ 1.1 186,076 1

Nested Loop (cost=1,806.20..8,933.28 rows=195,418 width=8) (actual time=17.934..1,206.448 rows=186,076 loops=1)

  • Join Filter: (ci_pipelines_1.project_id = rs.source_id)
  • Buffers: shared hit=184370
11. 0.530 18.163 ↑ 1.8 496 1

HashAggregate (cost=1,805.63..1,814.42 rows=879 width=8) (actual time=17.828..18.163 rows=496 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=2479
12. 0.124 17.633 ↑ 1.8 496 1

Nested Loop (cost=1.00..1,803.44 rows=879 width=8) (actual time=0.297..17.633 rows=496 loops=1)

  • Buffers: shared hit=2479
13. 7.589 7.589 ↑ 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.192..7.589 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
14. 9.920 9.920 ↑ 1.0 1 496

Index Only Scan using projects_pkey on projects (cost=0.43..1.94 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=496)

  • Index Cond: (id = rs.source_id)
  • Heap Fetches: 32
  • Buffers: shared hit=1941
15. 1,154.688 1,154.688 ↓ 1.7 375 496

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

  • Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
  • Buffers: shared hit=181891
16. 372.152 372.152 ↓ 0.0 0 186,076

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,076)

  • Index Cond: (pipeline_id = ci_pipelines_1.id)
  • Heap Fetches: 235
  • Buffers: shared hit=558895
17. 1.596 1.596 ↑ 1.0 1 28

Index Only Scan using ci_pipelines_pkey on ci_pipelines (cost=0.56..2.11 rows=1 width=4) (actual time=0.056..0.057 rows=1 loops=28)

  • Index Cond: (id = (max(ci_pipelines_1.id)))
  • Heap Fetches: 5
  • Buffers: shared hit=130 read=13
  • I/O Timings: read=0.975
18. 2.380 2.380 ↑ 6.0 58 28

Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.43..6.56 rows=350 width=12) (actual time=0.010..0.085 rows=58 loops=28)

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

Index Only Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.42..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,617)

  • Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
  • Heap Fetches: 246
  • Buffers: shared hit=5143 read=1
  • I/O Timings: read=0.021
Planning time : 23.354 ms
Execution time : 1,677.638 ms