explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uMxy

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 61.453 ↑ 1.0 1 1

Aggregate (cost=147,735.54..147,735.55 rows=1 width=8) (actual time=61.453..61.453 rows=1 loops=1)

2. 0.208 61.445 ↑ 1.2 43 1

HashAggregate (cost=147,734.37..147,734.89 rows=52 width=2,946) (actual time=61.416..61.445 rows=43 loops=1)

  • Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.memb
3. 0.008 61.237 ↓ 1.6 81 1

Append (cost=147,712.25..147,728.00 rows=52 width=2,946) (actual time=61.086..61.237 rows=81 loops=1)

4. 0.236 61.123 ↑ 1.1 43 1

HashAggregate (cost=212.92..213.38 rows=46 width=2,946) (actual time=61.085..61.123 rows=43 loops=1)

  • Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, nam
5.          

CTE base_and_ancestors

6. 2.532 53.289 ↑ 14.2 592 1

Recursive Union (cost=8,792.04..146,047.83 rows=8,391 width=2,946) (actual time=47.595..53.289 rows=592 loops=1)

7. 8.382 48.087 ↓ 1.3 540 1

HashAggregate (cost=8,792.04..8,796.15 rows=411 width=2,946) (actual time=47.588..48.087 rows=540 loops=1)

  • Group Key: namespaces_3.id, namespaces_3.name, namespaces_3.path, namespaces_3.owner_id, namespaces_3.created_at, namespaces_3.updated_at, namespaces_3.type, namespaces_3.descr
8. 0.317 39.705 ↓ 8.2 3,385 1

Append (cost=0.87..8,741.69 rows=411 width=2,946) (actual time=0.047..39.705 rows=3,385 loops=1)

9. 0.007 0.167 ↓ 9.0 9 1

Nested Loop (cost=0.87..22.58 rows=1 width=346) (actual time=0.046..0.167 rows=9 loops=1)

10. 0.061 0.061 ↓ 9.0 9 1

Index Scan using index_members_on_user_id on members (cost=0.44..19.13 rows=1 width=4) (actual time=0.024..0.061 rows=9 loops=1)

  • Index Cond: (user_id = 4,512,390)
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 5
11. 0.099 0.099 ↑ 1.0 1 9

Index Scan using namespaces_pkey on namespaces namespaces_3 (cost=0.43..3.45 rows=1 width=346) (actual time=0.011..0.011 rows=1 loops=9)

  • Index Cond: (id = members.source_id)
  • Filter: ((type)::text = 'Group'::text)
12. 0.484 39.221 ↓ 8.2 3,376 1

Nested Loop (cost=1.44..8,712.95 rows=410 width=346) (actual time=0.063..39.221 rows=3,376 loops=1)

13. 4.447 25.021 ↓ 1.6 3,429 1

Nested Loop (cost=1.01..7,705.59 rows=2,113 width=4) (actual time=0.053..25.021 rows=3,429 loops=1)

  • -> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.57..492.96 rows=2,113 width=4) (ac
  • Index Cond: (user_id = 4,512,390)
  • Heap Fetches: 514
14. 20.574 20.574 ↑ 1.0 1 3,429

Index Scan using projects_pkey on projects (cost=0.43..3.41 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,429)

  • Index Cond: (id = project_authorizations.project_id)
15. 13.716 13.716 ↑ 1.0 1 3,429

Index Scan using namespaces_pkey on namespaces namespaces_4 (cost=0.43..0.48 rows=1 width=346) (actual time=0.004..0.004 rows=1 loops=3,429)

  • Index Cond: (id = projects.namespace_id)
  • Filter: ((type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
16. 0.222 2.670 ↑ 7.0 114 5

Nested Loop (cost=0.43..13,707.98 rows=798 width=346) (actual time=0.009..0.534 rows=114 loops=5)

17. 0.080 0.080 ↑ 34.8 118 5

WorkTable Scan on base_and_ancestors (cost=0.00..82.20 rows=4,110 width=4) (actual time=0.001..0.016 rows=118 loops=5)

18. 2.368 2.368 ↑ 1.0 1 592

Index Scan using namespaces_pkey on namespaces namespaces_5 (cost=0.43..3.32 rows=1 width=346) (actual time=0.004..0.004 rows=1 loops=592)

  • Index Cond: (id = base_and_ancestors.parent_id)
  • Filter: ((type)::text = 'Group'::text)
19.          

CTE base_and_descendants

20. 1.601 5.822 ↑ 1.1 711 1

Recursive Union (cost=0.87..1,451.50 rows=791 width=346) (actual time=0.047..5.822 rows=711 loops=1)

21. 0.005 0.117 ↓ 9.0 9 1

Nested Loop (cost=0.87..22.58 rows=1 width=346) (actual time=0.041..0.117 rows=9 loops=1)

22. 0.058 0.058 ↓ 9.0 9 1

Index Scan using index_members_on_user_id on members members_1 (cost=0.44..19.13 rows=1 width=4) (actual time=0.025..0.058 rows=9 loops=1)

  • Index Cond: (user_id = 4,512,390)
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
  • Rows Removed by Filter: 5
23. 0.054 0.054 ↑ 1.0 1 9

Index Scan using namespaces_pkey on namespaces namespaces_6 (cost=0.43..3.45 rows=1 width=346) (actual time=0.006..0.006 rows=1 loops=9)

  • Index Cond: (id = members_1.source_id)
  • Filter: ((type)::text = 'Group'::text)
24. 0.486 4.104 ↑ 1.0 79 9

Nested Loop (cost=0.43..141.31 rows=79 width=346) (actual time=0.015..0.456 rows=79 loops=9)

25. 0.063 0.063 ↓ 7.9 79 9

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

26. 3.555 3.555 ↑ 8.0 1 711

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_7 (cost=0.43..14.03 rows=8 width=346) (actual time=0.003..0.005 rows=1 loops=711)

  • Index Cond: (parent_id = base_and_descendants.id)
27. 0.011 60.887 ↓ 1.7 80 1

Append (cost=0.00..207.28 rows=46 width=2,946) (actual time=47.677..60.887 rows=80 loops=1)

28. 54.138 54.138 ↑ 1.1 37 1

CTE Scan on base_and_ancestors namespaces (cost=0.00..188.80 rows=42 width=2,946) (actual time=47.677..54.138 rows=37 loops=1)

  • Filter: (parent_id = 9,970)
  • Rows Removed by Filter: 555
29. 6.738 6.738 ↓ 10.8 43 1

CTE Scan on base_and_descendants namespaces_1 (cost=0.00..17.80 rows=4 width=2,946) (actual time=0.440..6.738 rows=43 loops=1)

  • Filter: (parent_id = 9,970)
  • Rows Removed by Filter: 668
30. 0.106 0.106 ↓ 6.3 38 1

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..14.05 rows=6 width=346) (actual time=0.018..0.106 rows=38 loops=1)

  • Index Cond: (parent_id = 9,970)
  • Filter: (visibility_level = ANY ('{10,20}'::integer[]))
  • Rows Removed by Filter: 5
Planning time : 3.834 ms
Execution time : 62.350 ms