explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txPG

Settings
# exclusive inclusive rows x rows loops node
1. 0.970 7,858.818 ↓ 1.8 121 1

GroupAggregate (cost=7,568.72..7,570.44 rows=69 width=16) (actual time=7,857.665..7,858.818 rows=121 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 3.072 7,857.848 ↓ 89.9 6,202 1

Sort (cost=7,568.72..7,568.89 rows=69 width=8) (actual time=7,856.986..7,857.848 rows=6,202 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: 483kB
3. 10.131 7,854.776 ↓ 89.9 6,202 1

Nested Loop (cost=7,247.33..7,566.61 rows=69 width=8) (actual time=7,494.396..7,854.776 rows=6,202 loops=1)

4. 0.073 7,639.979 ↓ 81.6 6,202 1

Nested Loop (cost=7,246.76..7,519.80 rows=76 width=16) (actual time=7,494.138..7,639.979 rows=6,202 loops=1)

5. 4.967 7,497.260 ↓ 81.6 6,202 1

HashAggregate (cost=7,246.20..7,246.96 rows=76 width=4) (actual time=7,494.021..7,497.260 rows=6,202 loops=1)

  • Group Key: merge_requests_1.id
6. 5.227 7,492.293 ↓ 81.6 6,202 1

Sort (cost=7,245.06..7,245.25 rows=76 width=4) (actual time=7,491.464..7,492.293 rows=6,202 loops=1)

  • Sort Key: merge_requests_1.id DESC
  • Sort Method: quicksort Memory: 483kB
7. 8.926 7,487.066 ↓ 81.6 6,202 1

Nested Loop Left Join (cost=1,264.04..7,242.69 rows=76 width=4) (actual time=29.222..7,487.066 rows=6,202 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: 173
8. 43.354 7,452.640 ↓ 79.7 6,375 1

Nested Loop (cost=1,263.60..7,191.85 rows=80 width=8) (actual time=29.138..7,452.640 rows=6,375 loops=1)

9. 9.899 652.566 ↓ 83.7 56,306 1

Nested Loop (cost=1,263.04..6,788.70 rows=673 width=8) (actual time=2.193..652.566 rows=56,306 loops=1)

10. 0.158 7.667 ↓ 1.9 500 1

Nested Loop (cost=1,262.48..4,605.29 rows=268 width=4) (actual time=1.975..7.667 rows=500 loops=1)

11. 0.078 1.920 ↑ 1.9 69 1

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

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

13.          

CTE base_and_descendants

14. 0.153 1.746 ↑ 1.9 69 1

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

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

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

Nested Loop (cost=0.43..125.17 rows=13 width=322) (actual time=0.044..0.312 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. 1.518 1.518 ↑ 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.014..0.022 rows=1 loops=69)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
19. 5.589 5.589 ↓ 3.5 7 69

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..25.49 rows=2 width=8) (actual time=0.025..0.081 rows=7 loops=69)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: (visibility_level = 20)
  • Rows Removed by Filter: 2
20. 635.000 635.000 ↑ 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.56 rows=159 width=8) (actual time=0.058..1.270 rows=113 loops=500)

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

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.120..0.120 rows=0 loops=56,306)

  • Index Cond: ((merge_request_id = merge_requests_1.id) AND (merged_at >= '2019-08-01 09:42:14'::timestamp without time zone))
  • Heap Fetches: 1705
22. 25.500 25.500 ↑ 1.0 1 6,375

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.62 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=6,375)

  • Index Cond: (projects.id = project_id)
23. 142.646 142.646 ↑ 1.0 1 6,202

Index Scan using merge_requests_pkey on merge_requests (cost=0.56..3.58 rows=1 width=12) (actual time=0.022..0.023 rows=1 loops=6,202)

  • Index Cond: (id = merge_requests_1.id)
24. 204.666 204.666 ↑ 1.0 1 6,202

Index Scan using index_merge_request_metrics on merge_request_metrics (cost=0.56..0.60 rows=1 width=12) (actual time=0.033..0.033 rows=1 loops=6,202)

  • Index Cond: (merge_request_id = merge_requests.id)
Planning time : 11.857 ms
Execution time : 7,859.434 ms