explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A7dh

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 66,021.294 1,738,944.317 ↑ 4.6 4,025,435 1

Unique (cost=24,303,096.04..28,109,490.74 rows=18,567,779 width=1,258) (actual time=1,637,326.015..1,738,944.317 rows=4,025,435 loops=1)

  • Buffers: shared hit=17,342,967 read=7,872,082 dirtied=50,613 written=1
  • I/O Timings: read=1,150,438.872 write=0.109
2. 397,869.376 1,672,923.023 ↓ 1.0 18,615,692 1

Sort (cost=24,303,096.04..24,349,515.49 rows=18,567,779 width=1,258) (actual time=1,637,326.011..1,672,923.023 rows=18,615,692 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.failed_attempts, users.locked_at, users.username, users.can_create_group, users.can_create_team, 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.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.first_name, users.last_name, users.static_object_token, users.role, users.user_type
  • Sort Method: external merge Disk: 9,496,384kB
  • Buffers: shared hit=17,342,817 read=7,872,068 dirtied=50,613 written=1
  • I/O Timings: read=1,150,436.463 write=0.109
3. 23,910.451 1,275,053.647 ↓ 1.0 18,615,692 1

Merge Join (cost=28.19..2,355,774.67 rows=18,567,779 width=1,258) (actual time=0.304..1,275,053.647 rows=18,615,692 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=17,342,637 read=7,872,044 dirtied=50,613 written=1
  • I/O Timings: read=1,150,406.582 write=0.109
4. 695,884.594 695,884.594 ↓ 1.0 6,988,706 1

Index Scan using users_pkey on public.users (cost=0.43..1,013,956.45 rows=6,845,176 width=1,258) (actual time=0.282..695,884.594 rows=6,988,706 loops=1)

  • Filter: (((users.state)::text = 'active'::text) AND ((users.user_type IS NULL) OR (users.user_type = ANY ('{NULL,6,4}'::integer[]))) AND ((users.user_type IS NULL) OR (users.user_type <> ALL ('{2,6,1,3,7,8}'::integer[]))))
  • Rows Removed by Filter: 136,298
  • Buffers: shared hit=2,435,046 read=4,554,092 dirtied=13,418 written=1
  • I/O Timings: read=656,381.740 write=0.109
5. 555,258.602 555,258.602 ↑ 1.0 18,753,475 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..1,104,098.02 rows=18,924,119 width=4) (actual time=0.016..555,258.602 rows=18,753,475 loops=1)

  • Filter: (members.access_level > 10)
  • Rows Removed by Filter: 238,529
  • Buffers: shared hit=14,907,591 read=3,317,952 dirtied=37,195
  • I/O Timings: read=494,024.842