explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3yaS

Settings
# exclusive inclusive rows x rows loops node
1. 0.250 4,171.819 ↑ 2.7 75 1

GroupAggregate (cost=28,460.13..28,465.18 rows=202 width=16) (actual time=4,171.549..4,171.819 rows=75 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 1.223 4,171.569 ↓ 10.1 2,049 1

Sort (cost=28,460.13..28,460.64 rows=202 width=8) (actual time=4,171.336..4,171.569 rows=2,049 loops=1)

  • Sort Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
  • Sort Method: quicksort Memory: 193kB
3. 2.857 4,170.346 ↓ 10.1 2,049 1

Nested Loop Left Join (cost=1,264.04..28,452.40 rows=202 width=8) (actual time=35.174..4,170.346 rows=2,049 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
4. 4.832 4,159.293 ↓ 10.0 2,049 1

Nested Loop (cost=1,263.60..28,351.09 rows=205 width=20) (actual time=35.075..4,159.293 rows=2,049 loops=1)

5. 8.368 586.456 ↓ 10.8 56,635 1

Nested Loop (cost=1,263.04..25,198.39 rows=5,263 width=16) (actual time=13.491..586.456 rows=56,635 loops=1)

6. 0.260 21.051 ↑ 3.4 621 1

Nested Loop (cost=1,262.48..12,140.41 rows=2,095 width=4) (actual time=13.332..21.051 rows=621 loops=1)

7. 0.133 2.575 ↑ 1.9 69 1

HashAggregate (cost=1,262.04..1,263.35 rows=131 width=4) (actual time=2.466..2.575 rows=69 loops=1)

  • Group Key: namespaces.id
8. 2.442 2.442 ↑ 1.9 69 1

CTE Scan on base_and_descendants namespaces (cost=1,257.79..1,260.41 rows=131 width=4) (actual time=0.053..2.442 rows=69 loops=1)

9.          

CTE base_and_descendants

10. 0.184 2.342 ↑ 1.9 69 1

Recursive Union (cost=0.43..1,257.79 rows=131 width=322) (actual time=0.051..2.342 rows=69 loops=1)

11. 0.043 0.043 ↑ 1.0 1 1

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

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
12. 0.030 2.115 ↓ 1.1 14 5

Nested Loop (cost=0.43..125.17 rows=13 width=322) (actual time=0.054..0.423 rows=14 loops=5)

13. 0.015 0.015 ↓ 1.4 14 5

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

14. 2.070 2.070 ↑ 1.0 1 69

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.49 rows=1 width=322) (actual time=0.016..0.030 rows=1 loops=69)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 8.189 18.216 ↑ 1.8 9 69

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..82.87 rows=16 width=8) (actual time=0.184..0.264 rows=9 loops=69)

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

SubPlan (for Index Scan)

17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 1) AND (project_id = projects.id))
  • Heap Fetches: 0
18. 10.027 10.027 ↓ 20.9 2,443 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..34.50 rows=117 width=4) (actual time=0.083..10.027 rows=2,443 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 459
19. 557.037 557.037 ↑ 1.7 91 621

Index Scan using index_merge_requests_target_project_id_created_at on merge_requests (cost=0.56..4.64 rows=159 width=16) (actual time=0.041..0.897 rows=91 loops=621)

  • Index Cond: (target_project_id = projects.id)
  • Filter: (state_id = 3)
  • Rows Removed by Filter: 20
20. 3,568.005 3,568.005 ↓ 0.0 0 56,635

Index Only Scan using index_merge_request_metrics_on_merge_request_id_and_merged_at on merge_request_metrics (cost=0.56..0.59 rows=1 width=12) (actual time=0.063..0.063 rows=0 loops=56,635)

  • Index Cond: ((merge_request_id = merge_requests.id) AND (merged_at >= '2019-10-05 14:34:33'::timestamp without time zone))
  • Heap Fetches: 1189
21. 8.196 8.196 ↑ 1.0 1 2,049

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.47 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,049)

  • Index Cond: (projects.id = project_id)
Planning time : 10.620 ms
Execution time : 4,172.755 ms