/api/v4/projects ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout

https://sentry.gitlab.net/gitlab/gitlabcom/issues/1048656/

ActiveRecord::QueryCanceled /api/v4/projects

PG::QueryCanceled: ERROR: canceling statement due to statement timeout : SELECT COUNT(*) FROM (SELECT 1 AS one FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = XXX AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20)) AND "projects"."visibility_level" = 0 AND "projects"."archived" = FALSE AND "projects"."pending_delete" = FALSE LIMIT 10001) subquery_for_count

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

  active_record/connection_adapters/postgresql_adapter.rb:611:in `async_exec_params'
    @connection.exec_params(sql, type_casted_binds)
  active_record/connection_adapters/postgresql_adapter.rb:611:in `block (2 levels) in exec_no_cache'
    @connection.exec_params(sql, type_casted_binds)
  active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
    yield
  active_support/concurrency/share_lock.rb:187:in `yield_shares'
    yield
  active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
    @lock.yield_shares(compatible: [:load]) do
...
(158 additional frame(s) were not displayed)

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
: SELECT COUNT(*) FROM (SELECT  1 AS one FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3067627 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20)) AND "projects"."visibility_level" = 0 AND "projects"."pending_delete" = FALSE LIMIT 10001) subquery_for_count

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
: SELECT COUNT(*) FROM (SELECT  1 AS one FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3067627 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20)) AND "projects"."visibility_level" = 0 AND "projects"."pending_delete" = FALSE LIMIT 10001) subquery_for_count

---

Analysis

• See #37007 (comment 248817930)

• Basically (X AND projects.visibility_level IN (10,20)) AND "projects"."visibility_level" = 0 is forcing the DB to search LOTS of projects that matches the impossible 10,20 AND 0 clause. This goes way beyond the 10001 limit.

  • Without the extra "projects"."visibility_level" = 0 clause, the DB simply stops around 10001 projects.

• PostgreSQL (compared to proprietary databases) does not perform this sort of logical optimization - #37007 (comment 249320194)