Redmine

Good afternoon

At the moment of making a search with multiple filters at the level of a parent project with a user with multiple roles. It takes a long time. This causes the tool to stop attending and server timeout.

The only solution we have so far is to kill the consultation process not to lower performance Redmine.

Redmine version is 2.5.1, with PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Attached of the queries that gave timeout:

[2016-04-20T17:01:04.149-0500] [glassfish 4.1] [INFO] [] [javax.enterprise.web] [tid: _ThreadID=26 _ThreadName=http-listener-1(2)] [timeMillis: 1461189664149] [levelValue: 800] [[
WebModule[null] ServletContext.log():
ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: cancelando la sentencia debido a una petición del usuario: SELECT COUNT FROM "issues" LEFT OUTER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT OUTER JOIN "journals" ON "journals"."journalized_id" = "issues"."id" AND (journals.private_notes = 'f' OR (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND (projects.id IN (1,4,29,5,7,8) OR projects.id IN (1,4,29,5,7,8) OR projects.id IN (1,303,151,4,34,147,346,348,29,5,7,194,148,8) OR projects.id IN (1,321,352,234,175,257,186,169,109,195,303,304,183,163,247,164,296,167,106,165,173,269,151,95,311,337,30,107,239,4,268,12,339,34,147,79,350,259,168,43,260,177,178,217,180,174,181,346,347,179,348,349,182,229,193,184,29,99,331,5,136,133,166,141,219,46,42,7,305,24,17,35,194,159,241,238,148,117,91,204,8,170,320,28,10,105,82,103,171,9,26) OR projects.id IN (1,303,151,4,147,346,348,29,5,7,194,148,8) OR projects.id IN (1,4,147,346,347,348,349,29,5,7,8) OR projects.id IN (186,58,163,268,339,179,193,136,166,9) OR projects.id IN (303,304,151,95,147,79,346,347,348,349,194,159,148,117) OR projects.id IN (303,304,151,95,240,34,147,79,346,347,348,349,194,159,148,117) OR projects.id IN (303,304,151,95,34,147,79,346,347,348,349,194,159,148,117) OR projects.id IN (311) OR projects.id IN (240,219,235,284) OR projects.id IN (147) OR projects.id IN (348))))) AND "journals"."journalized_type" = 'Issue' WHERE (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.is_public = 't' AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR projects.id IN (1,4,29,5,7,8) OR projects.id IN (1,4,29,5,7,8) OR (projects.id IN (1,303,151,4,34,147,346,348,29,5,7,194,148,8) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR projects.id IN (1,321,352,234,175,257,186,169,109,195,303,304,183,163,247,164,296,167,106,165,173,269,151,95,311,337,30,107,239,4,268,12,339,34,147,79,350,259,168,43,260,177,178,217,180,174,181,346,347,179,348,349,182,229,193,184,29,99,331,5,136,133,166,141,219,46,42,7,305,24,17,35,194,159,241,238,148,117,91,204,8,170,320,28,10,105,82,103,171,9,26) OR (projects.id IN (1,303,151,4,147,346,348,29,5,7,194,148,8) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR projects.id IN (1,4,147,346,347,348,349,29,5,7,8) OR (projects.id IN (317,321,352,60,61,322,66,48,72,49,344,76) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (186,58,163,268,339,179,193,136,166,9) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (303,304,151,95,147,79,346,347,348,349,194,159,148,117) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR projects.id IN (303,304,151,95,240,34,147,79,346,347,348,349,194,159,148,117) OR projects.id IN (303,304,151,95,34,147,79,346,347,348,349,194,159,148,117) OR (projects.id IN (60,61,298,306,145,48,49,76) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (311) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (240,219,235,284) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (147) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR projects.id IN (348) OR (projects.id IN (348) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (10,103) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096)))) OR (projects.id IN (284) AND ((issues.is_private = 'f' OR issues.author_id = 803 OR issues.assigned_to_id IN (803,655,413,272,879,119,346,766,553,884,885,355,970,971,1064,7,331,1096))))))) AND (issues.project_id IN (95)) AND (((LOWER LIKE '%53857+%') OR (LOWER LIKE '%53857+%') OR (LOWER LIKE '%53857+%') OR issues.id IN (SELECT cfs.customized_id FROM custom_values cfs WHERE cfs.customized_type='Issue' AND cfs.customized_id=issues.id AND LOWER LIKE '%53857+%' AND cfs.custom_field_id IN (5,1,3,35,2,11,15,24,16,22,18,50,55,98,20,60,17) AND ((issues.project_id IN (SELECT DISTINCT m.project_id FROM members m INNER JOIN member_roles mr ON mr.member_id = m.id INNER JOIN custom_fields_roles cfr ON cfr.role_id = mr.role_id WHERE m.user_id = 803 AND cfr.custom_field_id = cfs.custom_field_id)) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = cfs.custom_field_id)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = 't' AND ifa.id = cfs.custom_field_id) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = cfs.custom_field_id))))))):
lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb:126:in `search'
app/controllers/search_controller.rb:77:in `index'
app/controllers/search_controller.rb:73:in `index'

]]

Please if I indicate where in the project redmine could optimize the way in which the queries is made.

Thanks for your help