explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ViT4

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 103.843 ↓ 5.0 5 1

Sort (cost=18,659.39..18,659.39 rows=1 width=739) (actual time=103.843..103.843 rows=5 loops=1)

  • Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date DESC, merge_requests.id DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=75,141
2. 0.879 103.814 ↓ 5.0 5 1

Aggregate (cost=18,659.35..18,659.38 rows=1 width=739) (actual time=103.102..103.814 rows=5 loops=1)

  • Group Key: merge_requests.id, milestones.id
  • Filter: (count(DISTINCT labels.title) = 2)
  • Rows Removed by Filter: 353
  • Buffers: shared hit=75,141
3. 1.253 102.935 ↓ 363.0 363 1

Sort (cost=18,659.35..18,659.36 rows=1 width=746) (actual time=102.833..102.935 rows=363 loops=1)

  • Sort Key: merge_requests.id DESC, milestones.id
  • Sort Method: quicksort Memory: 503kB
  • Buffers: shared hit=75,141
4. 0.497 101.682 ↓ 363.0 363 1

Nested Loop Left Join (cost=1,420.92..18,659.34 rows=1 width=746) (actual time=2.116..101.682 rows=363 loops=1)

  • Buffers: shared hit=75,141
5. 0.805 100.096 ↓ 363.0 363 1

Nested Loop Left Join (cost=1,420.50..18,658.88 rows=1 width=738) (actual time=2.082..100.096 rows=363 loops=1)

  • Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=74,049
6. 0.026 98.146 ↓ 363.0 363 1

Nested Loop (cost=1,420.06..18,654.78 rows=1 width=742) (actual time=2.063..98.146 rows=363 loops=1)

  • Buffers: shared hit=72,539
7. 7.323 51.712 ↓ 297.5 11,602 1

Nested Loop (cost=1,419.63..18,635.78 rows=39 width=737) (actual time=1.928..51.712 rows=11,602 loops=1)

  • Buffers: shared hit=26,120
8. 1.630 24.109 ↓ 11.1 2,535 1

Nested Loop (cost=1,419.06..18,458.44 rows=228 width=733) (actual time=1.910..24.109 rows=2,535 loops=1)

  • Buffers: shared hit=12,139
9. 0.330 9.752 ↑ 1.7 979 1

Nested Loop (cost=1,418.63..16,399.75 rows=1,633 width=4) (actual time=1.870..9.752 rows=979 loops=1)

  • Buffers: shared hit=6,367
10. 0.170 1.922 ↑ 1.3 125 1

HashAggregate (cost=1,418.20..1,419.81 rows=161 width=4) (actual time=1.830..1.922 rows=125 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=627
11. 1.752 1.752 ↑ 1.3 125 1

CTE Scan on base_and_descendants namespaces (cost=1,412.97..1,416.19 rows=161 width=4) (actual time=0.042..1.752 rows=125 loops=1)

  • Buffers: shared hit=627
12.          

CTE base_and_descendants

13. 0.378 1.538 ↑ 1.3 125 1

Recursive Union (cost=0.43..1,412.97 rows=161 width=329) (actual time=0.038..1.538 rows=125 loops=1)

  • Buffers: shared hit=627
14. 0.030 0.030 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=329) (actual time=0.028..0.030 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
15. 0.110 1.130 ↓ 1.6 25 5

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.021..0.226 rows=25 loops=5)

  • Buffers: shared hit=623
16. 0.020 0.020 ↓ 2.5 25 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.004 rows=25 loops=5)

17. 1.000 1.000 ↑ 2.0 1 125

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.02 rows=2 width=329) (actual time=0.005..0.008 rows=1 loops=125)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=623
18. 3.584 7.500 ↑ 1.2 8 125

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.94 rows=10 width=8) (actual time=0.012..0.060 rows=8 loops=125)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=5,740
19.          

SubPlan (for Index Scan)

20. 3.916 3.916 ↑ 1.0 1 979

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=979)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 113
  • Buffers: shared hit=4,383
21. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.57..230.03 rows=1,014 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 4,156,052) AND (project_authorizations_1.access_level >= 20))
  • Heap Fetches: 0
22. 12.727 12.727 ↑ 3.0 3 979

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..1.17 rows=9 width=729) (actual time=0.005..0.013 rows=3 loops=979)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=5,772
23. 20.280 20.280 ↓ 5.0 5 2,535

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.77 rows=1 width=8) (actual time=0.006..0.008 rows=5 loops=2,535)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
  • Heap Fetches: 285
  • Buffers: shared hit=13,981
24. 46.408 46.408 ↓ 0.0 0 11,602

Index Scan using labels_pkey on public.labels (cost=0.43..0.49 rows=1 width=13) (actual time=0.004..0.004 rows=0 loops=11,602)

  • Index Cond: (labels.id = label_links.label_id)
  • Filter: ((labels.title)::text = ANY ('{database::approved,feature}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=46,419
25. 1.089 1.089 ↑ 1.0 1 363

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=363)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=1,452
26.          

SubPlan (for Nested Loop Left Join)

27. 0.056 0.056 ↑ 1.0 1 14

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: ((project_authorizations_2.user_id = 4,156,052) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 20))
  • Heap Fetches: 0
  • Buffers: shared hit=58
28. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..230.03 rows=1,014 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 20))
  • Heap Fetches: 0
29. 1.089 1.089 ↑ 1.0 1 363

Index Scan using milestones_pkey on public.milestones (cost=0.42..0.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=363)

  • Index Cond: (merge_requests.milestone_id = milestones.id)
  • Buffers: shared hit=1,092