explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UCt3

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 119,754.291 ↑ 1.0 1 1

Aggregate (cost=25,161,619.16..25,161,619.17 rows=1 width=8) (actual time=119,754.291..119,754.291 rows=1 loops=1)

  • Buffers: shared dirtied=29,223 hit=10,913,509 read=182,784
2. 0.048 119,754.276 ↑ 686.3 3 1

Nested Loop (cost=14,861,824.45..25,161,614.01 rows=2,059 width=0) (actual time=114,919.120..119,754.276 rows=3 loops=1)

  • Buffers: shared dirtied=29,223 hit=10,913,509 read=182,784
3. 0.003 119,753.120 ↑ 65.5 2 1

Unique (cost=14,861,824.01..25,161,138.74 rows=131 width=4) (actual time=114,919.021..119,753.120 rows=2 loops=1)

  • Buffers: shared dirtied=29,223 hit=10,913,502 read=182,782
4.          

CTE base_and_descendants

5. 0.509 122.538 ↓ 1.8 236 1

Recursive Union (cost=0.87..1,680.84 rows=131 width=314) (actual time=37.003..122.538 rows=236 loops=1)

  • Buffers: shared dirtied=12 hit=966 read=78
6. 0.056 116.989 ↓ 7.0 7 1

Nested Loop (cost=0.87..25.45 rows=1 width=314) (actual time=36.984..116.989 rows=7 loops=1)

  • Buffers: shared dirtied=12 hit=28 read=74
7. 102.744 102.744 ↓ 7.0 7 1

