explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hzg2

Settings
# exclusive inclusive rows x rows loops node
1. 0.244 17.739 ↓ 41.0 1,190 1

Unique (cost=2,278.71..2,278.86 rows=29 width=4) (actual time=17.358..17.739 rows=1,190 loops=1)

  • Buffers: shared hit=9269
2. 0.819 17.495 ↓ 60.3 1,748 1

Sort (cost=2,278.71..2,278.78 rows=29 width=4) (actual time=17.355..17.495 rows=1,748 loops=1)

  • Sort Key: members.user_id
  • Sort Method: quicksort Memory: 130kB
  • Buffers: shared hit=9269
3. 0.408 16.676 ↓ 60.3 1,748 1

Nested Loop (cost=1,629.84..2,278.01 rows=29 width=4) (actual time=0.972..16.676 rows=1,748 loops=1)

  • Buffers: shared hit=9269
4. 0.237 5.360 ↓ 60.6 1,818 1

Nested Loop (cost=1,629.41..2,232.10 rows=30 width=4) (actual time=0.965..5.360 rows=1,818 loops=1)

  • Buffers: shared hit=2254
5. 0.075 0.987 ↑ 1.5 88 1

HashAggregate (cost=1,628.85..1,630.16 rows=131 width=4) (actual time=0.948..0.987 rows=88 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=358
6. 0.912 0.912 ↑ 1.5 88 1

CTE Scan on base_and_descendants namespaces (cost=1,624.59..1,627.21 rows=131 width=4) (actual time=0.039..0.912 rows=88 loops=1)

  • Buffers: shared hit=358
7.          

CTE base_and_descendants

8. 0.224 0.794 ↑ 1.5 88 1

Recursive Union (cost=0.43..1,624.59 rows=131 width=323) (actual time=0.037..0.794 rows=88 loops=1)

  • Buffers: shared hit=358
  • -> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..4.45 rows=1 width=323) (actual time=0.017..0.018 rows=1 loo
  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared hit=4
9. 0.565 0.570 ↓ 1.3 17 5

Nested Loop (cost=0.43..161.75 rows=13 width=323) (actual time=0.013..0.114 rows=17 loops=5)

  • Buffers: shared hit=354
  • -> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_2 (cost=0.43..16.15 rows=1 width=323) (actual
10. 0.005 0.005 ↓ 1.8 18 5

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

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared hit=354
11. 4.136 4.136 ↓ 21.0 21 88

Index Scan using index_members_on_source_id_and_source_type on members (cost=0.56..4.58 rows=1 width=8) (actual time=0.009..0.047 rows=21 loops=88)

  • Index Cond: ((source_id = namespaces.id) AND ((source_type)::text = 'Namespace'::text))
  • Filter: ((requested_at IS NULL) AND (access_level > 10) AND ((type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1896
12. 10.908 10.908 ↑ 1.0 1 1,818

Index Scan using users_pkey on users (cost=0.43..1.52 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,818)

  • Index Cond: (id = members.user_id)
  • Filter: ((state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=7015
Planning time : 1.328 ms
Execution time : 18.139 ms