explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kR94

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 12,443.344 ↑ 1.0 1 1

Aggregate (cost=25,123,785.06..25,123,785.07 rows=1 width=8) (actual time=12,443.344..12,443.344 rows=1 loops=1)

2. 0.051 12,443.320 ↑ 299.6 7 1

Nested Loop (cost=14,470,868.76..25,123,779.82 rows=2,097 width=0) (actual time=11,693.568..12,443.320 rows=7 loops=1)

3. 0.011 12,443.221 ↑ 32.8 4 1

Unique (cost=14,470,868.33..25,123,406.17 rows=131 width=4) (actual time=11,693.521..12,443.221 rows=4 loops=1)

4.          

CTE base_and_descendants

5. 0.535 2.403 ↓ 1.8 230 1

Recursive Union (cost=0.87..1,276.43 rows=131 width=322) (actual time=0.249..2.403 rows=230 loops=1)

6. 0.089 0.398 ↓ 7.0 7 1

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

7. 0.239 0.239 ↓ 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.124..0.239 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
8. 0.070 0.070 ↑ 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.009..0.010 rows=1 loops=7)

  • Index Cond: (id = members.source_id)
  • Filter: ((type)::text = 'Group'::text)
9. 0.048 1.470 ↓ 2.5 32 7

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

10. 0.042 0.042 ↓ 3.3 33 7

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

11. 1.380 1.380 ↑ 1.0 1 230

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.006 rows=1 loops=230)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
12. 0.141 12,443.210 ↑ 32.8 4 1

Merge Join (cost=14,469,591.90..25,122,129.41 rows=131 width=4) (actual time=11,693.516..12,443.210 rows=4 loops=1)

  • Merge Cond: (namespaces_1.custom_project_templates_group_id = namespaces.id)
13. 0.166 12,440.014 ↑ 1.6 128 1

Unique (cost=14,469,584.67..25,122,117.72 rows=200 width=4) (actual time=9,926.704..12,440.014 rows=128 loops=1)

14.          

CTE base_and_descendants

15. 86.817 7,349.969 ↑ 1,662.5 22,087 1

Recursive Union (cost=99,698.12..8,153,237.55 rows=36,719,221 width=322) (actual time=15.872..7,349.969 rows=22,087 loops=1)

16. 266.842 1,537.658 ↑ 121.9 2,291 1

Hash Semi Join (cost=99,698.12..204,438.57 rows=279,201 width=322) (actual time=15.853..1,537.658 rows=2,291 loops=1)

  • Hash Cond: (namespaces_4.id = gitlab_subscriptions.namespace_id)
17. 1,263.248 1,263.248 ↓ 1.0 1,108,187 1

Index Scan using index_namespaces_on_type_partial on namespaces namespaces_4 (cost=0.43..98,727.98 rows=1,107,346 width=322) (actual time=0.036..1,263.248 rows=1,108,187 loops=1)

  • Index Cond: ((type)::text = 'Group'::text)
18. 0.997 7.568 ↑ 336.4 4,340 1

Hash (cost=81,447.02..81,447.02 rows=1,460,054 width=4) (actual time=7.568..7.568 rows=4,340 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 16537kB
19. 1.005 6.571 ↑ 336.3 4,341 1

Nested Loop (cost=0.43..81,447.02 rows=1,460,054 width=4) (actual time=0.077..6.571 rows=4,341 loops=1)

20. 0.050 0.050 ↑ 1.0 2 1

Seq Scan on plans (cost=0.00..4.06 rows=2 width=4) (actual time=0.043..0.050 rows=2 loops=1)

  • Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
  • Rows Removed by Filter: 3
21. 5.516 5.516 ↑ 420.5 2,170 2

Index Scan using index_gitlab_subscriptions_on_hosted_plan_id on gitlab_subscriptions (cost=0.43..31,596.45 rows=912,503 width=8) (actual time=0.021..2.758 rows=2,170 loops=2)

  • Index Cond: (hosted_plan_id = plans.id)
22. 198.288 5,725.494 ↑ 1,656.4 2,200 9

Merge Join (cost=354,765.41..721,441.46 rows=3,644,002 width=322) (actual time=288.519..636.166 rows=2,200 loops=9)

  • Merge Cond: (namespaces_5.parent_id = base_and_descendants_1.id)
23. 5,509.053 5,509.053 ↑ 3.7 296,961 9

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_5 (cost=0.43..309,248.09 rows=1,107,346 width=322) (actual time=0.012..612.117 rows=296,961 loops=9)

  • Filter: ((type)::text = 'Group'::text)
  • Rows Removed by Filter: 240
24. 13.338 18.153 ↑ 656.8 4,251 9

Sort (cost=354,764.97..361,745.00 rows=2,792,010 width=4) (actual time=1.132..2.017 rows=4,251 loops=9)

  • Sort Key: base_and_descendants_1.id
  • Sort Method: quicksort Memory: 25kB
25. 4.815 4.815 ↑ 1,137.7 2,454 9

WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..55,840.20 rows=2,792,010 width=4) (actual time=0.001..0.535 rows=2,454 loops=9)

26. 695.077 12,439.848 ↑ 1,303,493.5 451 1

Merge Join (cost=6,316,347.12..15,499,191.26 rows=587,875,563 width=4) (actual time=9,926.697..12,439.848 rows=451 loops=1)

  • Merge Cond: (projects_1.namespace_id = namespaces_1.custom_project_templates_group_id)
27. 4,359.666 4,359.666 ↑ 1.0 10,975,008 1

Index Only Scan using index_projects_on_namespace_id on projects projects_1 (cost=0.43..245,422.31 rows=10,996,308 width=4) (actual time=0.028..4,359.666 rows=10,975,008 loops=1)

  • Heap Fetches: 720790
28. 0.088 7,385.105 ↑ 80,173.0 458 1

Materialize (cost=6,316,346.69..6,499,942.79 rows=36,719,221 width=4) (actual time=7,384.330..7,385.105 rows=458 loops=1)

29. 5.930 7,385.017 ↑ 268,023.5 137 1

Sort (cost=6,316,346.69..6,408,144.74 rows=36,719,221 width=4) (actual time=7,384.326..7,385.017 rows=137 loops=1)

  • Sort Key: namespaces_1.custom_project_templates_group_id
  • Sort Method: quicksort Memory: 1461kB
30. 7,379.087 7,379.087 ↑ 1,662.5 22,087 1

CTE Scan on base_and_descendants namespaces_1 (cost=0.00..734,384.42 rows=36,719,221 width=4) (actual time=15.875..7,379.087 rows=22,087 loops=1)

31. 0.196 3.055 ↓ 1.7 222 1

Sort (cost=7.23..7.55 rows=131 width=4) (actual time=2.941..3.055 rows=222 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 35kB
32. 2.859 2.859 ↓ 1.8 230 1

CTE Scan on base_and_descendants namespaces (cost=0.00..2.62 rows=131 width=4) (actual time=0.256..2.859 rows=230 loops=1)

33. 0.048 0.048 ↑ 8.0 2 4

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

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 0
Planning time : 2.671 ms
Execution time : 12,519.194 ms