explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LEWl

Settings
# exclusive inclusive rows x rows loops node
1. 0.709 509.442 ↑ 5.0 122 1

GroupAggregate (cost=28,747.69..28,762.92 rows=609 width=16) (actual time=508.689..509.442 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.387 508.733 ↓ 10.5 6,410 1

Sort (cost=28,747.69..28,749.21 rows=609 width=8) (actual time=508.201..508.733 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.225 506.346 ↓ 10.5 6,410 1

Nested Loop Left Join (cost=1,264.04..28,719.52 rows=609 width=8) (actual time=1.247..506.346 rows=6,410 loops=1)

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

Nested Loop (cost=1,263.60..28,414.12 rows=618 width=20) (actual time=1.209..488.301 rows=6,410 loops=1)

5. 6.927 215.025 ↓ 10.7 56,629 1

Nested Loop (cost=1,263.04..25,262.68 rows=5,275 width=16) (actual time=0.932..215.025 rows=56,629 loops=1)

6. 0.152 5.652 ↑ 3.4 621 1

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

7. 0.073 0.877 ↑ 1.9 69 1

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

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

9.          

CTE base_and_descendants

10. 0.174 0.715 ↑ 1.9 69 1

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

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

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

Nested Loop (cost=0.43..125.17 rows=13 width=322) (actual time=0.018..0.103 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.483 0.483 ↑ 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.005..0.007 rows=1 loops=69)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 2.139 4.623 ↑ 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.067 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. 202.446 202.446 ↑ 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.326 rows=91 loops=621)

  • Index Cond: (target_project_id = projects.id)
  • Filter: (state_id = 3)
  • Rows Removed by Filter: 20
20. 283.145 283.145 ↓ 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.005..0.005 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: 1661
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 : 2.253 ms
Execution time : 509.875 ms