Improve performance of loading OAuth apps and tokens
What does this MR do and why?
Describe in detail what your merge request does and why.
Fixes #364827 (closed)
Resolves an N+1 and also optimizes how this controller and view work. Before we were finding tokens, then finding the applications from those tokens, then once again querying for the tokens associated with that application. What's more, we were also incorrectly scoping the lookup of tokens from applications and potentially showing inaccurate created at date/times.
This area of code will likely need to change again once !89854 (merged) is merged. I commented about this on !89854 (comment 999369746) and we may need to create a follow-up issue.
Database
Query:
SELECT "oauth_access_tokens".* FROM "oauth_access_tokens" WHERE "oauth_access_tokens"."resource_owner_id" = 1 AND "oauth_access_tokens"."revoked_at" IS NULL ORDER BY "oauth_access_tokens"."created_at" DESC
Migration
Migrate
main: == 20220621202616 AddPartialIndexOnOauthAccessTokensRevokedAt: migrating ======
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:oauth_access_tokens, [:resource_owner_id, :created_at], {:name=>"partial_index_resource_owner_id_created_at_token_not_revoked", :where=>"revoked_at IS NULL", :algorithm=>:concurrently})
main: -> 0.0034s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:oauth_access_tokens, [:resource_owner_id, :created_at], {:name=>"partial_index_resource_owner_id_created_at_token_not_revoked", :where=>"revoked_at IS NULL", :algorithm=>:concurrently})
main: -> 0.0013s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:oauth_access_tokens, :resource_owner_id, {:name=>"index_oauth_access_tokens_on_resource_owner_id", :algorithm=>:concurrently})
main: -> 0.0016s
main: -- remove_index(:oauth_access_tokens, {:name=>"index_oauth_access_tokens_on_resource_owner_id", :algorithm=>:concurrently, :column=>:resource_owner_id})
main: -> 0.0022s
main: == 20220621202616 AddPartialIndexOnOauthAccessTokensRevokedAt: migrated (0.0144s)
Rollback
main: == 20220621202616 AddPartialIndexOnOauthAccessTokensRevokedAt: reverting ======
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:oauth_access_tokens, :resource_owner_id, {:name=>"index_oauth_access_tokens_on_resource_owner_id", :algorithm=>:concurrently})
main: -> 0.0065s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:oauth_access_tokens, :resource_owner_id, {:name=>"index_oauth_access_tokens_on_resource_owner_id", :algorithm=>:concurrently})
main: -> 0.0013s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: -- transaction_open?()
main: -> 0.0000s
main: -- index_exists?(:oauth_access_tokens, [:resource_owner_id, :created_at], {:name=>"partial_index_resource_owner_id_created_at_token_not_revoked", :algorithm=>:concurrently})
main: -> 0.0020s
main: -- remove_index(:oauth_access_tokens, {:name=>"partial_index_resource_owner_id_created_at_token_not_revoked", :algorithm=>:concurrently, :column=>[:resource_owner_id, :created_at]})
main: -> 0.0025s
main: == 20220621202616 AddPartialIndexOnOauthAccessTokensRevokedAt: reverted (0.0196s)
Query Plans
Before index changes (this was on GitLab.com):
Sort (cost=83.23..83.26 rows=13 width=172) (actual time=188.258..188.268 rows=36 loops=1)
Sort Key: oauth_access_tokens.created_at DESC
Sort Method: quicksort Memory: 34kB
Buffers: shared hit=4 read=35
I/O Timings: read=187.455 write=0.000
-> Index Scan using index_oauth_access_tokens_on_resource_owner_id on public.oauth_access_tokens (cost=0.43..82.99 rows=13 width=172) (actual time=30.543..188.107 rows=36 loops=1)
Index Cond: (oauth_access_tokens.resource_owner_id = 13356)
Filter: (oauth_access_tokens.revoked_at IS NULL)
Rows Removed by Filter: 1
Buffers: shared hit=1 read=35
I/O Timings: read=187.455 write=0.000
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.