Skip to content

Speed up find_by when used on finders

Sean McGivern requested to merge 57867-accessing-issue-1-of-gitlab-ce-via-the-api-results-in-500-internal-server-error into master

What does this MR do?

Removes any ordering before calling find_by or find_by! on a finder.

Before:

[ gprd ] production> IssuesFinder.new(nil, project_id: 13083).find_by!(iid: 1)
D, [2019-02-20T11:17:38.458595 #17832] DEBUG -- :   Project Load (3.3ms)  SELECT  "projects".* FROM "projects" WHERE "projects"."id" = 13083 LIMIT 1
D, [2019-02-20T11:17:38.534457 #17832] DEBUG -- :   ProjectFeature Load (0.8ms)  SELECT  "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 13083 LIMIT 1
D, [2019-02-20T11:17:38.553632 #17832] DEBUG -- :   License Load (0.8ms)  SELECT  "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
D, [2019-02-20T11:17:38.556389 #17832] DEBUG -- :    (0.9ms)  SELECT "features"."key" FROM "features"
D, [2019-02-20T11:17:53.566601 #17832] DEBUG -- :   Issue Load (15002.6ms)  SELECT  "issues".* FROM "issues" WHERE (issues.confidential IS NOT TRUE) AND "issues"."project_id" = 13083 AND "issues"."iid" = 1 ORDER BY "issues"."id" DESC LIMIT 1
Traceback (most recent call last):
       16: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:41:in `select_all'
       15: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:62:in `read_using_load_balancer'
       14: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/load_balancer.rb:32:in `read'
       13: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:63:in `block in read_using_load_balancer'
       12: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/query_cache.rb:95:in `select_all'
       11: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
       10: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
        9: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
        8: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:587:in `execute_and_clear'
        7: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `exec_no_cache'
        6: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
        5: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activesupport-5.0.7.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
        4: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
        3: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `block in exec_no_cache'
        2: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
        1: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
ActiveRecord::StatementInvalid (PG::QueryCanceled: ERROR:  canceling statement due to statement timeout)
: SELECT  "issues".* FROM "issues" WHERE (issues.confidential IS NOT TRUE) AND "issues"."project_id" = 13083 AND "issues"."iid" = 1 ORDER BY "issues"."id" DESC LIMIT 1

After:

[ gprd ] production> IssuesFinder.new(nil, project_id: 13083).find_by!(iid: 1)
D, [2019-02-20T11:18:25.877062 #17832] DEBUG -- :   Project Load (2.5ms)  SELECT  "projects".* FROM "projects" WHERE "projects"."id" = 13083 LIMIT 1
D, [2019-02-20T11:18:25.882109 #17832] DEBUG -- :   ProjectFeature Load (0.8ms)  SELECT  "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 13083 LIMIT 1
D, [2019-02-20T11:18:25.890337 #17832] DEBUG -- :   License Load (0.9ms)  SELECT  "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
D, [2019-02-20T11:18:25.893177 #17832] DEBUG -- :    (0.8ms)  SELECT "features"."key" FROM "features"
D, [2019-02-20T11:18:25.896725 #17832] DEBUG -- :   Issue Load (1.1ms)  SELECT  "issues".* FROM "issues" WHERE (issues.confidential IS NOT TRUE) AND "issues"."project_id" = 13083 AND "issues"."iid" = 1 LIMIT 1
D, [2019-02-20T11:18:25.934002 #17832] DEBUG -- :   Project Load (1.2ms)  SELECT  "projects".* FROM "projects" WHERE "projects"."id" = 13083 LIMIT 1
D, [2019-02-20T11:18:25.939056 #17832] DEBUG -- :   ProjectFeature Load (1.0ms)  SELECT  "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 13083 LIMIT 1
D, [2019-02-20T11:18:25.945861 #17832] DEBUG -- :   License Load (0.9ms)  SELECT  "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
D, [2019-02-20T11:18:25.948631 #17832] DEBUG -- :    (0.9ms)  SELECT "features"."key" FROM "features"
D, [2019-02-20T11:18:25.956705 #17832] DEBUG -- :   Route Load (1.1ms)  SELECT  "routes".* FROM "routes" WHERE "routes"."source_id" = 13083 AND "routes"."source_type" = 'Project' LIMIT 1
=> #<Issue id:12379 gitlab-org/gitlab-ce#1>

What are the relevant issue numbers?

Closes #57867 (closed).

Edited by Sean McGivern

Merge request reports