|Assignee:||Jean-Philippe Lang||% Done:|
Project allowed to condition is not using index for enabled_modules subquery.
It can cause issues with growing count of projects.
#2 Updated by Ondřej Ezr over 2 years ago
It does not solve problem with list of project ids in those queries, but it speeds them up because the list is not the main problem of those queries.
It will resolve #19976 - it is exactly what slowers that query ( altough, subquery instead of list of project ids would help too ).
In #19102 it will speedup the queries for sure, but the list of ids again is caused by project_statement in query.rb and it is not what this issue is targeting, in our company we rewritten a query and solve that, but to extract and generalize that solution we currently have no capacities, but I will try to keep it in mind.
#4 Updated by Go MAEDA over 2 years ago
- Target version set to Candidate for next major release
Ondřej Ezr wrote:
I had gave it quick look and it would be solved by #21611
project_statement is really bad right now
Thanks for investigating. I have set the target version to "Candidate for next major release".
#7 Updated by Go MAEDA over 2 years ago
- Status changed from Needs feedback to New
- Target version set to 3.3.0
The patch passed all tests.
I compared query plans when getting Latest News in Home screen.
SELECT "news".* FROM "news" INNER JOIN "projects" ON "projects"."id" = "news"."project_id" WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='news')) ORDER BY news.created_on DESC LIMIT 5;
There is a full scan of enabled_modules table.
0|0|1|SEARCH TABLE projects USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SCAN TABLE enabled_modules AS em 0|1|0|SEARCH TABLE news USING INDEX news_project_id (project_id=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY
with the patch applied
SELECT "news".* FROM "news" INNER JOIN "projects" ON "projects"."id" = "news"."project_id" WHERE (projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='news')) ORDER BY news.created_on DESC LIMIT 5;
No full scan of enabled_modules table.
0|0|0|SCAN TABLE news USING INDEX index_news_on_created_on 0|1|1|SEARCH TABLE projects USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE enabled_modules AS em USING INDEX enabled_modules_project_id (project_id=?)
#8 Updated by Jean-Philippe Lang over 2 years ago
Which database are you using? I'm using PostgreSQL 9.3 and I get the same query plans before and after the patch. In some case, it uses the enabled_modules_project_id index but not always (eg. for Issue.visible.count). I made these tests against 1k projects and ~10k lines in enabled_modules.
I was able to make Issue.visible.count use the index by doing a JOIN on enabled_modules instead of a subquery but that would require a bit more refactoring to implement. Compared to the above queries, that would be:
SELECT "news".* FROM "news" INNER JOIN "projects" ON "projects"."id" = "news"."project_id" INNER JOIN "enabled_modules" ON "enabled_modules"."project_id" = "projects"."id" AND "enabled_modules"."name" = 'news' WHERE projects.status <> 9 ORDER BY news.created_on DESC LIMIT 5;