explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w0de : Warm users_with_descendants.excluding_guests

Settings
# exclusive inclusive rows x rows loops node
1. 0.253 1,638.746 ↑ 1.0 1 1

Aggregate (cost=2,382.51..2,382.52 rows=1 width=8) (actual time=1,638.746..1,638.746 rows=1 loops=1)

  • Buffers: shared hit=41460
2. 78.581 1,638.493 ↓ 14.2 1,196 1

Unique (cost=2,363.61..2,381.46 rows=84 width=1,666) (actual time=1,525.204..1,638.493 rows=1,196 loops=1)

  • Buffers: shared hit=41460
3. 1,463.821 1,559.912 ↓ 283.5 23,812 1

Sort (cost=2,363.61..2,363.82 rows=84 width=1,666) (actual time=1,525.197..1,559.912 rows=23,812 loops=1)

  • Sort Key: users.id, users.email, users.encrypted_password, users.reset_password_token, users.reset_password_sent_at, users.remember_created_at, users.sign_in_count, users.current_sign_in_at, users.last_sign_in_at, users.current_sign_in_ip, users.last_sign_in_ip, users.created_at, users.updated_at, users.name, users.admin, users.projects_limit, users.skype, users.linkedin, users.twitter, users.bio, users.failed_attempts, users.locked_at, users.username, users.can_create_group, users.can_create_team, users.state, users.color_scheme_id, users.password_expires_at, users.created_by_id, users.avatar, users.confirmation_token, users.confirmed_at, users.confirmation_sent_at, users.unconfirmed_email, users.hide_no_ssh_key, users.website_url, users.last_credential_check_at, users.admin_email_unsubscribed_at, users.notification_email, users.hide_no_password, users.password_automatically_set, users.location, users.public_email, users.encrypted_otp_secret, users.encrypted_otp_secret_iv, users.encrypted_otp_secret_salt, users.otp_required_for_login, users.otp_backup_codes, users.dashboard, users.project_view, users.consumed_timestep, users.layout, users.hide_project_limit, users.unlock_token, users.note, users.otp_grace_period_started_at, users.external, users.organization, users.incoming_email_token, users.auditor, users.ghost, users.require_two_factor_authentication_from_group, users.two_factor_grace_period, users.notified_of_own_activity, users.last_activity_on, users.preferred_language, users.email_opted_in, users.email_opted_in_ip, users.email_opted_in_source_id, users.email_opted_in_at, users.theme_id, users.accepted_term_id, users.feed_token, users.private_profile, users.roadmap_layout, users.include_private_contributions, users.commit_email, users.group_view, users.managing_group_id, users.bot_type, users.first_name, users.last_name, users.static_object_token, users.role
  • Sort Method: external merge Disk: 30160kB
  • Buffers: shared hit=41460
4. 12.915 96.091 ↓ 283.5 23,812 1

Nested Loop (cost=2,279.58..2,360.93 rows=84 width=1,666) (actual time=16.215..96.091 rows=23,812 loops=1)

  • Buffers: shared hit=41460
5. 1.388 31.748 ↓ 39.9 1,196 1

Nested Loop (cost=2,279.14..2,329.69 rows=30 width=1,674) (actual time=16.193..31.748 rows=1,196 loops=1)

  • Buffers: shared hit=14073
6. 1.950 17.204 ↓ 39.9 1,196 1

HashAggregate (cost=2,278.71..2,279.01 rows=30 width=8) (actual time=16.181..17.204 rows=1,196 loops=1)

  • Group Key: members_1.user_id
  • Buffers: shared hit=9285
7. 1.274 15.254 ↓ 58.5 1,754 1

Nested Loop (cost=1,629.84..2,278.64 rows=30 width=8) (actual time=1.070..15.254 rows=1,754 loops=1)

  • Buffers: shared hit=9285
8. 0.506 4.860 ↓ 58.8 1,824 1

Nested Loop (cost=1,629.41..2,231.78 rows=31 width=4) (actual time=1.063..4.860 rows=1,824 loops=1)

  • Buffers: shared hit=2250
9. 0.086 1.098 ↑ 1.5 88 1

HashAggregate (cost=1,628.85..1,630.16 rows=131 width=4) (actual time=1.051..1.098 rows=88 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=356
10. 1.012 1.012 ↑ 1.5 88 1

CTE Scan on base_and_descendants namespaces (cost=1,624.59..1,627.21 rows=131 width=4) (actual time=0.043..1.012 rows=88 loops=1)

  • Buffers: shared hit=356
11.          

CTE base_and_descendants

12. 0.242 0.844 ↑ 1.5 88 1

Recursive Union (cost=0.43..1,624.59 rows=131 width=323) (actual time=0.039..0.844 rows=88 loops=1)

  • Buffers: shared hit=356
13. 0.017 0.017 ↑ 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=0.015..0.017 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
14. 0.047 0.585 ↓ 1.3 17 5

Nested Loop (cost=0.43..161.75 rows=13 width=323) (actual time=0.014..0.117 rows=17 loops=5)

  • Buffers: shared hit=352
15. 0.010 0.010 ↓ 1.8 18 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.002 rows=18 loops=5)

16. 0.528 0.528 ↑ 1.0 1 88

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.43..16.15 rows=1 width=323) (actual time=0.004..0.006 rows=1 loops=88)

  • 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=352
17. 3.256 3.256 ↓ 21.0 21 88

Index Scan using index_members_on_source_id_and_source_type on public.members members_1 (cost=0.56..4.58 rows=1 width=8) (actual time=0.009..0.037 rows=21 loops=88)

  • Index Cond: ((members_1.source_id = namespaces.id) AND ((members_1.source_type)::text = 'Namespace'::text))
  • Filter: ((members_1.requested_at IS NULL) AND ((members_1.type)::text = 'GroupMember'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1894
18. 9.120 9.120 ↑ 1.0 1 1,824

Index Scan using users_pkey on public.users users_1 (cost=0.43..1.50 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,824)

  • Index Cond: (users_1.id = members_1.user_id)
  • Filter: ((users_1.state)::text = 'active'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=7035
19. 13.156 13.156 ↑ 1.0 1 1,196

Index Scan using users_pkey on public.users (cost=0.43..1.68 rows=1 width=1,666) (actual time=0.010..0.011 rows=1 loops=1,196)

  • Index Cond: (users.id = members_1.user_id)
  • Buffers: shared hit=4788
20. 51.428 51.428 ↓ 1.2 20 1,196

Index Scan using index_members_on_user_id on public.members (cost=0.43..0.88 rows=16 width=4) (actual time=0.008..0.043 rows=20 loops=1,196)

  • Index Cond: (members.user_id = users.id)
  • Filter: (members.access_level > 10)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=27387