Project

General

Profile

Actions

Patch #38319

closed

Optimize IssueQuery#sql_for_assigned_to_role_field for PostgreSQL performance

Added by Vincent Robert over 1 year ago. Updated about 1 year ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Performance
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:

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

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

Related issues

Related to Redmine - Defect #39443: Invalid statement query error on MSSQL when role filter is used in issues queryClosedMarius BĂLTEANU

Actions
Actions #1

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.

Actions #2

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?

Actions #3

Updated by Go MAEDA over 1 year ago

  • Target version changed from 5.0.5 to 5.0.6
Actions #4

Updated by Vincent Robert over 1 year ago

  • Status changed from Needs feedback to New
Actions #5

Updated by Go MAEDA about 1 year ago

  • Target version changed from 5.0.6 to 5.1.0
Actions #6

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

Committed the patch in r22334 after refactoring the existing code in r22333.

Thank you for your contribution.

Actions #7

Updated by Go MAEDA about 1 year ago

  • Status changed from New to Closed
Actions #8

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
Actions

Also available in: Atom PDF