explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X498

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 12.243 ↑ 1.0 1 1

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

  • Buffers: shared hit=8467
2. 0.233 12.220 ↓ 19.4 136 1

HashAggregate (cost=58,136.15..58,136.22 rows=7 width=2,126) (actual time=12.187..12.220 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=8467
3. 0.017 11.987 ↓ 19.4 136 1

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

  • Buffers: shared hit=8467
4. 1.876 1.876 ↓ 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.051..1.876 rows=134 loops=1)

  • Index Cond: ((members.source_id = 9970) AND ((members.source_type)::text = 'Namespace'::text))
  • Filter: ((members.requested_at IS NULL) AND (members.created_at >= '2019-12-26 12:35:40.208911'::timestamp without time zone) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 1155
  • Buffers: shared hit=1182
5. 0.069 10.094 ↑ 1.0 2 1

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

  • Buffers: shared hit=7285
6. 0.062 1.097 ↑ 1.5 93 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=375
7. 1.035 1.035 ↑ 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.052..1.035 rows=93 loops=1)

  • Buffers: shared hit=375
8.          

CTE base_and_descendants

9. 0.283 0.849 ↑ 1.5 93 1

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

  • Buffers: shared hit=375
10. 0.126 0.126 ↓ 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.017..0.126 rows=38 loops=1)

  • Index Cond: (namespaces_1.parent_id = 9970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=41
11. 0.064 0.440 ↑ 1.0 14 4

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

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

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

13. 0.372 0.372 ↑ 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.003..0.004 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.007 8.928 ↓ 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.096..0.096 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-26 12:35:40.208911'::timestamp without time zone) AND ((members_1.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=6910
15.          

SubPlan (for Index Scan)

16. 0.311 6.921 ↓ 29.1 1,224 1

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

  • Buffers: shared hit=6079
17. 1.454 1.454 ↓ 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.018..1.454 rows=1,289 loops=1)

  • Index Cond: ((members_2.source_id = 9970) 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=1182
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=4897