explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W3G5

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 59.215 ↑ 1.0 1 1

Aggregate (cost=395,337.34..395,337.35 rows=1 width=8) (actual time=59.215..59.215 rows=1 loops=1)

2. 0.005 59.187 ↑ 1.5 2 1

Nested Loop (cost=1,282.47..395,337.31 rows=3 width=16) (actual time=53.585..59.187 rows=2 loops=1)

  • Join Filter: (merge_requests_closing_issues.merge_request_id = merge_requests.id)
3. 0.311 59.152 ↑ 5.0 2 1

Nested Loop (cost=1,281.90..395,330.12 rows=10 width=24) (actual time=53.561..59.152 rows=2 loops=1)

  • Join Filter: (merge_request_metrics.first_deployed_to_production_at >= issues.created_at)
  • Rows Removed by Join Filter: 405
4. 0.000 55.585 ↓ 14.0 407 1

Nested Loop (cost=1,281.34..395,302.89 rows=29 width=12) (actual time=5.727..55.585 rows=407 loops=1)

5. 0.882 40.982 ↓ 10.8 5,003 1

Nested Loop (cost=1,280.91..395,073.21 rows=463 width=12) (actual time=1.486..40.982 rows=5,003 loops=1)

6. 1.455 17.038 ↑ 1.6 887 1

Nested Loop Left Join (cost=1,280.34..21,577.34 rows=1,458 width=4) (actual time=1.460..17.038 rows=887 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 94
7. 0.196 8.124 ↑ 1.6 981 1

Nested Loop (cost=1,279.91..15,330.98 rows=1,524 width=4) (actual time=1.441..8.124 rows=981 loops=1)

8. 0.086 1.428 ↑ 1.2 130 1

HashAggregate (cost=1,279.47..1,280.98 rows=151 width=4) (actual time=1.377..1.428 rows=130 loops=1)

  • Group Key: namespaces.id
9. 1.342 1.342 ↑ 1.2 130 1

CTE Scan on base_and_descendants namespaces (cost=1,274.57..1,277.59 rows=151 width=4) (actual time=0.028..1.342 rows=130 loops=1)

10.          

CTE base_and_descendants

11. 0.275 1.210 ↑ 1.2 130 1

Recursive Union (cost=0.43..1,274.57 rows=151 width=329) (actual time=0.025..1.210 rows=130 loops=1)

12. 0.020 0.020 ↑ 1.0 1 1

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

  • Index Cond: (id = 9,970)
  • Filter: ((type)::text = 'Group'::text)
13. 0.125 0.915 ↓ 1.7 26 5

Nested Loop (cost=0.56..126.81 rows=15 width=329) (actual time=0.017..0.183 rows=26 loops=5)

14. 0.010 0.010 ↓ 2.6 26 5

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

15. 0.780 0.780 ↑ 1.0 1 130

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.56..12.65 rows=1 width=329) (actual time=0.004..0.006 rows=1 loops=130)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
16. 3.557 6.500 ↑ 1.2 8 130

Index Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..92.95 rows=10 width=8) (actual time=0.010..0.050 rows=8 loops=130)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = ANY ('{10,20}'::integer[])))
17.          

SubPlan (for Index Scan)

18. 2.943 2.943 ↑ 1.0 1 981

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 time=0.003..0.003 rows=1 loops=981)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 146
19. 0.000 0.000 ↓ 0.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..210.67 rows=1,253 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 0
20. 5.886 5.886 ↑ 1.0 1 981

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=981)

  • Index Cond: (projects.id = project_id)
21.          

SubPlan (for Nested Loop Left Join)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.57..3.59 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
23. 1.573 1.573 ↓ 2.5 3,163 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.57..210.67 rows=1,253 width=4) (actual time=0.019..1.573 rows=3,163 loops=1)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 10))
  • Heap Fetches: 408
24. 22.150 23.062 ↑ 5.8 6 887

Index Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on issues (cost=0.56..255.82 rows=35 width=16) (actual time=0.007..0.026 rows=6 loops=887)

  • Index Cond: ((project_id = projects.id) AND (created_at <= '2020-07-06 23:59:59.999999+00'::timestamp with time zone) AND (created_at >= '2020-06-07 00:00:00+00'::timestamp with time zone))
  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 4,156,052) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))))
25.          

SubPlan (for Index Scan)

26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 4,156,052))
  • Heap Fetches: 0
27. 0.310 0.310 ↓ 1.5 125 1

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..82.90 rows=82 width=4) (actual time=0.021..0.310 rows=125 loops=1)

  • Index Cond: (user_id = 4,156,052)
28. 0.602 0.602 ↑ 1.0 1 301

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 time=0.002..0.002 rows=1 loops=301)

  • Index Cond: ((user_id = 4,156,052) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 13
29. 0.000 0.000 ↓ 0.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..180.13 rows=1,042 width=4) (never executed)

  • Index Cond: ((user_id = 4,156,052) AND (access_level >= 20))
  • Heap Fetches: 0
30. 15.009 15.009 ↓ 0.0 0 5,003

Index Scan using index_merge_requests_closing_issues_on_issue_id on merge_requests_closing_issues (cost=0.43..0.48 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=5,003)

  • Index Cond: (issue_id = issues.id)
31. 3.256 3.256 ↑ 1.0 1 407

Index Scan using unique_merge_request_metrics_by_merge_request_id on merge_request_metrics (cost=0.56..0.93 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=407)

  • Index Cond: (merge_request_id = merge_requests_closing_issues.merge_request_id)
32. 0.030 0.030 ↑ 1.0 1 2

Index Scan using merge_requests_pkey on merge_requests (cost=0.56..0.71 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=2)

  • Index Cond: (id = merge_request_metrics.merge_request_id)
  • Filter: (merge_request_metrics.first_deployed_to_production_at >= created_at)
Planning time : 7.101 ms
Execution time : 59.549 ms