Patch #38319
closedOptimize IssueQuery#sql_for_assigned_to_role_field for PostgreSQL performance
Description
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
end
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}))"
end
end
Thank you for considering this change.
Files
Related issues
Updated by Holger Just over 1 year 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.
Updated by Vincent Robert over 1 year 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 members.id = 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 members.id = 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?
Updated by Vincent Robert over 1 year ago
- Status changed from Needs feedback to New
Updated by Go MAEDA about 1 year ago
- Subject changed from Improve assigned_to_role filter performance to Optimize IssueQuery#sql_for_assigned_to_role_field for PostgreSQL performance
- Assignee set to Go MAEDA
Updated by Marius BĂLTEANU 12 months ago
- Related to Defect #39443: Invalid statement query error on MSSQL when role filter is used in issues query added