explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cKvj

Settings
# exclusive inclusive rows x rows loops node
1. 0.733 492.514 ↑ 5.0 122 1

GroupAggregate (cost=28,747.71..28,762.93 rows=609 width=16) (actual time=491.769..492.514 rows=122 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 2.720 491.781 ↓ 10.5 6,410 1

Sort (cost=28,747.71..28,749.23 rows=609 width=8) (actual time=491.206..491.781 rows=6,410 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: 493kB
3. 5.477 489.061 ↓ 10.5 6,410 1

Nested Loop Left Join (cost=1,264.04..28,719.54 rows=609 width=8) (actual time=0.998..489.061 rows=6,410 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
4. 36.494 470.764 ↓ 10.4 6,410 1

Nested Loop (cost=1,263.60..28,414.14 rows=618 width=20) (actual time=0.979..470.764 rows=6,410 loops=1)

5. 6.892 207.754 ↓ 10.7 56,629 1

Nested Loop (cost=1,263.04..25,262.70 rows=5,275 width=16) (actual time=0.747..207.754 rows=56,629 loops=1)

6. 0.152 5.868 ↑ 3.4 621 1

Nested Loop (cost=1,262.48..12,141.98 rows=2,095 width=4) (actual time=0.712..5.868 rows=621 loops=1)

7. 0.101 0.748 ↑ 1.9 69 1

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

  • Group Key: namespaces.id
8. 0.647 0.647 ↑ 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.024..0.647 rows=69 loops=1)

9.          

CTE base_and_descendants

10. 0.136 0.581 ↑ 1.9 69 1

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

11. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

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

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

13. 0.010 0.010 ↓ 1.4 14 5

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

14. 0.414 0.414 ↑ 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.004..0.006 rows=1 loops=69)

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

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..82.88 rows=16 width=8) (actual time=0.013..0.072 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. 2.484 2.484 ↑ 1.0 1 621

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) (actual time=0.004..0.004 rows=1 loops=621)

  • Index Cond: ((user_id = 1) AND (project_id = projects.id))
  • Heap Fetches: 121
18. 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.56..368.64 rows=2,900 width=4) (never executed)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
19. 194.994 194.994 ↑ 1.8 91 621

Index Scan using index_merge_requests_target_project_id_created_at on merge_requests (cost=0.56..4.66 rows=160 width=16) (actual time=0.009..0.314 rows=91 loops=621)

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

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.004..0.004 rows=0 loops=56,629)

  • Index Cond: ((merge_request_id = merge_requests.id) AND (merged_at >= '2019-08-01 09:42:14'::timestamp without time zone))
  • Heap Fetches: 1662
21. 12.820 12.820 ↑ 1.0 1 6,410

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=6,410)

  • Index Cond: (projects.id = project_id)
Planning time : 3.175 ms
Execution time : 493.188 ms