explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y4O2

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 11.531 ↓ 5.0 5 1

Sort (cost=1,582.21..1,582.22 rows=1 width=729) (actual time=11.530..11.531 rows=5 loops=1)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=6,311
2. 0.018 11.505 ↓ 5.0 5 1

Nested Loop Semi Join (cost=1,470.72..1,582.20 rows=1 width=729) (actual time=4.387..11.505 rows=5 loops=1)

  • Buffers: shared hit=6,311
3. 0.033 11.279 ↓ 16.0 16 1

Nested Loop Semi Join (cost=1,470.16..1,581.23 rows=1 width=733) (actual time=2.906..11.279 rows=16 loops=1)

  • Buffers: shared hit=6,202
4. 0.053 10.942 ↓ 16.0 16 1

Nested Loop Left Join (cost=57.19..161.42 rows=1 width=737) (actual time=2.613..10.942 rows=16 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=6,172
5. 0.041 10.777 ↓ 16.0 16 1

Nested Loop (cost=56.76..157.32 rows=1 width=741) (actual time=2.563..10.777 rows=16 loops=1)

  • Buffers: shared hit=6,099
6. 0.851 10.528 ↓ 16.0 16 1

Nested Loop (cost=56.32..152.82 rows=1 width=733) (actual time=2.517..10.528 rows=16 loops=1)

  • Buffers: shared hit=5,967
7. 0.812 2.733 ↓ 36.7 992 1

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

  • Group Key: label_links_1.target_id
  • Buffers: shared hit=999
8. 1.921 1.921 ↓ 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.921 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
9. 6.944 6.944 ↓ 0.0 0 992

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

  • Index Cond: (merge_requests.id = label_links_1.target_id)
  • Filter: (merge_requests.state_id = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4,968
10. 0.112 0.208 ↑ 1.0 1 16

Index Scan using projects_pkey on public.projects (cost=0.43..4.49 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=16)

  • 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=132
11.          

SubPlan (for Index Scan)

12. 0.096 0.096 ↑ 1.0 1 16

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.006..0.006 rows=1 loops=16)

  • 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=68
13. 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
14. 0.096 0.096 ↑ 1.0 1 16

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.006..0.006 rows=1 loops=16)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=64
15.          

SubPlan (for Nested Loop Left Join)

16. 0.016 0.016 ↑ 1.0 1 2

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.008..0.008 rows=1 loops=2)

  • 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=9
17. 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
18. 0.304 0.304 ↑ 40.2 4 16

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

  • Buffers: shared hit=30
19.          

CTE base_and_descendants

20. 0.083 0.219 ↑ 7.0 23 1

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

  • Buffers: shared hit=30
21. 0.022 0.022 ↑ 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.022..0.022 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
22. 0.019 0.114 ↓ 1.4 22 1

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.034..0.114 rows=22 loops=1)

  • Buffers: shared hit=26
23. 0.002 0.002 ↑ 10.0 1 1

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

24. 0.093 0.093 ↓ 11.0 22 1

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.027..0.093 rows=22 loops=1)

  • 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=26
25. 0.208 0.208 ↓ 0.0 0 16

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.77 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=16)

  • Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
  • Heap Fetches: 3
  • Filter: (label_links.label_id = ANY ('{3519305,10230929,7402316}'::integer[]))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=109