explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9PCI

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.795 ↑ 1.0 1 1

Aggregate (cost=1,960.52..1,960.53 rows=1 width=8) (actual time=2.795..2.795 rows=1 loops=1)

2. 0.002 2.792 ↑ 506.8 4 1

Nested Loop (cost=1,619.42..1,955.45 rows=2,027 width=0) (actual time=2.782..2.792 rows=4 loops=1)

3. 0.006 2.774 ↑ 65.5 2 1

HashAggregate (cost=1,618.98..1,620.29 rows=131 width=4) (actual time=2.773..2.774 rows=2 loops=1)

  • Group Key: namespaces.custom_project_templates_group_id
4.          

CTE base_and_descendants

5. 0.410 2.464 ↓ 1.8 237 1

Recursive Union (cost=1.43..1,277.44 rows=131 width=322) (actual time=0.196..2.464 rows=237 loops=1)

6. 0.004 0.353 ↓ 2.0 2 1

Nested Loop Semi Join (cost=1.43..20.16 rows=1 width=322) (actual time=0.188..0.353 rows=2 loops=1)

7. 0.001 0.293 ↓ 7.0 7 1

Nested Loop (cost=0.87..19.36 rows=1 width=326) (actual time=0.101..0.293 rows=7 loops=1)

8. 0.215 0.215 ↓ 7.0 7 1

Index Scan using index_members_on_user_id on members (cost=0.43..15.90 rows=1 width=4) (actual time=0.085..0.215 rows=7 loops=1)

  • Index Cond: (user_id = 2,535,118)
  • Filter: ((requested_at IS NULL) AND (access_level >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 55
9. 0.077 0.077 ↑ 1.0 1 7

Index Scan using namespaces_pkey on namespaces namespaces_1 (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.001 0.056 ↓ 0.0 0 7

Nested Loop (cost=0.56..0.68 rows=1 width=4) (actual time=0.008..0.008 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.52 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=7)

  • Index Cond: (namespace_id = namespaces_1.id)
12. 0.006 0.006 ↑ 1.0 1 2

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

  • Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
  • Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
13. 0.028 1.701 ↓ 2.6 34 7

Nested Loop (cost=0.43..125.47 rows=13 width=322) (actual time=0.021..0.243 rows=34 loops=7)

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

15. 1.659 1.659 ↑ 1.0 1 237

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..12.52 rows=1 width=322) (actual time=0.004..0.007 rows=1 loops=237)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
16. 0.084 2.768 ↑ 506.8 4 1

Nested Loop (cost=0.43..336.47 rows=2,027 width=4) (actual time=0.429..2.768 rows=4 loops=1)

17. 2.684 2.684 ↓ 1.8 237 1

CTE Scan on base_and_descendants namespaces (cost=0.00..2.62 rows=131 width=4) (actual time=0.199..2.684 rows=237 loops=1)

18. 0.000 0.000 ↓ 0.0 0 237

Index Only Scan using index_projects_on_namespace_id on projects projects_1 (cost=0.43..2.40 rows=15 width=4) (actual time=0.000..0.000 rows=0 loops=237)

  • Index Cond: (namespace_id = namespaces.custom_project_templates_group_id)
  • Heap Fetches: 0
19. 0.016 0.016 ↑ 7.5 2 2

Index Only Scan using index_projects_on_namespace_id on projects (cost=0.43..2.40 rows=15 width=4) (actual time=0.007..0.008 rows=2 loops=2)

  • Index Cond: (namespace_id = namespaces.custom_project_templates_group_id)
  • Heap Fetches: 0
Planning time : 2.102 ms
Execution time : 2.996 ms