explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 398P

Settings
# exclusive inclusive rows x rows loops node
1. 3.012 147.604 ↓ 323.5 1,294 1

HashAggregate (cost=579.42..579.46 rows=4 width=2,126) (actual time=146.862..147.604 rows=1,294 loops=1)

  • Group Key: members.id, members.access_level, members.source_id, members.source_type, members.user_id, members.notification_level, members.type, members.created_at, members.updated_at, members.created_by_id, members.invite_email, members.invite_token, members.invite_accepted_at, members.requested_at, members.expires_at, members.ldap, members.override
  • Buffers: shared hit=1,320 read=107 dirtied=1
  • I/O Timings: read=135.786
2. 0.229 144.592 ↓ 323.5 1,294 1

Append (cost=0.56..579.25 rows=4 width=2,126) (actual time=7.447..144.592 rows=1,294 loops=1)

  • Buffers: shared hit=1,320 read=107 dirtied=1
  • I/O Timings: read=135.786
3. 23.144 23.144 ↓ 48.0 48 1

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..3.58 rows=1 width=168) (actual time=7.446..23.144 rows=48 loops=1)

  • Index Cond: ((members.source_id = 5,754,519) AND ((members.source_type)::text = 'Namespace'::text))
  • Filter: ((members.requested_at IS NULL) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4 read=14 dirtied=1
  • I/O Timings: read=22.906
4. 0.496 121.219 ↓ 415.3 1,246 1

Nested Loop (cost=359.66..575.60 rows=3 width=168) (actual time=114.751..121.219 rows=1,246 loops=1)

  • Buffers: shared hit=1,316 read=93
  • I/O Timings: read=112.881
5. 0.007 4.986 ↑ 21.0 1 1

HashAggregate (cost=352.06..352.27 rows=21 width=4) (actual time=4.985..4.986 rows=1 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared read=4
  • I/O Timings: read=4.882
6. 4.979 4.979 ↑ 21.0 1 1

CTE Scan on base_and_ancestors namespaces (cost=351.38..351.80 rows=21 width=4) (actual time=4.952..4.979 rows=1 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=4.882
7.          

CTE base_and_ancestors

8. 0.016 4.973 ↑ 21.0 1 1

Recursive Union (cost=0.43..351.38 rows=21 width=346) (actual time=4.947..4.973 rows=1 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=4.882
9. 4.933 4.933 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.46 rows=1 width=346) (actual time=4.932..4.933 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared read=4
  • I/O Timings: read=4.882
10. 0.006 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.43..34.75 rows=2 width=346) (actual time=0.023..0.024 rows=0 loops=1)

11. 0.005 0.005 ↑ 10.0 1 1

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

12. 0.013 0.013 ↓ 0.0 0 1

Index Scan using namespaces_pkey on public.namespaces namespaces_2 (cost=0.43..3.46 rows=1 width=346) (actual time=0.013..0.013 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
13. 6.185 115.737 ↓ 1,246.0 1,246 1

Index Scan using index_members_on_source_id_and_source_type on public.members members_1 (cost=7.60..10.63 rows=1 width=168) (actual time=109.759..115.737 rows=1,246 loops=1)

  • Index Cond: ((members_1.source_id = namespaces.id) AND ((members_1.source_type)::text = 'Namespace'::text))
  • Filter: ((members_1.requested_at IS NULL) AND (NOT (hashed SubPlan 1)) AND ((members_1.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=1,316 read=89
  • I/O Timings: read=107.999
14.          

SubPlan (for Index Scan)

15. 0.186 109.552 ↓ 48.0 48 1

Nested Loop (cost=0.99..7.04 rows=1 width=4) (actual time=3.887..109.552 rows=48 loops=1)

  • Buffers: shared hit=121 read=89
  • I/O Timings: read=107.999
16. 0.262 0.262 ↓ 48.0 48 1

Index Scan using index_members_on_source_id_and_source_type on public.members members_2 (cost=0.56..3.58 rows=1 width=4) (actual time=0.022..0.262 rows=48 loops=1)

  • Index Cond: ((members_2.source_id = 5,754,519) AND ((members_2.source_type)::text = 'Namespace'::text))
  • Filter: ((members_2.requested_at IS NULL) AND ((members_2.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=18
17. 109.104 109.104 ↑ 1.0 1 48

Index Only Scan using users_pkey on public.users (cost=0.43..3.45 rows=1 width=4) (actual time=2.273..2.273 rows=1 loops=48)

  • Index Cond: (users.id = members_2.user_id)
  • Heap Fetches: 8
  • Buffers: shared hit=103 read=89
  • I/O Timings: read=107.999