explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SX6h : Get shared group members

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 97.891 ↓ 11.4 57 1

Unique (cost=562.60..562.63 rows=5 width=4) (actual time=97.875..97.891 rows=57 loops=1)

  • Buffers: shared hit=195 read=88 dirtied=4
  • I/O Timings: read=93.975
2. 0.130 97.880 ↓ 11.4 57 1

Sort (cost=562.60..562.62 rows=5 width=4) (actual time=97.874..97.880 rows=57 loops=1)

  • Sort Key: members.user_id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=195 read=88 dirtied=4
  • I/O Timings: read=93.975
3. 0.136 97.750 ↓ 11.4 57 1

Nested Loop (cost=459.10..562.55 rows=5 width=4) (actual time=14.142..97.750 rows=57 loops=1)

  • Buffers: shared hit=192 read=88 dirtied=4
  • I/O Timings: read=93.975
4. 0.026 42.381 ↓ 11.4 57 1

Nested Loop (cost=458.67..554.81 rows=5 width=4) (actual time=14.069..42.381 rows=57 loops=1)

  • Buffers: shared hit=13 read=37 dirtied=1
  • I/O Timings: read=40.685
5. 0.006 6.413 ↑ 21.0 1 1

HashAggregate (cost=458.11..458.32 rows=21 width=4) (actual time=6.412..6.413 rows=1 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=8 read=5 dirtied=1
  • I/O Timings: read=5.210
6. 6.407 6.407 ↑ 21.0 1 1

CTE Scan on base_and_ancestors namespaces (cost=457.43..457.85 rows=21 width=4) (actual time=6.397..6.407 rows=1 loops=1)

  • Buffers: shared hit=8 read=5 dirtied=1
  • I/O Timings: read=5.210
7.          

CTE base_and_ancestors

8. 0.015 6.396 ↑ 21.0 1 1

Recursive Union (cost=0.71..457.43 rows=21 width=323) (actual time=6.388..6.396 rows=1 loops=1)

  • Buffers: shared hit=8 read=5 dirtied=1
  • I/O Timings: read=5.210
9. 0.008 6.375 ↑ 1.0 1 1

Nested Loop (cost=0.71..8.76 rows=1 width=323) (actual time=6.372..6.375 rows=1 loops=1)

  • Buffers: shared hit=8 read=5 dirtied=1
  • I/O Timings: read=5.210
10. 4.245 4.245 ↑ 1.0 1 1

Index Scan using index_group_group_links_on_shared_with_group_id on public.group_group_links (cost=0.28..4.29 rows=1 width=8) (actual time=4.245..4.245 rows=1 loops=1)

  • Index Cond: (group_group_links.shared_with_group_id = 9970)
  • Buffers: shared hit=3 read=3 dirtied=1
  • I/O Timings: read=3.182
11. 2.122 2.122 ↑ 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=2.120..2.122 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = group_group_links.shared_group_id)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=5 read=2
  • I/O Timings: read=2.028
12. 0.002 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.43..44.83 rows=2 width=323) (actual time=0.005..0.006 rows=0 loops=1)

13. 0.003 0.003 ↑ 10.0 1 1

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

14. 0.001 0.001 ↓ 0.0 0 1

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..4.45 rows=1 width=323) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (namespaces_2.id = base_and_ancestors.parent_id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
15. 35.942 35.942 ↓ 57.0 57 1

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=7.655..35.942 rows=57 loops=1)

  • 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: 4
  • Buffers: shared hit=5 read=32
  • I/O Timings: read=35.475
16. 55.233 55.233 ↑ 1.0 1 57

Index Scan using users_pkey on public.users (cost=0.43..1.54 rows=1 width=4) (actual time=0.952..0.969 rows=1 loops=57)

  • Index Cond: (users.id = members.user_id)
  • Filter: ((users.state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=179 read=51 dirtied=3
  • I/O Timings: read=53.290