Patch #21608

Project#allowed_to_condition performance

Added by Ondřej Ezr over 1 year ago. Updated 8 days ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Performance
Target version:3.4.0

Description

Project allowed to condition is not using index for enabled_modules subquery.
It can cause issues with growing count of projects.

project_query_speedup.patch Magnifier - Project#allowed_to_condition (4.19 KB) Ondřej Ezr, 2015-12-26 04:18

project_visible_condition.diff Magnifier - right patch (953 Bytes) Ondřej Ezr, 2016-01-03 11:31


Related issues

Related to Redmine - Patch #23196: Speed up Project.allowed_to_condition Closed

Associated revisions

Revision 16242
Added by Jean-Philippe Lang 2 months ago

Use EXISTS instead of IN subquery (#21608).

Patch by Ondřej Ezr.

History

#1 Updated by Go MAEDA over 1 year ago

Does this patch resolve #19976 and #19102?

#2 Updated by Ondřej Ezr over 1 year 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.

#3 Updated by Ondřej Ezr over 1 year ago

I had gave it quick look and it would be solved by #21611
project_statement is really bad right now

#4 Updated by Go MAEDA about 1 year 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".

#5 Updated by Jean-Philippe Lang about 1 year 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?

#6 Updated by Ondřej Ezr about 1 year ago

I am really sorry, I have selected wrong file.

#7 Updated by Go MAEDA about 1 year 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=?)

#8 Updated by Jean-Philippe Lang about 1 year 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;

#9 Updated by Go MAEDA about 1 year ago

Thanks for deep inspection.
I tested on SQLite3. I will test again on MySQL.

#10 Updated by Jean-Philippe Lang 11 months 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.

#11 Updated by Jean-Philippe Lang 9 months ago

  • Related to Patch #23196: Speed up Project.allowed_to_condition added

#12 Updated by Jean-Philippe Lang 2 months 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.

#13 Updated by Go MAEDA 8 days ago

  • Subject changed from Project allowed to condition performance to Project#allowed_to_condition performance

Also available in: Atom PDF