explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MSu6

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 34.469 ↑ 1.0 20 1

Limit (cost=18,017.66..18,017.71 rows=20 width=725) (actual time=34.460..34.469 rows=20 loops=1)

  • Buffers: shared hit=24,033
2. 2.299 34.463 ↑ 10.2 20 1

Sort (cost=18,017.66..18,018.17 rows=204 width=725) (actual time=34.459..34.463 rows=20 loops=1)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
  • Sort Method: top-N heapsort Memory: 65kB
  • Buffers: shared hit=24,033
3. 3.920 32.164 ↓ 12.0 2,446 1

Nested Loop Left Join (cost=1,107.14..18,012.23 rows=204 width=725) (actual time=1.923..32.164 rows=2,446 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: 1
  • Buffers: shared hit=24,033
4. 1.669 21.847 ↓ 11.5 2,447 1

Nested Loop (cost=1,106.70..17,139.04 rows=213 width=729) (actual time=1.902..21.847 rows=2,447 loops=1)

  • Buffers: shared hit=12,528
5. 0.378 10.418 ↑ 1.5 976 1

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

  • Buffers: shared hit=6,869
6. 0.123 1.778 ↓ 1.0 153 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=761
7. 1.655 1.655 ↓ 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.030..1.655 rows=153 loops=1)

  • Buffers: shared hit=761
8.          

CTE base_and_descendants

9. 0.384 1.463 ↓ 1.0 153 1

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

  • Buffers: shared hit=761
10. 0.019 0.019 ↑ 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.018..0.019 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
11. 0.127 1.060 ↓ 2.0 30 5

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

  • Buffers: shared hit=757
12. 0.015 0.015 ↓ 3.1 31 5

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

13. 0.918 0.918 ↑ 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.004..0.006 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
14. 4.358 8.262 ↑ 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.014..0.054 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
15.          

SubPlan (for Index Scan)

16. 3.904 3.904 ↑ 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.004..0.004 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
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_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
18. 9.760 9.760 ↑ 3.3 3 976

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on public.merge_requests (cost=0.43..1.22 rows=10 width=725) (actual time=0.004..0.010 rows=3 loops=976)

  • Index Cond: (merge_requests.target_project_id = projects.id)
  • Buffers: shared hit=5,659
19. 4.894 4.894 ↑ 1.0 1 2,447

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=2,447)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=9,788
20.          

SubPlan (for Nested Loop Left Join)

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_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
22. 1.503 1.503 ↓ 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.022..1.503 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