Redmine

Hello

We noticed a significant impact on performances in the latest versions, after the fix of the issue described in #37255.
Some queries, which previously run in a few milliseconds, now take more than 60 seconds ; specifically when filtering issues by custom value.

Digging into the generated SQL queries, it appears some visibility conditions are duplicated in sub-queries and should, sometimes, be skipped to maintain good performances.

So, here is a patch to address this issue.
Thank you for reviewing it.

diff --git a/app/models/issue_custom_field.rb b/app/models/issue_custom_field.rb
index 285a0d48c..57e2cc8a7 100644
--- a/app/models/issue_custom_field.rb
+++ b/app/models/issue_custom_field.rb
@@ -40,6 +40,7 @@ class IssueCustomField < CustomField
       " OR #{Issue.table_name}.project_id IN (SELECT project_id FROM #{table_name_prefix}custom_fields_projects#{table_name_suffix} WHERE custom_field_id = #{id_column})" 

-    "((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}) AND (#{Issue.visible_condition(user)}))" 
+    "((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}) AND (#{Issue.visible_condition(user, {skip_pre_condition: true})}))" 
   end

   def validate_custom_field
diff --git a/app/models/project.rb b/app/models/project.rb
index 2d2ff5748..0992f4c8f 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -178,18 +178,21 @@ class Project < ActiveRecord::Base
   # * :member => true                   limit the condition to the user projects
   def self.allowed_to_condition(user, permission, options={})
     perm = Redmine::AccessControl.permission(permission)
-    base_statement =
-      if perm && perm.read?
+    if options[:skip_pre_condition]
+      base_statement = "1=1" 
+    else
+      base_statement = if perm && perm.read?
         "#{Project.table_name}.status <> #{Project::STATUS_ARCHIVED}" 
       else
         "#{Project.table_name}.status = #{Project::STATUS_ACTIVE}" 
       end
-    if !options[:skip_pre_condition] && perm && perm.project_module
-      # If the permission belongs to a project module, make sure the module is enabled
-      base_statement +=
-        " AND EXISTS (SELECT 1 AS one FROM #{EnabledModule.table_name} em" \
-          " WHERE em.project_id = #{Project.table_name}.id" \
-          " AND em.name='#{perm.project_module}')" 
+      if perm && perm.project_module
+        # If the permission belongs to a project module, make sure the module is enabled
+        base_statement +=
+          " AND EXISTS (SELECT 1 AS one FROM #{EnabledModule.table_name} em" \
+            " WHERE em.project_id = #{Project.table_name}.id" \
+            " AND em.name='#{perm.project_module}')" 
+      end
     end
     if project = options[:project]
       project_statement = project.project_condition(options[:with_subprojects])