explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r0UP

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 90.288 ↓ 5.0 5 1

Sort (cost=18,658.92..18,658.92 rows=1 width=729) (actual time=90.287..90.288 rows=5 loops=1)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=74,049
2. 0.676 90.264 ↓ 5.0 5 1

Aggregate (cost=18,658.89..18,658.91 rows=1 width=729) (actual time=89.692..90.264 rows=5 loops=1)

  • Group Key: merge_requests.id
  • Filter: (count(DISTINCT labels.title) = 2)
  • Rows Removed by Filter: 353
  • Buffers: shared hit=74,049
3. 1.317 89.588 ↓ 363.0 363 1

Sort (cost=18,658.89..18,658.89 rows=1 width=738) (actual time=89.513..89.588 rows=363 loops=1)

  • Sort Key: merge_requests.id DESC
  • Sort Method: quicksort Memory: 502kB
  • Buffers: shared hit=74,049
4. 0.533 88.271 ↓ 363.0 363 1

Nested Loop Left Join (cost=1,420.50..18,658.88 rows=1 width=738) (actual time=1.929..88.271 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
5. 4.933 86.593 ↓ 363.0 363 1

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

  • Buffers: shared hit=72,539
6. 7.703 46.854 ↓ 297.5 11,602 1

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

  • Buffers: shared hit=26,120
7. 2.206 21.406 ↓ 11.1 2,535 1

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

  • Buffers: shared hit=12,139
8. 0.244 8.431 ↑ 1.7 979 1

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

  • Buffers: shared hit=6,367
9. 0.126 1.687 ↑ 1.3 125 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=627
10. 1.561 1.561 ↑ 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.037..1.561 rows=125 loops=1)

  • Buffers: shared hit=627
11.          

CTE base_and_descendants

12. 0.364 1.327 ↑ 1.3 125 1

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

  • Buffers: shared hit=627
13. 0.028 0.028 ↑ 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.027..0.028 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
14. 0.170 0.935 ↓ 1.6 25 5

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

  • Buffers: shared hit=623
15. 0.015 0.015 ↓ 2.5 25 5

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

16. 0.750 0.750 ↑ 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.004..0.006 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
17. 3.563 6.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.011..0.052 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
18.          

SubPlan (for Index Scan)

19. 2.937 2.937 ↑ 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.003..0.003 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
20. 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
21. 10.769 10.769 ↑ 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.004..0.011 rows=3 loops=979)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=5,772
22. 17.745 17.745 ↓ 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.007 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
23. 34.806 34.806 ↓ 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.003..0.003 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
24. 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
25.          

SubPlan (for Nested Loop Left Join)

26. 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
27. 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