Index Scan using index_members_on_user_id on public.members (cost=0.43..20.99 rows=1 width=4) (actual time=32.923..102.744 rows=7 loops=1)

  • Index Cond: (members.user_id = 2,535,118)
  • Filter: ((members.requested_at IS NULL) AND (members.access_level >= 30) AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 54
  • Buffers: shared dirtied=7 read=64
8. 14.189 14.189 ↑ 1.0 1 7

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=314) (actual time=2.023..2.027 rows=1 loops=7)

  • Index Cond: (namespaces_2.id = members.source_id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared dirtied=5 hit=28 read=10
9. 0.049 5.040 ↓ 2.5 33 7

Nested Loop (cost=0.43..165.28 rows=13 width=314) (actual time=0.424..0.720 rows=33 loops=7)

  • Buffers: shared hit=938 read=4
10. 0.035 0.035 ↓ 3.4 34 7

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

11. 4.956 4.956 ↑ 1.0 1 236

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_3 (cost=0.43..16.50 rows=1 width=314) (actual time=0.019..0.021 rows=1 loops=236)

  • Index Cond: (namespaces_3.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_3.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=938 read=4
12. 0.148 119,753.117 ↑ 65.5 2 1

Merge Join (cost=14,860,143.17..25,159,457.56 rows=131 width=4) (actual time=114,919.020..119,753.117 rows=2 loops=1)

  • Buffers: shared dirtied=29,223 hit=10,913,502 read=182,782
13. 0.249 119,629.876 ↑ 1.8 110 1

Unique (cost=14,860,135.94..25,159,445.87 rows=200 width=4) (actual time=102,978.477..119,629.876 rows=110 loops=1)

  • Buffers: shared dirtied=29,211 hit=10,912,536 read=182,704
14.          

CTE base_and_descendants

15. 69.664 82,139.225 ↑ 1,753.0 20,578 1

Recursive Union (cost=109,071.68..8,624,465.97 rows=36,072,316 width=314) (actual time=1,448.795..82,139.225 rows=20,578 loops=1)

  • Buffers: shared dirtied=7,749 hit=2,561,241 read=95,049
16. 427.580 76,883.608 ↑ 126.9 2,168 1

Hash Join (cost=109,071.68..209,465.13 rows=275,116 width=314) (actual time=1,448.742..76,883.608 rows=2,168 loops=1)

  • Hash Cond: (namespaces_4.id = gitlab_subscriptions.namespace_id)
  • Buffers: shared dirtied=7,184 hit=118,779 read=93,794
17. 75,042.173 75,042.173 ↑ 1.0 1,054,552 1

Index Scan using index_namespaces_on_type_partial on public.namespaces namespaces_4 (cost=0.43..94,562.41 rows=1,055,542 width=314) (actual time=1.035..75,042.173 rows=1,054,552 loops=1)

  • Index Cond: ((namespaces_4.type)::text = 'Group'::text)
  • Buffers: shared dirtied=6,995 hit=117,320 read=92,247
18. 4.279 1,413.855 ↑ 382.1 3,793 1

Hash (cost=90,954.98..90,954.98 rows=1,449,302 width=4) (actual time=1,413.855..1,413.855 rows=3,793 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 16,518kB
  • Buffers: shared dirtied=189 hit=1,459 read=1,547
19. 2.425 1,409.576 ↑ 382.0 3,794 1

Nested Loop (cost=0.43..90,954.98 rows=1,449,302 width=4) (actual time=6.901..1,409.576 rows=3,794 loops=1)

  • Buffers: shared dirtied=189 hit=1,459 read=1,547
20. 0.059 0.059 ↑ 1.0 2 1

Seq Scan on public.plans (cost=0.00..4.06 rows=2 width=4) (actual time=0.046..0.059 rows=2 loops=1)

  • Filter: ((plans.name)::text = ANY ('{silver,gold}'::text[]))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=1
21. 1,407.092 1,407.092 ↑ 477.5 1,897 2

Index Scan using index_gitlab_subscriptions_on_hosted_plan_id on public.gitlab_subscriptions (cost=0.43..36,417.41 rows=905,805 width=8) (actual time=3.430..703.546 rows=1,897 loops=2)

  • Index Cond: (gitlab_subscriptions.hosted_plan_id = plans.id)
  • Buffers: shared dirtied=189 hit=1,458 read=1,547
22. 203.427 5,185.953 ↑ 1,749.6 2,046 9

Merge Join (cost=349,282.32..769,355.45 rows=3,579,720 width=314) (actual time=237.331..576.217 rows=2,046 loops=9)

  • Buffers: shared dirtied=565 hit=2,442,462 read=1,255
23. 4,965.759 4,965.759 ↑ 3.8 277,586 9

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_5 (cost=0.43..363,738.91 rows=1,055,542 width=314) (actual time=0.564..551.751 rows=277,586 loops=9)

  • Filter: ((namespaces_5.type)::text = 'Group'::text)
  • Rows Removed by Filter: 240
  • Buffers: shared dirtied=565 hit=2,442,462 read=1,255
24. 12.618 16.767 ↑ 692.1 3,975 9

Sort (cost=349,281.89..356,159.79 rows=2,751,160 width=4) (actual time=1.160..1.863 rows=3,975 loops=9)

  • Sort Key: base_and_descendants_1.id
  • Sort Method: quicksort Memory: 25kB
25. 4.149 4.149 ↑ 1,203.5 2,286 9

WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..55,023.20 rows=2,751,160 width=4) (actual time=0.001..0.461 rows=2,286 loops=9)

26. 994.022 119,629.627 ↑ 1,484,180.0 382 1

Merge Join (cost=6,235,669.97..15,117,588.03 rows=566,956,747 width=4) (actual time=102,978.475..119,629.627 rows=382 loops=1)

  • Buffers: shared dirtied=29,211 hit=10,912,536 read=182,704
27. 36,444.734 36,444.734 ↓ 1.0 10,532,598 1

Index Only Scan using index_projects_on_namespace_id on public.projects projects_1 (cost=0.43..261,055.35 rows=10,532,459 width=4) (actual time=3.007..36,444.734 rows=10,532,598 loops=1)

  • Heap Fetches: 1,401,252
  • Buffers: shared dirtied=21,462 hit=8,351,295 read=87,655
28. 0.126 82,190.871 ↑ 92,493.1 390 1

Materialize (cost=6,235,669.53..6,416,031.11 rows=36,072,316 width=4) (actual time=82,190.506..82,190.871 rows=390 loops=1)

  • Buffers: shared dirtied=7,749 hit=2,561,241 read=95,049
29. 8.678 82,190.745 ↑ 300,602.6 120 1

Sort (cost=6,235,669.53..6,325,850.32 rows=36,072,316 width=4) (actual time=82,190.501..82,190.745 rows=120 loops=1)

  • Sort Key: namespaces_1.custom_project_templates_group_id
  • Sort Method: quicksort Memory: 1,413kB
  • Buffers: shared dirtied=7,749 hit=2,561,241 read=95,049
30. 82,182.067 82,182.067 ↑ 1,753.0 20,578 1

CTE Scan on base_and_descendants namespaces_1 (cost=0.00..721,446.32 rows=36,072,316 width=4) (actual time=1,448.800..82,182.067 rows=20,578 loops=1)

  • Buffers: shared dirtied=7,749 hit=2,561,241 read=95,049
31. 0.216 123.093 ↓ 1.8 236 1

Sort (cost=7.23..7.55 rows=131 width=4) (actual time=122.964..123.093 rows=236 loops=1)

  • Sort Key: namespaces.id
  • Sort Method: quicksort Memory: 36kB
  • Buffers: shared dirtied=12 hit=966 read=78
32. 122.877 122.877 ↓ 1.8 236 1

CTE Scan on base_and_descendants namespaces (cost=0.00..2.62 rows=131 width=4) (actual time=37.010..122.877 rows=236 loops=1)

  • Buffers: shared dirtied=12 hit=966 read=78
33. 1.108 1.108 ↑ 8.0 2 2

Index Only Scan using index_projects_on_namespace_id on public.projects (cost=0.43..3.46 rows=16 width=4) (actual time=0.553..0.554 rows=2 loops=2)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Heap Fetches: 0
  • Buffers: shared hit=7 read=2