explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gQrC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=4,172,217.05..4,172,217.10 rows=20 width=725) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=4,172,217.05..4,173,598.12 rows=552,430 width=725) (actual rows= loops=)

  • Sort Key: merge_requests.updated_at DESC, merge_requests.id DESC
3. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,992,606.21..4,157,517.08 rows=552,430 width=725) (actual rows= loops=)

  • Merge Cond: (projects.id = merge_requests.target_project_id)
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,992,603.33..2,002,381.21 rows=3,911,153 width=4) (actual rows= loops=)

  • Sort Key: projects.id
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,076,137.93..1,461,221.95 rows=3,911,153 width=4) (actual rows= loops=)

  • Hash Cond: (projects.id = project_features.project_id)
  • 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)))
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,142.30..19,736.39 rows=4,078,448 width=4) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,141.86..1,143.86 rows=200 width=4) (actual rows= loops=)

  • Group Key: namespaces.id
8. 0.000 0.000 ↓ 0.0

CTE Scan on base_and_descendants namespaces (cost=1,117.13..1,132.35 rows=761 width=4) (actual rows= loops=)

9.          

CTE base_and_descendants

10. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.43..1,117.13 rows=761 width=357) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=357) (actual rows= loops=)

  • Index Cond: (id = 9,970)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..109.85 rows=76 width=357) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..10.88 rows=8 width=357) (actual rows= loops=)

  • Index Cond: (parent_id = base_and_descendants.id)
15. 0.000 0.000 ↓ 0.0

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..92.86 rows=10 width=8) (actual rows= loops=)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))))
16.          

SubPlan (for Index Scan)

17. 0.000 0.000 ↓ 0.0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 20))
18. 0.000 0.000 ↓ 0.0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.57..111.99 rows=1,248 width=4) (actual rows= loops=)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
19. 0.000 0.000 ↓ 0.0

Hash (cost=664,007.65..664,007.65 rows=14,612,799 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..664,007.65 rows=14,612,799 width=8) (actual rows= loops=)

21.          

SubPlan (for Hash Left Join)

22. 0.000 0.000 ↓ 0.0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 20))
23. 0.000 0.000 ↓ 0.0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.57..111.99 rows=1,248 width=4) (actual rows= loops=)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
24. 0.000 0.000 ↓ 0.0

Index Scan using idx_merge_requests_on_target_project_id_and_iid_opened on merge_requests (cost=0.43..2,134,675.09 rows=2,064,489 width=725) (actual rows= loops=)