Redmine

...will get executed for each of the issues found in the project hierarchy.

I don't get it. In which case the subquery would be executed for each issue in the project hierarchy?
Can you give your DBMS and the numbers of projects/issues you're doing your tests with that result in >10 sec responses, I'd really like to reproduce this performance issue before making any changes. A different one was proposed in #21608 and it would be interesting to compare these two solutions VS. the current implementation.

We had the same performance issue and this patch solved the problem for us.

I tried the patch too in our instance and the results are impressive.

Some details about our instance:
- MySQL
- 5289 open issues from a total of 49043
- 166 active projects from a total of 209 projects (13 closed and 30 archived).

I made two basic tests in the global issues page:
1. default query that returns only the open issues:
Before the patch: ~ 8 sec
With the patch applied: ~ 1.4 sec

2. a custom query with all open issues, assigned to some members, project is not a specific one and group by project
Before the patch: ~ 13 sec (some days ago I tried to investigate this slow response and I removed all the plugins from our instance and the result was the same).
With the patch applied: ~ 1.6 sec

Thanks for your feedback!

I've made a few tests with MySQL 5.7 vs PostgreSQL 9.3 with a test database:
2 000 projects, 200 000 issues (100 in each project), 1 000 users
I was logged as a non-admin user who is a member of 300 projects.

I got fast response times with both on projects#show but PostgreSQL behaves much better on the cross project issue list: PG ~ 0.4s / MySQL ~ 6.1s. With the patch applied, I get better response times with MySQL: PG ~ 0.4s (no change) / MySQL ~ 4.2s

The problem is that PostgreSQL gets slower on pagination with the patch applied: page 50 takes 0.4s without the patch (same response time as the first page) but takes 1.6s with the patch applied.

I've also tested the patch proposed in #21608: PG ~ 0.4s (no change) / MySQL ~ 3.3s. And here, the pagination is not affected with PostgreSQL.

So at the and, #21608 seems to behave better, could you guys give it a try too? I don't really intend to commit a patch that would affect PostgreSQL performance.

I created a script to create testing data: https://gist.github.com/alexandermeindl/ed6ae8a199f1b4869ca0f581b9d94f16#file-sample_data-rb. On my laptop this take some hours to run.

My first impression is, that everything runs much smother with PostgreSQL (I only had experience with Redmine and MySQL till now). I've to recreate the test data to get meaningful results. Maybe it is a good idea, to run the tests with enabled and disabled caching.

Alexander Meindl wrote:

I created a script to create testing data: https://gist.github.com/alexandermeindl/ed6ae8a199f1b4869ca0f581b9d94f16#file-sample_data-rb. On my laptop this take some hours to run.

Yes, creating thousands of records using models can be slow. I've made a script too, but using some raw insert queries to do it much faster. I will add it the repository soon.

I made the same two tests using the patch from #21608 and the results are almost the same with the one obtained with the patch from this ticket.