explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tovm

Settings
# exclusive inclusive rows x rows loops node
1. 0.247 4,151.577 ↓ 3.2 74 1

GroupAggregate (cost=7,299.09..7,299.66 rows=23 width=16) (actual time=4,151.350..4,151.577 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.718 4,151.330 ↓ 84.8 1,950 1

Sort (cost=7,299.09..7,299.14 rows=23 width=8) (actual time=4,151.197..4,151.330 rows=1,950 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. 1.226 4,150.612 ↓ 84.8 1,950 1

Nested Loop (cost=7,194.29..7,298.57 rows=23 width=8) (actual time=4,045.586..4,150.612 rows=1,950 loops=1)

4. 1.458 4,090.886 ↓ 78.0 1,950 1

Nested Loop (cost=7,193.73..7,283.17 rows=25 width=16) (actual time=4,045.524..4,090.886 rows=1,950 loops=1)

5. 1.565 4,046.528 ↓ 78.0 1,950 1

HashAggregate (cost=7,193.17..7,193.42 rows=25 width=4) (actual time=4,045.482..4,046.528 rows=1,950 loops=1)

  • Group Key: merge_requests_1.id
6. 1.388 4,044.963 ↓ 78.0 1,950 1

Sort (cost=7,192.79..7,192.86 rows=25 width=4) (actual time=4,044.745..4,044.963 rows=1,950 loops=1)

  • Sort Key: merge_requests_1.id DESC
  • Sort Method: quicksort Memory: 140kB
7. 2.232 4,043.575 ↓ 78.0 1,950 1

Nested Loop Left Join (cost=1,264.04..7,192.21 rows=25 width=4) (actual time=18.329..4,043.575 rows=1,950 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. 15.991 4,033.203 ↓ 78.3 2,035 1

Nested Loop (cost=1,263.60..7,175.66 rows=26 width=8) (actual time=18.244..4,033.203 rows=2,035 loops=1)

9. 8.274 639.272 ↓ 84.3 56,299 1

Nested Loop (cost=1,263.04..6,776.58 rows=668 width=8) (actual time=2.364..639.272 rows=56,299 loops=1)

10. 0.168 7.998 ↓ 1.9 500 1

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

11. 0.064 2.103 ↑ 1.9 69 1

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

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

13.          

CTE base_and_descendants

14. 0.150 1.931 ↑ 1.9 69 1

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

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

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

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

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

18. 1.725 1.725 ↑ 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.025 rows=1 loops=69)

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

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: (visibility_level = 20)
  • Rows Removed by Filter: 2
20. 623.000 623.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.59 rows=160 width=8) (actual time=0.055..1.246 rows=113 loops=500)

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

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.060..0.060 rows=0 loops=56,299)

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

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

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

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

  • Index Cond: (id = merge_requests_1.id)
24. 58.500 58.500 ↑ 1.0 1 1,950

Index Scan using index_merge_request_metrics on merge_request_metrics (cost=0.56..0.60 rows=1 width=12) (actual time=0.029..0.030 rows=1 loops=1,950)

  • Index Cond: (merge_request_id = merge_requests.id)
Planning time : 9.239 ms
Execution time : 4,152.097 ms