explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pV4h

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3.286 ↑ 1.0 1 1

Aggregate (cost=1,527.23..1,527.24 rows=1 width=8) (actual time=3.286..3.286 rows=1 loops=1)

2. 0.012 3.284 ↑ 2.3 7 1

Nested Loop (cost=1,523.73..1,527.19 rows=16 width=0) (actual time=3.271..3.284 rows=7 loops=1)

3. 0.002 3.260 ↓ 4.0 4 1

Unique (cost=1,523.30..1,523.31 rows=1 width=4) (actual time=3.258..3.260 rows=4 loops=1)

4.          

CTE base_and_descendants

5. 0.480 2.565 ↓ 1.7 228 1

Recursive Union (cost=1.43..1,277.23 rows=131 width=322) (actual time=0.248..2.565 rows=228 loops=1)

6. 0.008 0.433 ↓ 2.0 2 1

Nested Loop Semi Join (cost=1.43..21.06 rows=1 width=322) (actual time=0.232..0.433 rows=2 loops=1)

7. 0.009 0.355 ↓ 7.0 7 1

Nested Loop (cost=0.87..20.27 rows=1 width=326) (actual time=0.128..0.355 rows=7 loops=1)

8. 0.269 0.269 ↓ 7.0 7 1

Index Scan using index_members_on_user_id on members (cost=0.43..16.80 rows=1 width=4) (actual time=0.108..0.269 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
9. 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)
10. 0.000 0.070 ↓ 0.0 0 7

Nested Loop (cost=0.56..0.68 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=7)

11. 0.049 0.049 ↓ 0.0 0 7

Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (cost=0.43..0.51 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=7)

  • Index Cond: (namespace_id = namespaces_2.id)
12. 0.022 0.022 ↑ 1.0 1 2

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

  • Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
  • Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
13. 0.042 1.652 ↓ 2.5 32 7

Nested Loop (cost=0.43..125.35 rows=13 width=322) (actual time=0.025..0.236 rows=32 loops=7)

14. 0.014 0.014 ↓ 3.3 33 7

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

15. 1.596 1.596 ↑ 1.0 1 228

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

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
16. 0.018 3.258 ↓ 7.0 7 1

Sort (cost=246.07..246.08 rows=1 width=4) (actual time=3.258..3.258 rows=7 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 25kB
17. 0.006 3.240 ↓ 7.0 7 1

Nested Loop (cost=0.71..246.06 rows=1 width=4) (actual time=1.107..3.240 rows=7 loops=1)

  • Join Filter: (namespaces.id = projects_1.namespace_id)
18. 0.139 3.178 ↓ 4.0 4 1

Nested Loop (cost=0.27..245.08 rows=1 width=8) (actual time=1.086..3.178 rows=4 loops=1)

19. 2.811 2.811 ↓ 1.7 228 1

CTE Scan on base_and_descendants namespaces (cost=0.00..2.62 rows=131 width=8) (actual time=0.250..2.811 rows=228 loops=1)

20. 0.228 0.228 ↓ 0.0 0 228

Index Scan using index_namespaces_on_custom_project_templates_group_id_and_type on namespaces namespaces_1 (cost=0.27..1.84 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=228)

  • Index Cond: ((custom_project_templates_group_id = namespaces.id) AND ((type)::text = 'Group'::text))
  • Filter: (namespaces.parent_id = id)
21. 0.056 0.056 ↑ 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.012..0.014 rows=2 loops=4)

  • Index Cond: (namespace_id = namespaces_1.custom_project_templates_group_id)
  • Heap Fetches: 0
22. 0.012 0.012 ↑ 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.003..0.003 rows=2 loops=4)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 0
Planning time : 2.814 ms
Execution time : 3.638 ms