Patch #38319


Improve assigned_to_role filter performance

Added by Vincent Robert 23 days ago. Updated 12 days ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


In our installation, with plenty of issues and projects, we met a performance issue with the assigned_to_role filter on issues.
The SQL query was very slow when using this filter.
So, here is a patch to make this request a lot faster:

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index eb7684cc2..ec3d5ec62 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -575,8 +575,9 @@ class IssueQuery < Query
       sw = operator == "!" ? 'NOT' : ''
       nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
-      "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
-        " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))" 
+      "(#{nl} (#{Issue.table_name}.assigned_to_id, #{Issue.table_name}.project_id) #{sw} IN (" +
+        " SELECT DISTINCT #{Member.table_name}.user_id, #{Member.table_name}.project_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
+        " WHERE #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))" 

Thank you for considering this change.


issue_query.diff (1.01 KB) issue_query.diff Vincent Robert, 2023-03-02 08:14
Actions #1

Updated by Holger Just 23 days ago

  • Status changed from New to Needs feedback

Thank you for your contribution. Unfortunately though, on a first check of your poatch, I was not able to find significant performance differences between your patched query and the current one.

Could you please describe in more detail what this patch aims to achieve? Please state your database engine and version and show full representative SQL queries of both versions (possibly with anonymized ids if required) along with the EXPLAIN output of both queries and the timing differences you have observed.

Actions #2

Updated by Vincent Robert 23 days ago

Hi Holger. Thanks for your feedback.
I'm using PostgreSQL 13.

Here is a simplified version of the current SQL Query generated by Redmine for the assigned_to_role filter :

SELECT "issues"."id" 
FROM "issues" 
WHERE issues.assigned_to_id IN (SELECT DISTINCT members.user_id
  FROM members, member_roles
  WHERE = member_roles.member_id
  AND issues.project_id = members.project_id
  AND member_roles.role_id IN ('3'))

In my environment, this query returns 25 000 rows, in more than 30 seconds.

Here is the patched query :

SELECT "issues"."id" 
FROM "issues" 
WHERE (issues.assigned_to_id, issues.project_id) IN (SELECT DISTINCT members.user_id, members.project_id
  FROM members, member_roles
  WHERE = member_roles.member_id
  AND member_roles.role_id IN ('3'))

With this new version, I get the same rows (25 000), but it only takes 200 ms.

It seems to me that the current version must execute the subquery for each issue.
The main difference is that with the new patched query, the subquery can be executed only once.
Do you agree?

Actions #3

Updated by Go MAEDA 22 days ago

  • Target version changed from 5.0.5 to 5.0.6
Actions #4

Updated by Vincent Robert 12 days ago

  • Status changed from Needs feedback to New

Also available in: Atom PDF