explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iVbK

Settings
# exclusive inclusive rows x rows loops node
1. 2.462 230.022 ↓ 182.3 36,452 1

Nested Loop (cost=1,796.15..1,936.68 rows=200 width=168) (actual time=0.356..230.022 rows=36,452 loops=1)

2. 7.358 81.752 ↓ 180.5 36,452 1

Nested Loop (cost=1,795.72..1,834.74 rows=202 width=168) (actual time=0.340..81.752 rows=36,452 loops=1)

3. 0.217 0.477 ↓ 3.7 191 1

HashAggregate (cost=1,795.16..1,795.68 rows=52 width=4) (actual time=0.312..0.477 rows=191 loops=1)

  • Group Key: projects.id
4. 0.025 0.260 ↓ 3.7 191 1

Nested Loop (cost=1,280.13..1,795.03 rows=52 width=4) (actual time=0.060..0.260 rows=191 loops=1)

5. 0.001 0.048 ↑ 28.0 1 1

Nested Loop (cost=1,279.69..1,767.47 rows=28 width=8) (actual time=0.047..0.048 rows=1 loops=1)

6. 0.003 0.044 ↑ 141.0 1 1

HashAggregate (cost=1,279.26..1,280.67 rows=141 width=4) (actual time=0.043..0.044 rows=1 loops=1)

  • Group Key: namespaces_1.id
7. 0.041 0.041 ↑ 141.0 1 1

CTE Scan on base_and_descendants namespaces_1 (cost=1,274.68..1,277.50 rows=141 width=4) (actual time=0.025..0.041 rows=1 loops=1)

8.          

CTE base_and_descendants

9. 0.006 0.038 ↑ 141.0 1 1

Recursive Union (cost=0.43..1,274.68 rows=141 width=334) (actual time=0.022..0.038 rows=1 loops=1)

10. 0.017 0.017 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=334) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (id = 2735941)
  • Filter: ((type)::text = 'Group'::text)
11. 0.001 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.56..126.84 rows=14 width=334) (actual time=0.015..0.015 rows=0 loops=1)

12. 0.001 0.001 ↑ 10.0 1 1

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

13. 0.013 0.013 ↓ 0.0 0 1

Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_3 (cost=0.56..12.65 rows=1 width=334) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
14. 0.003 0.003 ↑ 1.0 1 1

Index Scan using namespaces_pkey on namespaces (cost=0.43..3.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = namespaces_1.id)
  • Filter: ((type)::text = 'Group'::text)
15. 0.187 0.187 ↓ 11.2 191 1

Index Only Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..0.81 rows=17 width=8) (actual time=0.012..0.187 rows=191 loops=1)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 23
16. 73.917 73.917 ↓ 47.8 191 191

Index Scan using index_members_on_source_id_and_source_type on members (cost=0.56..0.71 rows=4 width=168) (actual time=0.017..0.387 rows=191 loops=191)

  • Index Cond: ((source_id = projects.id) AND ((source_type)::text = 'Project'::text))
  • Filter: ((requested_at IS NULL) AND ((type)::text = 'ProjectMember'::text))
17. 145.808 145.808 ↑ 1.0 1 36,452

Index Scan using users_pkey on users (cost=0.43..0.50 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=36,452)

  • Index Cond: (id = members.user_id)
  • Filter: (((user_type IS NULL) OR (user_type <> 6)) AND ((state)::text = 'active'::text))
Planning time : 2.565 ms
Execution time : 231.787 ms