explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KXF0

Settings
# exclusive inclusive rows x rows loops node
1. 0.606 219.801 ↓ 436.0 436 1

GroupAggregate (cost=18,837.37..18,837.40 rows=1 width=16) (actual time=218.939..219.801 rows=436 loops=1)

  • Group Key: label_links.label_id, (date(merge_requests.created_at))
2. 1.788 219.195 ↓ 3,929.0 3,929 1

Sort (cost=18,837.37..18,837.38 rows=1 width=12) (actual time=218.935..219.195 rows=3,929 loops=1)

  • Sort Key: label_links.label_id, (date(merge_requests.created_at))
  • Sort Method: quicksort Memory: 281kB
3. 2.251 217.407 ↓ 3,929.0 3,929 1

Nested Loop Left Join (cost=1,266.77..18,837.36 rows=1 width=12) (actual time=1.695..217.407 rows=3,929 loops=1)

  • Filter: ((project_features.merge_requests_access_level IS NULL) OR (project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.merge_requests_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
4. 0.393 207.202 ↓ 3,929.0 3,929 1

Nested Loop (cost=1,266.33..18,833.26 rows=1 width=20) (actual time=1.668..207.202 rows=3,929 loops=1)

5. 1.180 136.889 ↓ 17.1 6,992 1

Nested Loop (cost=1,265.77..18,530.83 rows=410 width=16) (actual time=0.751..136.889 rows=6,992 loops=1)

6. 0.111 4.813 ↑ 1.9 606 1

Nested Loop (cost=1,265.21..12,144.98 rows=1,177 width=4) (actual time=0.688..4.813 rows=606 loops=1)

7. 0.045 0.672 ↑ 2.0 65 1

HashAggregate (cost=1,264.77..1,266.08 rows=131 width=4) (actual time=0.646..0.672 rows=65 loops=1)

  • Group Key: namespaces.id
8. 0.627 0.627 ↑ 2.0 65 1

CTE Scan on base_and_descendants namespaces (cost=1,260.52..1,263.14 rows=131 width=4) (actual time=0.026..0.627 rows=65 loops=1)

9.          

CTE base_and_descendants

10. 0.122 0.559 ↑ 2.0 65 1

Recursive Union (cost=0.43..1,260.52 rows=131 width=322) (actual time=0.025..0.559 rows=65 loops=1)

11. 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)
12. 0.025 0.420 ↑ 1.0 13 5

Nested Loop (cost=0.43..125.44 rows=13 width=322) (actual time=0.013..0.084 rows=13 loops=5)

13. 0.005 0.005 ↓ 1.3 13 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.001 rows=13 loops=5)

14. 0.390 0.390 ↑ 1.0 1 65

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.51 rows=1 width=322) (actual time=0.004..0.006 rows=1 loops=65)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 2.212 4.030 ↑ 1.0 9 65

Index Scan using index_projects_on_namespace_id on projects (cost=0.43..82.95 rows=9 width=8) (actual time=0.012..0.062 rows=9 loops=65)

  • Index Cond: (namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[])))
16.          

SubPlan (forIndex Scan)

17. 1.818 1.818 ↑ 1.0 1 606

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..3.59 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=606)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 251
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..608.06 rows=2,038 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 20))
  • Heap Fetches: 0
19. 130.896 130.896 ↑ 1.8 12 606

Index Scan using index_merge_requests_on_target_project_id_and_iid on merge_requests (cost=0.56..5.21 rows=22 width=16) (actual time=0.165..0.216 rows=12 loops=606)

  • Index Cond: (target_project_id = projects.id)
  • Filter: ((created_at <= '2019-08-01 00:00:00+00'::timestamp with time zone) AND (created_at >= '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 99
20. 69.920 69.920 ↑ 1.0 1 6,992

Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.56..0.73 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=6,992)

  • Index Cond: ((target_id = merge_requests.id) AND ((target_type)::text = 'MergeRequest'::text))
  • Filter: (label_id = ANY ('{2731248,10230929,4116705,2492649,2278648,2779806}'::integer[]))
  • Rows Removed by Filter: 4
21. 7.858 7.858 ↑ 1.0 1 3,929

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,929)

  • Index Cond: (projects.id = project_id)
22.          

SubPlan (forNested Loop Left Join)

23. 0.096 0.096 ↑ 1.0 1 48

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..3.59 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=48)

  • Index Cond: ((user_id = 4156052) AND (project_id = projects.id) AND (access_level >= 20))
  • Heap Fetches: 0
24. 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_3 (cost=0.56..608.06 rows=2,038 width=4) (never executed)

  • Index Cond: ((user_id = 4156052) AND (access_level >= 20))
  • Heap Fetches: 0
Planning time : 2.796 ms
Execution time : 220.046 ms