explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HDja : Current users_with_descendants.excluding_guests

Settings
# exclusive inclusive rows x rows loops node
1. 0.192 25,937.739 ↑ 1.0 1 1

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

  • Buffers: shared hit=17520 read=24002 dirtied=702
  • I/O Timings: read=23968.547
2. 70.776 25,937.547 ↓ 14.2 1,196 1

Unique (cost=2,363.61..2,381.46 rows=84 width=1,666) (actual time=25,824.007..25,937.547 rows=1,196 loops=1)

  • Buffers: shared hit=17520 read=24002 dirtied=702
  • I/O Timings: read=23968.547
3. 1,491.521 25,866.771 ↓ 283.5 23,812 1

Sort (cost=2,363.61..2,363.82 rows=84 width=1,666) (actual time=25,824.005..25,866.771 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=17520 read=24002 dirtied=702
  • I/O Timings: read=23968.547
4. 29.336 24,375.250 ↓ 283.5 23,812 1

Nested Loop (cost=2,279.58..2,360.93 rows=84 width=1,666) (actual time=4,789.255..24,375.250 rows=23,812 loops=1)

  • Buffers: shared hit=17497 read=24002 dirtied=702
  • I/O Timings: read=23968.547
5. 4.726 4,816.430 ↓ 39.9 1,196 1

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

  • Buffers: shared hit=10133 read=3977 dirtied=141
  • I/O Timings: read=4698.492
6. 6.824 4,788.980 ↓ 39.9 1,196 1

HashAggregate (cost=2,278.71..2,279.01 rows=30 width=8) (actual time=4,786.611..4,788.980 rows=1,196 loops=1)

  • Group Key: members_1.user_id
  • Buffers: shared hit=5345 read=3977 dirtied=141
  • I/O Timings: read=4698.492
7. 6.186 4,782.156 ↓ 58.5 1,754 1

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

  • Buffers: shared hit=5345 read=3977 dirtied=141
  • I/O Timings: read=4698.492
8. 1.576 2,198.658 ↓ 58.8 1,824 1

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

  • Buffers: shared hit=516 read=1736 dirtied=63
  • I/O Timings: read=2168.564
9. 0.278 223.594 ↑ 1.5 88 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=189 read=167 dirtied=4
  • I/O Timings: read=219.894
10. 223.316 223.316 ↑ 1.5 88 1

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

  • Buffers: shared hit=189 read=167 dirtied=4
  • I/O Timings: read=219.894
11.          

CTE base_and_descendants

12. 0.784 222.989 ↑ 1.5 88 1

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

  • Buffers: shared hit=189 read=167 dirtied=4
  • I/O Timings: read=219.894
13. 6.445 6.445 ↑ 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=6.444..6.445 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared read=4
  • I/O Timings: read=6.402
14. 0.271 215.760 ↓ 1.3 17 5

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

  • Buffers: shared hit=189 read=163 dirtied=4
  • I/O Timings: read=213.492
15. 0.065 0.065 ↓ 1.8 18 5

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

16. 215.424 215.424 ↑ 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=1.259..2.448 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=189 read=163 dirtied=4
  • I/O Timings: read=213.492
17. 1,973.488 1,973.488 ↓ 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=3.652..22.426 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=327 read=1569 dirtied=59
  • I/O Timings: read=1948.670
18. 2,577.312 2,577.312 ↑ 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=1.398..1.413 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=4829 read=2241 dirtied=78
  • I/O Timings: read=2529.928
19. 22.724 22.724 ↑ 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.017..0.019 rows=1 loops=1,196)

  • Index Cond: (users.id = members_1.user_id)
  • Buffers: shared hit=4788
20. 19,529.484 19,529.484 ↓ 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=1.936..16.329 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=7364 read=20025 dirtied=561
  • I/O Timings: read=19270.055