explain.depesz.com

PostgreSQL's explain analyze made readable

Result: My75 : Get direct group member's user_id

Settings
# exclusive inclusive rows x rows loops node
1. 0.303 5,242.718 ↓ 40.2 1,166 1

Unique (cost=2,287.55..2,287.70 rows=29 width=4) (actual time=5,242.221..5,242.718 rows=1,166 loops=1)

  • Buffers: shared hit=5265 read=3815 dirtied=131
  • I/O Timings: read=5129.479
2. 3.426 5,242.415 ↓ 59.0 1,711 1

Sort (cost=2,287.55..2,287.62 rows=29 width=4) (actual time=5,242.220..5,242.415 rows=1,711 loops=1)

  • Sort Key: members.user_id
  • Sort Method: quicksort Memory: 129kB
  • Buffers: shared hit=5265 read=3815 dirtied=131
  • I/O Timings: read=5129.479
3. 7.017 5,238.989 ↓ 59.0 1,711 1

Nested Loop (cost=1,639.73..2,286.85 rows=29 width=4) (actual time=231.340..5,238.989 rows=1,711 loops=1)

  • Buffers: shared hit=5262 read=3815 dirtied=131
  • I/O Timings: read=5129.479
4. 1.629 2,531.540 ↓ 61.6 1,786 1

Nested Loop (cost=1,639.29..2,241.99 rows=29 width=4) (actual time=228.319..2,531.540 rows=1,786 loops=1)

  • Buffers: shared hit=507 read=1695 dirtied=103
  • I/O Timings: read=2488.076
5. 0.323 216.683 ↑ 1.5 86 1

HashAggregate (cost=1,638.73..1,640.04 rows=131 width=4) (actual time=216.523..216.683 rows=86 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=186 read=162 dirtied=2
  • I/O Timings: read=212.364
6. 216.360 216.360 ↑ 1.5 86 1

CTE Scan on base_and_descendants namespaces (cost=1,634.48..1,637.10 rows=131 width=4) (actual time=3.964..216.360 rows=86 loops=1)

  • Buffers: shared hit=186 read=162 dirtied=2
  • I/O Timings: read=212.364
7.          

CTE base_and_descendants

8. 0.756 216.045 ↑ 1.5 86 1

Recursive Union (cost=0.43..1,634.48 rows=131 width=323) (actual time=3.956..216.045 rows=86 loops=1)

  • Buffers: shared hit=186 read=162 dirtied=2
  • I/O Timings: read=212.364
9. 3.894 3.894 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..4.45 rows=1 width=323) (actual time=3.893..3.894 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=3.802
10. 0.276 211.395 ↓ 1.3 17 5

Nested Loop (cost=0.43..162.74 rows=13 width=323) (actual time=3.748..42.279 rows=17 loops=5)

  • Buffers: shared hit=185 read=159 dirtied=2
  • I/O Timings: read=208.562
11. 0.075 0.075 ↓ 1.7 17 5

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

12. 211.044 211.044 ↑ 1.0 1 86

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.43..16.24 rows=1 width=323) (actual time=1.326..2.454 rows=1 loops=86)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=185 read=159 dirtied=2
  • I/O Timings: read=208.562
13. 2,313.228 2,313.228 ↓ 21.0 21 86

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..4.58 rows=1 width=8) (actual time=4.413..26.898 rows=21 loops=86)

  • Index Cond: ((members.source_id = namespaces.id) AND ((members.source_type)::text = 'Namespace'::text))
  • Filter: ((members.requested_at IS NULL) AND (members.access_level > 10) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=321 read=1533 dirtied=101
  • I/O Timings: read=2275.712
14. 2,700.432 2,700.432 ↑ 1.0 1 1,786

Index Scan using users_pkey on public.users (cost=0.43..1.54 rows=1 width=4) (actual time=1.505..1.512 rows=1 loops=1,786)

  • Index Cond: (users.id = members.user_id)
  • Filter: ((users.state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4755 read=2120 dirtied=28
  • I/O Timings: read=2641.403