explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tfJA

Settings
# exclusive inclusive rows x rows loops node
1. 0.671 595.116 ↓ 1.8 121 1

GroupAggregate (cost=7,549.33..7,551.03 rows=68 width=16) (actual time=594.402..595.116 rows=121 loops=1)

  • Group Key: (date_part('day'::text, ((merge_request_metrics.merged_at)::timestamp with time zone - merge_requests.created_at)))
2. 2.310 594.445 ↓ 91.1 6,196 1

Sort (cost=7,549.33..7,549.50 rows=68 width=8) (actual time=593.901..594.445 rows=6,196 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. 1.974 592.135 ↓ 91.1 6,196 1

Nested Loop (cost=7,232.20..7,547.26 rows=68 width=8) (actual time=485.370..592.135 rows=6,196 loops=1)

4. 2.385 540.593 ↓ 82.6 6,196 1

Nested Loop (cost=7,231.64..7,501.07 rows=75 width=16) (actual time=485.330..540.593 rows=6,196 loops=1)

5. 4.938 488.640 ↓ 82.6 6,196 1

HashAggregate (cost=7,231.07..7,231.82 rows=75 width=4) (actual time=485.307..488.640 rows=6,196 loops=1)

  • Group Key: merge_requests_1.id
6. 3.251 483.702 ↓ 82.6 6,196 1

Sort (cost=7,229.95..7,230.14 rows=75 width=4) (actual time=483.056..483.702 rows=6,196 loops=1)

  • Sort Key: merge_requests_1.id DESC
  • Sort Method: quicksort Memory: 483kB
7. 2.674 480.451 ↓ 82.6 6,196 1

Nested Loop Left Join (cost=1,264.04..7,227.61 rows=75 width=4) (actual time=1.200..480.451 rows=6,196 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. 34.981 465.039 ↓ 81.7 6,369 1

Nested Loop (cost=1,263.60..7,177.93 rows=78 width=8) (actual time=1.168..465.039 rows=6,369 loops=1)

9. 6.349 204.858 ↓ 84.3 56,300 1

Nested Loop (cost=1,263.04..6,778.85 rows=668 width=8) (actual time=0.890..204.858 rows=56,300 loops=1)

10. 0.097 3.009 ↓ 1.9 500 1

Nested Loop (cost=1,262.48..4,606.86 rows=265 width=4) (actual time=0.836..3.009 rows=500 loops=1)

11. 0.063 0.842 ↑ 1.9 69 1

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

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

13.          

CTE base_and_descendants

14. 0.142 0.692 ↑ 1.9 69 1

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

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

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

Nested Loop (cost=0.43..125.17 rows=13 width=322) (actual time=0.016..0.102 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.004..0.007 rows=1 loops=69)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
19. 2.070 2.070 ↓ 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.012..0.030 rows=7 loops=69)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: (visibility_level = 20)
  • Rows Removed by Filter: 2
20. 195.500 195.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.60 rows=160 width=8) (actual time=0.009..0.391 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-08-01 09:42:14'::timestamp without time zone))
  • Heap Fetches: 1652
22. 12.738 12.738 ↑ 1.0 1 6,369

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=6,369)

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

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=6,196)

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

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=6,196)

  • Index Cond: (merge_request_id = merge_requests.id)
Planning time : 4.291 ms
Execution time : 595.736 ms