explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vpaa

Settings
# exclusive inclusive rows x rows loops node
1. 0.400 487.610 ↑ 2.7 75 1

GroupAggregate (cost=28,487.20..28,492.17 rows=199 width=16) (actual time=487.168..487.610 rows=75 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 0.849 487.210 ↓ 10.3 2,042 1

Sort (cost=28,487.20..28,487.69 rows=199 width=8) (actual time=486.902..487.210 rows=2,042 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: 144kB
3. 2.387 486.361 ↓ 10.3 2,042 1

Nested Loop Left Join (cost=1,264.04..28,479.60 rows=199 width=8) (actual time=1.120..486.361 rows=2,042 loops=1)

  • Filter: ((project_features.merge_requests_access_level > 0) OR (project_features.merge_requests_access_level IS NULL))
4. 0.000 479.890 ↓ 10.1 2,042 1

Nested Loop (cost=1,263.60..28,379.78 rows=202 width=20) (actual time=1.101..479.890 rows=2,042 loops=1)

5. 6.759 212.176 ↓ 10.8 56,628 1

Nested Loop (cost=1,263.04..25,236.71 rows=5,261 width=16) (actual time=0.778..212.176 rows=56,628 loops=1)

6. 0.156 6.076 ↑ 3.4 621 1

Nested Loop (cost=1,262.48..12,141.29 rows=2,091 width=4) (actual time=0.742..6.076 rows=621 loops=1)

7. 0.058 0.745 ↑ 1.9 69 1

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

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

9.          

CTE base_and_descendants

10. 0.131 0.615 ↑ 1.9 69 1

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

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

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

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

13. 0.005 0.005 ↓ 1.4 14 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.001 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.691 5.175 ↑ 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.014..0.075 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. 199.341 199.341 ↑ 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.010..0.321 rows=91 loops=621)

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

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.005..0.005 rows=0 loops=56,628)

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

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

  • Index Cond: (projects.id = project_id)
Planning time : 2.166 ms
Execution time : 487.849 ms