explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fbgn

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

Aggregate (cost=164,121.18..164,121.19 rows=1 width=8) (actual time=7.459..7.459 rows=1 loops=1)

2. 0.007 7.454 ↑ 2.7 6 1

Nested Loop (cost=161,117.54..164,121.14 rows=16 width=0) (actual time=6.729..7.454 rows=6 loops=1)

3. 0.000 7.439 ↓ 4.0 4 1

Unique (cost=161,117.11..164,117.26 rows=1 width=4) (actual time=6.722..7.439 rows=4 loops=1)

4.          

CTE base_and_descendants

5. 0.502 5.752 ↑ 73.2 234 1

Recursive Union (cost=1,275.15..159,569.10 rows=17,131 width=2,907) (actual time=0.248..5.752 rows=234 loops=1)

6. 0.011 4.305 ↑ 65.5 2 1

Nested Loop Semi Join (cost=1,275.15..1,751.42 rows=131 width=2,907) (actual time=0.232..4.305 rows=2 loops=1)

7.          

CTE base_and_descendants

8. 0.550 2.570 ↓ 1.8 236 1

Recursive Union (cost=0.87..1,274.58 rows=131 width=322) (actual time=0.133..2.570 rows=236 loops=1)

9. 0.006 0.326 ↓ 7.0 7 1

Nested Loop (cost=0.87..20.27 rows=1 width=322) (actual time=0.118..0.326 rows=7 loops=1)

10. 0.243 0.243 ↓ 7.0 7 1

Index Scan using index_members_on_user_id on members (cost=0.43..16.81 rows=1 width=4) (actual time=0.099..0.243 rows=7 loops=1)

  • Index Cond: (user_id = 2535118)
  • Filter: ((requested_at IS NULL) AND (access_level >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 58
11. 0.077 0.077 ↑ 1.0 1 7

Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=322) (actual time=0.011..0.011 rows=1 loops=7)

  • Index Cond: (id = members.source_id)
  • Filter: ((type)::text = 'Group'::text)
12. 0.028 1.694 ↓ 2.5 33 7

Nested Loop (cost=0.43..125.17 rows=13 width=322) (actual time=0.026..0.242 rows=33 loops=7)

13. 0.014 0.014 ↓ 3.4 34 7

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.002 rows=34 loops=7)

14. 1.652 1.652 ↑ 1.0 1 236

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_3 (cost=0.43..12.49 rows=1 width=322) (actual time=0.004..0.007 rows=1 loops=236)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 2.878 2.878 ↓ 1.8 236 1

CTE Scan on base_and_descendants namespaces_4 (cost=0.00..2.62 rows=131 width=2,907) (actual time=0.134..2.878 rows=236 loops=1)

16. 0.181 1.416 ↓ 0.0 0 236

Nested Loop (cost=0.56..3.61 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=236)

17. 1.180 1.180 ↓ 0.0 0 236

Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (cost=0.43..3.45 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=236)

  • Index Cond: (namespace_id = namespaces_4.id)
18. 0.055 0.055 ↓ 0.0 0 55

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

  • Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
  • Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
  • Rows Removed by Filter: 1
19. 0.000 0.945 ↑ 51.5 33 7

Nested Loop (cost=0.43..15,747.51 rows=1,700 width=322) (actual time=0.014..0.135 rows=33 loops=7)

20. 0.021 0.021 ↑ 39.7 33 7

WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..26.20 rows=1,310 width=4) (actual time=0.000..0.003 rows=33 loops=7)

21. 0.936 0.936 ↑ 1.0 1 234

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_5 (cost=0.43..11.99 rows=1 width=322) (actual time=0.002..0.004 rows=1 loops=234)

  • Index Cond: (parent_id = base_and_descendants_1.id)
  • Filter: ((type)::text = 'Group'::text)
22. 0.012 7.439 ↓ 6.0 6 1

Nested Loop (cost=1,548.01..4,548.15 rows=1 width=4) (actual time=6.722..7.439 rows=6 loops=1)

  • Join Filter: (namespaces.id = projects_1.namespace_id)
23. 0.062 7.367 ↓ 4.0 4 1

Merge Join (cost=1,547.57..4,547.16 rows=1 width=8) (actual time=6.694..7.367 rows=4 loops=1)

  • Merge Cond: (namespaces_1.custom_project_templates_group_id = namespaces.id)
  • Join Filter: (namespaces.parent_id = namespaces_1.id)
24. 1.170 1.170 ↑ 2,703.7 410 1

Index Scan using index_namespaces_on_custom_project_templates_group_id_and_type on namespaces namespaces_1 (cost=0.27..102.19 rows=1,108,497 width=8) (actual time=0.025..1.170 rows=410 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
25. 0.093 6.135 ↑ 73.8 232 1

Sort (cost=1,547.30..1,590.13 rows=17,131 width=8) (actual time=6.112..6.135 rows=232 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 35kB
26. 6.042 6.042 ↑ 73.2 234 1

CTE Scan on base_and_descendants namespaces (cost=0.00..342.62 rows=17,131 width=8) (actual time=0.252..6.042 rows=234 loops=1)

27. 0.060 0.060 ↑ 8.0 2 4

Index Only Scan using index_projects_on_namespace_id on projects projects_1 (cost=0.43..0.79 rows=16 width=4) (actual time=0.015..0.015 rows=2 loops=4)

  • Index Cond: (namespace_id = namespaces_1.custom_project_templates_group_id)
  • Heap Fetches: 1
28. 0.008 0.008 ↑ 8.0 2 4

Index Only Scan using index_projects_on_namespace_id on projects (cost=0.43..3.72 rows=16 width=4) (actual time=0.002..0.002 rows=2 loops=4)

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