explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Fl6

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 12.599 ↑ 1.0 1 1

Aggregate (cost=58,136.31..58,136.32 rows=1 width=8) (actual time=12.598..12.599 rows=1 loops=1)

  • Buffers: shared hit=8,467
2. 0.211 12.577 ↓ 19.4 136 1

HashAggregate (cost=58,136.15..58,136.22 rows=7 width=2,126) (actual time=12.542..12.577 rows=136 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=8,467
3. 0.021 12.366 ↓ 19.4 136 1

Append (cost=0.56..58,135.85 rows=7 width=2,126) (actual time=0.033..12.366 rows=136 loops=1)

  • Buffers: shared hit=8,467
4. 1.813 1.813 ↓ 26.8 134 1

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..286.97 rows=5 width=168) (actual time=0.033..1.813 rows=134 loops=1)

  • Index Cond: ((members.source_id = 9,970) AND ((members.source_type)::text = 'Namespace'::text))
  • Filter: ((members.requested_at IS NULL) AND (members.created_at >= '2019-12-25 12:35:40.208911'::timestamp without time zone) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 1,155
  • Buffers: shared hit=1,182
5. 0.016 10.532 ↑ 1.0 2 1

Nested Loop (cost=2,057.44..57,848.82 rows=2 width=168) (actual time=9.314..10.532 rows=2 loops=1)

  • Buffers: shared hit=7,285
6. 0.067 1.030 ↑ 1.5 93 1

HashAggregate (cost=1,663.01..1,664.42 rows=141 width=4) (actual time=0.997..1.030 rows=93 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=375
7. 0.963 0.963 ↑ 1.5 93 1

CTE Scan on base_and_descendants namespaces (cost=1,658.43..1,661.25 rows=141 width=4) (actual time=0.042..0.963 rows=93 loops=1)

  • Buffers: shared hit=375
8.          

CTE base_and_descendants

9. 0.249 0.812 ↑ 1.5 93 1

Recursive Union (cost=0.43..1,658.43 rows=141 width=326) (actual time=0.036..0.812 rows=93 loops=1)

  • Buffers: shared hit=375
10. 0.095 0.095 ↓ 38.0 38 1

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.43..14.39 rows=1 width=326) (actual time=0.014..0.095 rows=38 loops=1)

  • Index Cond: (namespaces_1.parent_id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=41
11. 0.000 0.468 ↑ 1.0 14 4

Nested Loop (cost=0.43..164.12 rows=14 width=326) (actual time=0.014..0.117 rows=14 loops=4)

  • Buffers: shared hit=334
12. 0.012 0.012 ↓ 2.3 23 4

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

13. 0.465 0.465 ↑ 1.0 1 93

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.43..16.38 rows=1 width=326) (actual time=0.004..0.005 rows=1 loops=93)

  • 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=334
14. 2.273 9.486 ↓ 0.0 0 93

Index Scan using index_members_on_source_id_and_source_type on public.members members_1 (cost=394.43..398.46 rows=1 width=168) (actual time=0.102..0.102 rows=0 loops=93)

  • 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.created_at >= '2019-12-25 12:35:40.208911'::timestamp without time zone) AND ((members_1.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=6,910
15.          

SubPlan (for Index Scan)

16. 0.505 7.213 ↓ 29.1 1,224 1

Nested Loop (cost=0.99..393.77 rows=42 width=4) (actual time=0.032..7.213 rows=1,224 loops=1)

  • Buffers: shared hit=6,079
17. 1.552 1.552 ↓ 30.7 1,289 1

Index Scan using index_members_on_source_id_and_source_type on public.members members_2 (cost=0.56..286.45 rows=42 width=4) (actual time=0.016..1.552 rows=1,289 loops=1)

  • Index Cond: ((members_2.source_id = 9,970) 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=1,182
18. 5.156 5.156 ↑ 1.0 1 1,289

Index Only Scan using users_pkey on public.users (cost=0.43..2.55 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,289)

  • Index Cond: (users.id = members_2.user_id)
  • Heap Fetches: 214
  • Buffers: shared hit=4,897