explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GpPa : b.sql

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 0.225 ↑ 15.5 10 1

GroupAggregate (cost=75.37..78.85 rows=155 width=14) (actual time=0.205..0.225 rows=10 loops=1)

  • Group Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
2. 0.022 0.197 ↑ 1.8 84 1

Sort (cost=75.37..75.75 rows=155 width=14) (actual time=0.195..0.197 rows=84 loops=1)

  • Sort Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
  • Sort Method: quicksort Memory: 28kB
3. 0.001 0.175 ↑ 1.8 84 1

Nested Loop (cost=8.46..69.73 rows=155 width=14) (actual time=0.066..0.175 rows=84 loops=1)

4. 0.011 0.090 ↑ 1.8 84 1

Nested Loop (cost=8.18..9.55 rows=155 width=16) (actual time=0.064..0.090 rows=84 loops=1)

5. 0.003 0.063 ↓ 4.0 4 1

HashAggregate (cost=7.91..7.92 rows=1 width=8) (actual time=0.062..0.063 rows=4 loops=1)

  • Group Key: ci_pipelines.id
6. 0.004 0.060 ↓ 4.0 4 1

Nested Loop Semi Join (cost=5.77..7.91 rows=1 width=8) (actual time=0.052..0.060 rows=4 loops=1)

7. 0.003 0.051 ↑ 1.2 5 1

Nested Loop (cost=5.50..6.08 rows=6 width=4) (actual time=0.047..0.051 rows=5 loops=1)

  • Join Filter: (ci_pipelines.project_id = rs.source_id)
8. 0.002 0.040 ↓ 4.0 8 1

Unique (cost=5.35..5.36 rows=2 width=8) (actual time=0.038..0.040 rows=8 loops=1)

9. 0.008 0.038 ↓ 4.0 8 1

Sort (cost=5.35..5.36 rows=2 width=8) (actual time=0.038..0.038 rows=8 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 25kB
10. 0.003 0.030 ↓ 4.0 8 1

Merge Join (cost=0.28..5.34 rows=2 width=8) (actual time=0.009..0.030 rows=8 loops=1)

  • Merge Cond: (projects.id = rs.source_id)
11. 0.015 0.015 ↓ 1.1 46 1

Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..2.77 rows=42 width=4) (actual time=0.003..0.015 rows=46 loops=1)

  • Heap Fetches: 46
12. 0.012 0.012 ↓ 2.7 8 1

Index Scan using index_routes_on_source_type_and_source_id on routes rs (cost=0.14..3.60 rows=3 width=4) (actual time=0.005..0.012 rows=8 loops=1)

  • Index Cond: ((source_type)::text = 'Project'::text)
  • Filter: ((path)::text ~~ 'gitlab-org/%'::text)
  • Rows Removed by Filter: 38
13. 0.008 0.008 ↑ 3.0 1 8

Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on ci_pipelines (cost=0.14..0.32 rows=3 width=8) (actual time=0.001..0.001 rows=1 loops=8)

  • Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
14. 0.005 0.005 ↑ 41.0 1 5

Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.28..1.18 rows=41 width=4) (actual time=0.001..0.001 rows=1 loops=5)

  • Index Cond: (pipeline_id = ci_pipelines.id)
  • Heap Fetches: 4
15. 0.016 0.016 ↑ 1.7 21 4

Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.28..1.28 rows=35 width=20) (actual time=0.001..0.004 rows=21 loops=4)

  • Index Cond: (pipeline_id = ci_pipelines.id)
  • Filter: (created_at >= '2019-03-06 00:00:00-06'::timestamp with time zone)
16. 0.084 0.084 ↑ 1.0 1 84

Index Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.28..0.38 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=84)

  • Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)