explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xUm2

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 209.461 ↑ 1.0 1 1

Aggregate (cost=1,967,764.17..1,967,764.18 rows=1 width=8) (actual time=209.461..209.461 rows=1 loops=1)

2. 0.006 209.456 ↑ 116.1 23 1

Nested Loop (cost=1,967,083.60..1,967,757.50 rows=2,671 width=0) (actual time=209.175..209.456 rows=23 loops=1)

3. 0.005 209.414 ↑ 12.6 12 1

Unique (cost=1,967,083.17..1,967,312.53 rows=151 width=4) (actual time=209.170..209.414 rows=12 loops=1)

4.          

CTE base_and_descendants

5. 1.523 6.154 ↓ 4.3 656 1

Recursive Union (cost=0.87..1,439.36 rows=151 width=347) (actual time=0.062..6.154 rows=656 loops=1)

6. 0.012 0.176 ↓ 8.0 8 1

Nested Loop (cost=0.87..21.88 rows=1 width=347) (actual time=0.054..0.176 rows=8 loops=1)

7. 0.068 0.068 ↓ 8.0 8 1

Index Scan using index_members_on_user_id on members (cost=0.44..18.43 rows=1 width=4) (actual time=0.028..0.068 rows=8 loops=1)

  • Index Cond: (user_id = 4,156,052)
  • Filter: ((requested_at IS NULL) AND (access_level >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 6
8. 0.096 0.096 ↑ 1.0 1 8

Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=347) (actual time=0.012..0.012 rows=1 loops=8)

  • Index Cond: (id = members.source_id)
  • Filter: ((type)::text = 'Group'::text)
9. 0.456 4.455 ↓ 4.8 72 9

Nested Loop (cost=0.56..141.45 rows=15 width=347) (actual time=0.022..0.495 rows=72 loops=9)

10. 0.063 0.063 ↓ 7.3 73 9

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.007 rows=73 loops=9)

11. 3.936 3.936 ↑ 2.0 1 656

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_3 (cost=0.56..14.10 rows=2 width=347) (actual time=0.004..0.006 rows=1 loops=656)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
12. 0.014 209.409 ↑ 58.1 23 1

Nested Loop (cost=1,965,643.81..1,965,869.83 rows=1,336 width=4) (actual time=209.169..209.409 rows=23 loops=1)

13. 0.099 209.299 ↑ 6.3 12 1

Merge Join (cost=1,965,643.37..1,965,646.64 rows=76 width=8) (actual time=209.151..209.299 rows=12 loops=1)

  • Merge Cond: (namespaces.id = namespaces_1.custom_project_templates_group_id)
14. 0.234 7.250 ↓ 4.3 656 1

Sort (cost=8.48..8.86 rows=151 width=4) (actual time=7.197..7.250 rows=656 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 55kB
15. 7.016 7.016 ↓ 4.3 656 1

CTE Scan on base_and_descendants namespaces (cost=0.00..3.02 rows=151 width=4) (actual time=0.065..7.016 rows=656 loops=1)

16. 0.117 201.950 ↓ 2.1 412 1

Sort (cost=1,965,634.89..1,965,635.39 rows=200 width=4) (actual time=201.928..201.950 rows=412 loops=1)

  • Sort Key: namespaces_1.custom_project_templates_group_id
  • Sort Method: quicksort Memory: 44kB
17. 0.320 201.833 ↓ 2.1 413 1

HashAggregate (cost=1,965,625.24..1,965,627.24 rows=200 width=4) (actual time=201.767..201.833 rows=413 loops=1)

  • Group Key: namespaces_1.custom_project_templates_group_id
18. 201.513 201.513 ↑ 647.9 418 1

CTE Scan on base_and_descendants namespaces_1 (cost=1,956,796.89..1,962,240.19 rows=270,804 width=4) (actual time=0.107..201.513 rows=418 loops=1)

  • Filter: (custom_project_templates_group_id IS NOT NULL)
  • Rows Removed by Filter: 18,472
19.          

CTE base_and_descendants

20. 41.675 173.049 ↑ 14.4 18,890 1

Recursive Union (cost=58,833.44..1,956,796.89 rows=272,165 width=2,946) (actual time=0.104..173.049 rows=18,890 loops=1)

21. 0.553 16.193 ↑ 5.6 317 1

Nested Loop Semi Join (cost=58,833.44..70,888.17 rows=1,765 width=2,946) (actual time=0.100..16.193 rows=317 loops=1)

22.          

CTE base_and_ancestors

23. 2.613 7.754 ↑ 3.7 957 1

Recursive Union (cost=0.28..58,832.87 rows=3,530 width=347) (actual time=0.029..7.754 rows=957 loops=1)

24. 2.073 2.073 ↓ 4.2 722 1

Index Scan using index_namespaces_on_custom_project_templates_group_id_and_type on namespaces namespaces_4 (cost=0.28..269.77 rows=170 width=347) (actual time=0.022..2.073 rows=722 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
25. 0.073 3.068 ↑ 3.7 91 4

Nested Loop (cost=0.43..5,849.25 rows=336 width=347) (actual time=0.016..0.767 rows=91 loops=4)

26. 0.124 0.124 ↑ 7.1 239 4

WorkTable Scan on base_and_ancestors (cost=0.00..34.00 rows=1,700 width=4) (actual time=0.001..0.031 rows=239 loops=4)

27. 2.871 2.871 ↓ 0.0 0 957

Index Scan using namespaces_pkey on namespaces namespaces_5 (cost=0.43..3.42 rows=1 width=347) (actual time=0.003..0.003 rows=0 loops=957)

  • Index Cond: (id = base_and_ancestors.parent_id)
  • Filter: ((type)::text = 'Group'::text)
28. 8.941 8.941 ↑ 3.7 957 1

CTE Scan on base_and_ancestors namespaces_6 (cost=0.00..70.60 rows=3,530 width=2,946) (actual time=0.030..8.941 rows=957 loops=1)

29. 0.337 6.699 ↓ 0.0 0 957

Nested Loop (cost=0.56..3.39 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=957)

30. 5.742 5.742 ↑ 1.0 1 957

Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (cost=0.43..3.22 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=957)

  • Index Cond: (namespace_id = namespaces_6.id)
31. 0.620 0.620 ↑ 1.0 1 620

Index Scan using plans_pkey on plans (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=620)

  • Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
  • Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
  • Rows Removed by Filter: 0
32. 16.463 115.181 ↑ 16.0 1,689 11

Nested Loop (cost=0.56..188,046.54 rows=27,040 width=347) (actual time=0.046..10.471 rows=1,689 loops=11)

33. 4.268 4.268 ↑ 10.3 1,717 11

WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..353.00 rows=17,650 width=4) (actual time=0.000..0.388 rows=1,717 loops=11)

34. 94.450 94.450 ↑ 2.0 1 18,890

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_7 (cost=0.56..10.61 rows=2 width=347) (actual time=0.003..0.005 rows=1 loops=18,890)

  • Index Cond: (parent_id = base_and_descendants_1.id)
  • Filter: ((type)::text = 'Group'::text)
35. 0.096 0.096 ↑ 9.0 2 12

Index Only Scan using index_projects_on_namespace_id_and_id on projects projects_1 (cost=0.43..2.76 rows=18 width=4) (actual time=0.008..0.008 rows=2 loops=12)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 1
36. 0.036 0.036 ↑ 9.0 2 12

Index Only Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..2.76 rows=18 width=4) (actual time=0.002..0.003 rows=2 loops=12)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 1
Planning time : 4.000 ms
Execution time : 216.134 ms