explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wxcp : b_prod.sql

Settings
# exclusive inclusive rows x rows loops node
1. 166.493 7,432.635 ↑ 2.2 488 1

GroupAggregate (cost=98,856.54..98,880.35 rows=1,058 width=14) (actual time=7,198.986..7,432.635 rows=488 loops=1)

  • Group Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
  • Buffers: shared hit=3766441 read=8542
  • I/O Timings: read=2195.682
2. 259.043 7,266.142 ↓ 561.2 593,758 1

Sort (cost=98,856.54..98,859.19 rows=1,058 width=14) (actual time=7,196.642..7,266.142 rows=593,758 loops=1)

  • Sort Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
  • Sort Method: quicksort Memory: 52409kB
  • Buffers: shared hit=3766433 read=8542
  • I/O Timings: read=2195.682
3. 189.903 7,007.099 ↓ 561.2 593,758 1

Nested Loop (cost=97,797.68..98,803.39 rows=1,058 width=14) (actual time=1,754.494..7,007.099 rows=593,758 loops=1)

  • Buffers: shared hit=3766427 read=8542
  • I/O Timings: read=2195.682
4. 92.273 5,035.922 ↓ 561.2 593,758 1

Nested Loop (cost=97,797.26..98,309.64 rows=1,058 width=16) (actual time=1,754.452..5,035.922 rows=593,758 loops=1)

  • Buffers: shared hit=1389304 read=8147
  • I/O Timings: read=2178.707
5. 12.583 1,761.569 ↓ 141.8 7,232 1

HashAggregate (cost=97,796.82..97,797.33 rows=51 width=8) (actual time=1,754.407..1,761.569 rows=7,232 loops=1)

  • Group Key: ci_pipelines.id
  • Buffers: shared hit=743344
6. 102.008 1,748.986 ↓ 141.8 7,232 1

Nested Loop Semi Join (cost=1,866.65..97,796.70 rows=51 width=8) (actual time=47.463..1,748.986 rows=7,232 loops=1)

  • Buffers: shared hit=743344
7. 33.179 1,274.836 ↑ 1.1 186,071 1

Nested Loop (cost=1,866.22..9,055.65 rows=197,197 width=4) (actual time=20.726..1,274.836 rows=186,071 loops=1)

  • Join Filter: (ci_pipelines.project_id = rs.source_id)
  • Buffers: shared hit=184464
8. 0.661 21.001 ↑ 1.8 496 1

HashAggregate (cost=1,865.65..1,874.52 rows=887 width=8) (actual time=20.613..21.001 rows=496 loops=1)

  • Group Key: projects.id
  • Buffers: shared hit=2579
9. 0.107 20.340 ↑ 1.8 496 1

Nested Loop (cost=1.00..1,863.43 rows=887 width=8) (actual time=0.292..20.340 rows=496 loops=1)

  • Buffers: shared hit=2579
10. 7.833 7.833 ↑ 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.191..7.833 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
11. 12.400 12.400 ↑ 1.0 1 496

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

  • Index Cond: (id = rs.source_id)
  • Heap Fetches: 124
  • Buffers: shared hit=2041
12. 1,220.656 1,220.656 ↓ 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.025..2.461 rows=375 loops=496)

  • Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
  • Buffers: shared hit=181885
13. 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=558880
14. 3,182.080 3,182.080 ↑ 3.2 82 7,232

Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.43..7.44 rows=261 width=20) (actual time=0.185..0.440 rows=82 loops=7,232)

  • Index Cond: (pipeline_id = ci_pipelines.id)
  • Filter: (created_at >= '2019-03-06 00:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 31
  • Buffers: shared hit=645960 read=8147
  • I/O Timings: read=2178.707
15. 1,781.274 1,781.274 ↑ 1.0 1 593,758

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

  • Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
  • Buffers: shared hit=2377123 read=395
  • I/O Timings: read=16.975
Planning time : 23.580 ms
Execution time : 7,435.439 ms