explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XhFO

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 294.170 ↑ 1.0 1 1

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

2. 0.088 294.163 ↑ 116.1 23 1

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

3. 0.006 294.075 ↑ 12.6 12 1

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

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 1
4.          

CTE base_and_descendants

5. 1.566 15.978 ↓ 4.3 656 1

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

6. 0.006 0.966 ↓ 8.0 8 1

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

7. 0.472 0.472 ↓ 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.103..0.472 rows=8 loops=1)

  • Index Cond: (user_id = 4,156,052)evel >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 6
8. 0.488 0.488 ↑ 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.061..0.061 rows=1 loops=8)

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

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

10. 0.081 0.081 ↓ 7.3 73 9

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

11. 13.120 13.120 ↑ 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.011..0.020 rows=1 loops=656)

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

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

  • -> Merge Join (cost=1,965,643.37..1965646.64 rows=76 width=8) (actual time=293.463..293.613 rows=12 loops=1)ect_templates_group_id) Merge Cond: (namespaces.id = namespaces_1.custom_proj--More--
13. 0.235 17.111 ↓ 4.3 656 1

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

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

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

15. 0.123 276.403 ↓ 2.1 412 1

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

  • Sort Key: namespaces_1.custom_project_templates_group_id
  • Sort Method: quicksort Memory: 44kB
16. 0.309 276.280 ↓ 2.1 413 1

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

  • Group Key: namespaces_1.custom_project_templates_group_id
17. 275.971 275.971 ↑ 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.270..275.971 rows=418 loops=1)

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

CTE base_and_descendants

19. 208.305 249.547 ↑ 14.4 18,890 1

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

20. 0.910 37.282 ↑ 5.6 317 1

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

21.          

CTE base_and_ancestors

22. 9.537 16.914 ↑ 3.7 957 1

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

  • -> Nested Loop (cost=0.43..5849.25 rows=336 width=347) (actual time=0.022..1.680 rows=91 loops=4)time=0.001..0.041 rows=239 loops=4) -> WorkTable Scan on base_and_ancestors (cost=0.00..34.00 rows=1,700 width=4) (actual --More--
23. 1.635 7.377 ↓ 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.066..7.377 rows=722 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
24. 5.742 5.742 ↓ 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.006..0.006 rows=0 loops=957)

  • Index Cond: (id = base_and_ancestors.parent_id)
  • Filter: ((type)::text = 'Group'::text)
25. 18.189 18.189 ↑ 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.075..18.189 rows=957 loops=1)

26. 0.337 18.183 ↓ 0.0 0 957

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

27. 17.226 17.226 ↑ 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.018..0.018 rows=1 loops=957)

  • Index Cond: (namespace_id = namespaces_6.id)
28. 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
29. 0.000 3.960 ↑ 10.3 1,717 11

Nested Loop (cost=0.56..188046.54 rows=27040 width=347) (actual time=0.044..15.592 rows=1689 loops=11) -> WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..353.00 rows=17,650 width=4) (actual time=0.001..0.360 rows=1,717 loops=11)

30. 151.120 151.120 ↑ 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.004..0.008 rows=1 loops=18,890)

  • Index Cond: (parent_id = base_and_descendants_1.id)
  • Filter: ((type)::text = 'Group'::text)
31. 0.444 0.444 ↑ 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.037..0.037 rows=2 loops=12)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 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.007 rows=2 loops=1--More--
Planning time : 10.703 ms
Execution time : 304.989 ms