explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I5Af

Settings
# exclusive inclusive rows x rows loops node
1. 0.431 4,289.994 ↓ 41.0 1,190 1

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

  • Buffers: shared hit=5384 read=3885
  • I/O Timings: read=4203.030
2. 3.912 4,289.563 ↓ 60.3 1,748 1

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

  • Sort Key: members.user_id
  • Sort Method: quicksort Memory: 130kB
  • Buffers: shared hit=5384 read=3885
  • I/O Timings: read=4203.030
3. 5.130 4,285.651 ↓ 60.3 1,748 1

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

  • Buffers: shared hit=5381 read=3885
  • I/O Timings: read=4203.030
4. 1.481 2,084.377 ↓ 60.6 1,818 1

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

  • Buffers: shared hit=517 read=1737
  • I/O Timings: read=2056.162
5. 0.278 198.024 ↑ 1.5 88 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=191 read=167
  • I/O Timings: read=192.137
6. 197.746 197.746 ↑ 1.5 88 1

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

  • Buffers: shared hit=191 read=167
  • I/O Timings: read=192.137
7.          

CTE base_and_descendants

8. 4.929 197.384 ↑ 1.5 88 1

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

  • Buffers: shared hit=191 read=167
  • I/O Timings: read=192.137
  • -> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..4.45 rows=1 width=323) (actual time=4.158..4.160 rows=1 loo
  • Index Cond: (id = 9970)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared read=4
  • I/O Timings: read=4.066
9. 192.360 192.455 ↓ 1.3 17 5

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

  • Buffers: shared hit=191 read=163
  • I/O Timings: read=188.071
  • -> 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.095 0.095 ↓ 1.8 18 5

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

  • Index Cond: (parent_id = base_and_descendants.id)
  • Filter: ((type)::text = 'Group'::text)
  • Buffers: shared hit=191 read=163
  • I/O Timings: read=188.071
11. 1,884.872 1,884.872 ↓ 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=2.951..21.419 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=326 read=1570
  • I/O Timings: read=1864.025
12. 2,196.144 2,196.144 ↑ 1.0 1 1,818

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

  • Index Cond: (id = members.user_id)
  • Filter: ((state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4864 read=2148
  • I/O Timings: read=2146.868
Planning time : 236.200 ms
Execution time : 4,292.496 ms