explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FyBN

Settings
# exclusive inclusive rows x rows loops node
1. 36.901 335.492 ↑ 1.0 4 1

HashAggregate (cost=33,053.28..33,053.32 rows=4 width=10) (actual time=335.490..335.492 rows=4 loops=1)

  • Group Key: merge_requests.state_id
  • Buffers: shared hit=135,471
2. 24.704 298.591 ↓ 22.2 118,345 1

Nested Loop (cost=1,107.28..33,026.63 rows=5,330 width=2) (actual time=2.090..298.591 rows=118,345 loops=1)

  • Buffers: shared hit=135,471
3. 2.671 22.106 ↑ 1.5 943 1

Nested Loop Left Join (cost=1,106.71..21,322.95 rows=1,444 width=4) (actual time=2.071..22.106 rows=943 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: 33
  • Buffers: shared hit=12,491
4. 0.394 11.368 ↑ 1.5 976 1

Nested Loop (cost=1,106.28..15,149.12 rows=1,506 width=4) (actual time=2.048..11.368 rows=976 loops=1)

  • Buffers: shared hit=6,869
5. 0.172 2.100 ↓ 1.0 153 1

HashAggregate (cost=1,105.84..1,107.35 rows=151 width=4) (actual time=1.999..2.100 rows=153 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=761
6. 1.928 1.928 ↓ 1.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.031..1.928 rows=153 loops=1)

  • Buffers: shared hit=761
7.          

CTE base_and_descendants

8. 0.493 1.693 ↓ 1.0 153 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.028..1.693 rows=153 loops=1)

  • Buffers: shared hit=761
9. 0.020 0.020 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=357) (actual time=0.019..0.020 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
10. 0.089 1.180 ↓ 2.0 30 5

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.025..0.236 rows=30 loops=5)

  • Buffers: shared hit=757
11. 0.020 0.020 ↓ 3.1 31 5

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

12. 1.071 1.071 ↑ 2.0 1 153

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..10.90 rows=2 width=357) (actual time=0.005..0.007 rows=1 loops=153)

  • 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=757
13. 3.994 8.874 ↑ 1.7 6 153

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.89 rows=10 width=8) (actual time=0.012..0.058 rows=6 loops=153)

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

SubPlan (for Index Scan)

15. 4.880 4.880 ↑ 1.0 1 976

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.005..0.005 rows=1 loops=976)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 338
  • Buffers: shared hit=4,626
16. 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..111.99 rows=1,248 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
17. 5.856 5.856 ↑ 1.0 1 976

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=976)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=3,905
18.          

SubPlan (for Nested Loop Left Join)

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_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
20. 2.211 2.211 ↓ 2.6 3,307 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_3 (cost=0.57..111.99 rows=1,248 width=4) (actual time=0.031..2.211 rows=3,307 loops=1)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 20))
  • Heap Fetches: 861
  • Buffers: shared hit=1,717
21. 251.781 251.781 ↑ 2.0 125 943

Index Scan using index_merge_requests_on_target_project_id_and_iid on public.merge_requests (cost=0.56..5.61 rows=250 width=6) (actual time=0.007..0.267 rows=125 loops=943)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=122,980