explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dIRT

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6.894 ↑ 1.0 1 1

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

2. 0.006 6.890 ↑ 2.7 6 1

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

3. 0.001 6.876 ↓ 4.0 4 1

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

4.          

CTE base_and_descendants

5. 0.484 5.341 ↑ 73.2 234 1

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

6. 0.186 3.891 ↑ 65.5 2 1

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

7.          

CTE base_and_descendants

8. 0.500 2.223 ↓ 1.8 236 1

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

9. 0.005 0.099 ↓ 4.0 4 1

Nested Loop (cost=0.87..20.27 rows=1 width=322) (actual time=0.054..0.099 rows=4 loops=1)

10. 0.050 0.050 ↓ 4.0 4 1

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

  • Index Cond: (user_id = 4156052)
  • Filter: ((requested_at IS NULL) AND (access_level >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 5
11. 0.044 0.044 ↑ 1.0 1 4

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=4)

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

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

13. 0.021 0.021 ↓ 3.4 34 7

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

14. 1.416 1.416 ↑ 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.006 rows=1 loops=236)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
15. 2.525 2.525 ↓ 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.070..2.525 rows=236 loops=1)

16. 0.000 1.180 ↓ 0.0 0 236

Nested Loop (cost=0.56..3.61 rows=1 width=4) (actual time=0.005..0.005 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.057 0.057 ↓ 0.0 0 57

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=57)

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

Nested Loop (cost=0.43..15,747.51 rows=1,700 width=322) (actual time=0.010..0.138 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 6.875 ↓ 6.0 6 1

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

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

Merge Join (cost=1,547.57..4,547.16 rows=1 width=8) (actual time=6.198..6.815 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.046 1.046 ↑ 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.021..1.046 rows=410 loops=1)

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

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

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

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

27. 0.048 0.048 ↑ 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.012 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 : 3.128 ms
Execution time : 7.612 ms