Patch #21608
closed
Project#allowed_to_condition performance
0%
Description
Project allowed to condition is not using index for enabled_modules subquery.
It can cause issues with growing count of projects.
Files
Related issues
Updated by Ondřej Ezr almost 9 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.
Updated by Ondřej Ezr almost 9 years ago
I had gave it quick look and it would be solved by #21611
project_statement is really bad right now
Updated by Go MAEDA almost 9 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".
Updated by Jean-Philippe Lang almost 9 years ago
- Status changed from New to Needs feedback
- Target version deleted (
Candidate for next major release)
The attached patch contains non Redmine code (plugins/easyproject/*). What am I supposed to do with that?
Updated by Ondřej Ezr almost 9 years ago
I am really sorry, I have selected wrong file.
Updated by Go MAEDA almost 9 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.
current trunk
SQL:
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;
Query plan:
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
SQL:
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;
Query plan:
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=?)
Updated by Jean-Philippe Lang almost 9 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;
Updated by Go MAEDA almost 9 years ago
Thanks for deep inspection.
I tested on SQLite3. I will test again on MySQL.
Updated by Jean-Philippe Lang over 8 years ago
- Target version changed from 3.3.0 to Candidate for next major release
More feedback needed before applying this change. Full scans can be a problem but correlated subqueries can be a problem too.
Updated by Jean-Philippe Lang over 8 years ago
- Related to Patch #23196: Speed up Project.allowed_to_condition added
Updated by Jean-Philippe Lang almost 8 years ago
- Status changed from New to Closed
- Assignee set to Jean-Philippe Lang
- Target version changed from Candidate for next major release to 3.4.0
Patch committed.
Updated by Go MAEDA over 7 years ago
- Subject changed from Project allowed to condition performance to Project#allowed_to_condition performance
Updated by Jérôme BATAILLE about 6 years ago
Hi,
I have a question about this patch, isn't it introducing a slight difference ?- Before projects where rejected if they had not the module enabled.
- Now, no result is returned, if no project has the module enabled.
Am I wrong, does it matter ?
Updated by Pavel Rosický about 6 years ago
Jérôme BATAILLE wrote:
Hi,
I have a question about this patch, isn't it introducing a slight difference ?
- Before projects where rejected if they had not the module enabled.
- Now, no result is returned, if no project has the module enabled.
Am I wrong, does it matter ?
Before: project IN (all projects with [enabled module])
After: EXISTS ([enabled_module]) for a project (not all projects)
If you have many projects and enabled modules, the second version should be faster. Results should be equal.
https://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql/24948