explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZsn

Settings
# exclusive inclusive rows x rows loops node
1. 2.271 5.701 ↓ 323.5 1,294 1

HashAggregate (cost=579.53..579.57 rows=4 width=2,126) (actual time=5.002..5.701 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,427
2. 0.189 3.430 ↓ 323.5 1,294 1

Append (cost=0.56..579.36 rows=4 width=2,126) (actual time=0.042..3.430 rows=1,294 loops=1)

  • Buffers: shared hit=1,427
3. 0.088 0.088 ↓ 48.0 48 1

Index Scan using index_members_on_source_id_and_source_type on public.members (cost=0.56..3.59 rows=1 width=168) (actual time=0.041..0.088 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.access_level <> 5) AND ((members.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=18
4. 0.424 3.153 ↓ 415.3 1,246 1

Nested Loop (cost=359.66..575.71 rows=3 width=168) (actual time=0.507..3.153 rows=1,246 loops=1)

  • Buffers: shared hit=1,409
5. 0.004 0.060 ↑ 21.0 1 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=4
6. 0.056 0.056 ↑ 21.0 1 1

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

  • Buffers: shared hit=4
7.          

CTE base_and_ancestors

8. 0.011 0.042 ↑ 21.0 1 1

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

  • Buffers: shared hit=4
9. 0.018 0.018 ↑ 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=0.018..0.018 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 hit=4
10. 0.003 0.013 ↓ 0.0 0 1

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

11. 0.002 0.002 ↑ 10.0 1 1

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

12. 0.008 0.008 ↓ 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.008..0.008 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. 2.298 2.669 ↓ 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=0.445..2.669 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.access_level > 5) AND ((members_1.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=1,405
14.          

SubPlan (for Index Scan)

15. 0.032 0.371 ↓ 48.0 48 1

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

  • Buffers: shared hit=210
16. 0.051 0.051 ↓ 48.0 48 1

Index Scan using index_members_on_source_id_and_source_type on public.members members_2 (cost=0.56..3.59 rows=1 width=4) (actual time=0.015..0.051 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.access_level <> 5) AND ((members_2.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=18
17. 0.288 0.288 ↑ 1.0 1 48

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

  • Index Cond: (users.id = members_2.user_id)
  • Heap Fetches: 8
  • Buffers: shared hit=192