"Authorized applications" in settings takes 18.6 seconds to load

[trwnh]

Steps to reproduce the problem

Go to Settings > Account > Authorized applications

Expected behaviour

it loads quickly

Actual behaviour

it loads very slowly (18.6s)

Detailed description

No response

Specifications

mastodon.social

[ClearlyClaire]

I can't reproduce it, but how many registered apps do you have?

[ClearlyClaire]

There's at least one N+1 when doing most_recently_used_access_token

[trwnh]

Somewhere in the neighborhood of about ~130 or so? It's a lot.

[trwnh]

Update: this page load now results in a complete failure. I have no idea how many authorized applications I have, and I am actually completely unable to revoke any of them or do any sort of management on them. Which seems... problematic, to say the least...

[renchap]

I just checked, 114 applications at the moment on this specific account

Doorkeeper.config.application_model.authorized_for(user) is fast for your account, so I guess there is a N+1 query somewhere, triggered by the view.

[connyduck]

I have a test account on mastodon.social that easily has a thousand logins and https://mastodon.social/oauth/authorized_applications returns 503.

[renchap]

There is indeed a N+1 in the view, most_recently_used_access_token is called for every application and is very inefficient (not using an index…). It may also be wrong, as an app could be used by multiple users, and it does not select access tokens for the current user only.

The most recently used token's last usage date can be fetched for all apps at once, using a query like:

last_used_at_by_app = Doorkeeper::AccessToken
  .select('DISTINCT ON (application_id) application_id, last_used_at')
  .where(resource_owner_id: current_user.id)
  .where.not(last_used_at: nil)
  .order(application_id: :desc, last_used_at: :desc)
  .pluck(:application_id, :last_used_at)
  .to_h

This query takes < 1ms for OP's account on mastodon.social.