explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W0ALB

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 25.911 ↓ 3.0 3 1

Aggregate (cost=1,597.31..1,597.33 rows=1 width=10) (actual time=25.808..25.911 rows=3 loops=1)

  • Group Key: merge_requests.state_id
  • Buffers: shared hit=19,223
2. 0.198 25.833 ↓ 371.0 371 1

Sort (cost=1,597.31..1,597.31 rows=1 width=2) (actual time=25.799..25.833 rows=371 loops=1)

  • Sort Key: merge_requests.state_id
  • Sort Method: quicksort Memory: 42kB
  • Buffers: shared hit=19,223
3. 0.309 25.635 ↓ 371.0 371 1

Nested Loop Semi Join (cost=1,470.72..1,597.30 rows=1 width=2) (actual time=2.413..25.635 rows=371 loops=1)

  • Buffers: shared hit=19,223
4. 0.243 25.326 ↓ 371.0 371 1

Nested Loop Left Join (cost=57.76..177.49 rows=1 width=6) (actual time=2.386..25.326 rows=371 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=19,219
5. 0.176 24.341 ↓ 371.0 371 1

Nested Loop (cost=57.32..173.39 rows=1 width=10) (actual time=2.369..24.341 rows=371 loops=1)

  • Buffers: shared hit=17,735
6. 0.948 22.310 ↓ 371.0 371 1

Nested Loop Semi Join (cost=56.89..169.32 rows=1 width=6) (actual time=2.322..22.310 rows=371 loops=1)

  • Buffers: shared hit=14,722
7. 0.986 9.458 ↓ 36.7 992 1

Nested Loop (cost=56.32..152.76 rows=27 width=14) (actual time=2.237..9.458 rows=992 loops=1)

  • Buffers: shared hit=5,959
8. 0.749 2.520 ↓ 36.7 992 1

HashAggregate (cost=55.76..56.03 rows=27 width=4) (actual time=2.219..2.520 rows=992 loops=1)

  • Group Key: label_links_1.target_id
  • Buffers: shared hit=999
9. 1.771 1.771 ↓ 36.7 992 1

Index Scan using index_label_links_on_label_id on public.label_links label_links_1 (cost=0.56..55.69 rows=27 width=4) (actual time=0.034..1.771 rows=992 loops=1)

  • Index Cond: (label_links_1.label_id = 11,108,306)
  • Filter: ((label_links_1.target_type)::text = 'MergeRequest'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=999
10. 5.952 5.952 ↑ 1.0 1 992

Index Scan using merge_requests_pkey on public.merge_requests (cost=0.56..3.58 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=992)

  • Index Cond: (merge_requests.id = label_links_1.target_id)
  • Buffers: shared hit=4,960
11. 11.904 11.904 ↓ 0.0 0 992

Index Scan using index_label_links_on_target_id_and_target_type on public.label_links (cost=0.56..0.61 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=992)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND ((label_links.target_type)::text = 'MergeRequest'::text))
  • Filter: (label_links.label_id = ANY ('{3519305,10230929,7402316}'::integer[]))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=8,763
12. 1.113 1.855 ↑ 1.0 1 371

Index Scan using projects_pkey on public.projects (cost=0.43..4.07 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=371)

  • Index Cond: (projects.id = merge_requests.target_project_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=3,013
13.          

SubPlan (for Index Scan)

14. 0.742 0.742 ↑ 1.0 1 371

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.002..0.002 rows=1 loops=371)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 0
  • Buffers: shared hit=1,529
15. 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
16. 0.742 0.742 ↑ 1.0 1 371

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.002..0.002 rows=1 loops=371)

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

SubPlan (for Nested Loop Left Join)

18. 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_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • 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
19. 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
20. 0.000 0.000 ↑ 161.0 1 371

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

  • Buffers: shared hit=4
21.          

CTE base_and_descendants

22. 0.006 0.022 ↑ 161.0 1 1

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

  • Buffers: shared hit=4
23. 0.016 0.016 ↑ 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.016..0.016 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
24. 0.000 0.000 ↓ 0.0 0 0

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.000..0.000 rows=0 loops=0)

25. 0.000 0.000 ↓ 0.0 0 0

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

26. 0.000 0.000 ↓ 0.0 0 0

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.000..0.000 rows=0 loops=0)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0