explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SwgP

Settings
# exclusive inclusive rows x rows loops node
1. 0.234 499.351 ↓ 3.2 74 1

GroupAggregate (cost=7,299.13..7,299.71 rows=23 width=16) (actual time=499.128..499.351 rows=74 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 0.559 499.117 ↓ 84.8 1,951 1

Sort (cost=7,299.13..7,299.19 rows=23 width=8) (actual time=498.984..499.117 rows=1,951 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: 140kB
3. 0.937 498.558 ↓ 84.8 1,951 1

Nested Loop (cost=7,194.34..7,298.61 rows=23 width=8) (actual time=465.086..498.558 rows=1,951 loops=1)

4. 0.670 482.013 ↓ 78.0 1,951 1

Nested Loop (cost=7,193.78..7,283.21 rows=25 width=16) (actual time=465.051..482.013 rows=1,951 loops=1)

5. 1.156 465.735 ↓ 78.0 1,951 1

HashAggregate (cost=7,193.21..7,193.46 rows=25 width=4) (actual time=465.034..465.735 rows=1,951 loops=1)

  • Group Key: merge_requests_1.id
6. 0.729 464.579 ↓ 78.0 1,951 1

Sort (cost=7,192.84..7,192.90 rows=25 width=4) (actual time=464.393..464.579 rows=1,951 loops=1)

  • Sort Key: merge_requests_1.id DESC
  • Sort Method: quicksort Memory: 140kB
7. 1.443 463.850 ↓ 78.0 1,951 1

Nested Loop Left Join (cost=1,264.04..7,192.26 rows=25 width=4) (actual time=1.236..463.850 rows=1,951 loops=1)

  • Filter: ((project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR (project_features.merge_requests_access_level IS NULL))
  • Rows Removed by Filter: 85
8. 33.277 458.335 ↓ 78.3 2,036 1

Nested Loop (cost=1,263.60..7,175.71 rows=26 width=8) (actual time=1.219..458.335 rows=2,036 loops=1)

9. 6.498 199.858 ↓ 84.3 56,300 1

Nested Loop (cost=1,263.04..6,776.63 rows=668 width=8) (actual time=0.906..199.858 rows=56,300 loops=1)

10. 0.068 2.860 ↓ 1.9 500 1

Nested Loop (cost=1,262.48..4,606.17 rows=265 width=4) (actual time=0.859..2.860 rows=500 loops=1)

11. 0.044 0.860 ↑ 1.9 69 1

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

  • Group Key: namespaces.id
12. 0.816 0.816 ↑ 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.049..0.816 rows=69 loops=1)

13.          

CTE base_and_descendants

14. 0.227 0.739 ↑ 1.9 69 1

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

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

  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
16. 0.002 0.495 ↓ 1.1 14 5

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

17. 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)

18. 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)
19. 1.932 1.932 ↓ 3.5 7 69

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..25.50 rows=2 width=8) (actual time=0.010..0.028 rows=7 loops=69)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: (visibility_level = 20)
  • Rows Removed by Filter: 2
20. 190.500 190.500 ↑ 1.4 113 500

Index Scan using index_merge_requests_target_project_id_created_at on merge_requests merge_requests_1 (cost=0.56..6.59 rows=160 width=8) (actual time=0.009..0.381 rows=113 loops=500)

  • Index Cond: (target_project_id = projects.id)
  • Filter: (state_id = 3)
  • Rows Removed by Filter: 25
21. 225.200 225.200 ↓ 0.0 0 56,300

Index Only Scan using index_merge_request_metrics_on_merge_request_id_and_merged_at on merge_request_metrics merge_request_metrics_1 (cost=0.56..0.59 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=56,300)

  • Index Cond: ((merge_request_id = merge_requests_1.id) AND (merged_at >= '2019-10-05 09:42:14'::timestamp without time zone))
  • Heap Fetches: 1161
22. 4.072 4.072 ↑ 1.0 1 2,036

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,036)

  • Index Cond: (projects.id = project_id)
23. 15.608 15.608 ↑ 1.0 1 1,951

Index Scan using merge_requests_pkey on merge_requests (cost=0.56..3.58 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1,951)

  • Index Cond: (id = merge_requests_1.id)
24. 15.608 15.608 ↑ 1.0 1 1,951

Index Scan using index_merge_request_metrics on merge_request_metrics (cost=0.56..0.60 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=1,951)

  • Index Cond: (merge_request_id = merge_requests.id)
Planning time : 3.044 ms
Execution time : 499.662 ms