Redmine

The two filter operators "has been" and "has never been" introduced in #38527 can be very slow when there are a large number of rows in the journal_details table.

The attached patch improves the performance by changing the SQL. The patch moves an additional query to outside the EXISTS. In my environment with 150,000 rows of journal_details, a query that took 230 seconds was reduced to 200 milliseconds.

before:

EXISTS (
    SELECT 1
    FROM journals
    INNER JOIN journal_details ON journals.id = journal_details.journal_id
    WHERE (
        (
            journals.private_notes = FALSE
            OR journals.user_id = ?
            OR (projects.status <> 9 AND projects.status <> 10)
        )
        AND journals.journalized_type = 'Issue'
        AND journals.journalized_id = issues.id
        AND journal_details.property = 'attr'
        AND journal_details.prop_key = 'status_id'
        AND journal_details.old_value = '?'
    )
    OR issues.status_id = '?'
)

   (228880.7ms)  SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "issue_sta
tuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS 
one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((NOT EXISTS (SELECT 1 FROM journals
 INNER JOIN journal_details ON journals.id = journal_details.journal_id WHERE ((journals.private_notes = FALSE OR journals.user_id = 
3 OR (projects.status <> 9 AND projects.status <> 10)) AND journals.journalized_type = 'Issue' AND journals.journalized_id = issues.i
d AND journal_details.property = 'attr' AND journal_details.prop_key = 'status_id' AND journal_details.old_value IN ('2')) OR issues.
status_id IN ('2'))) AND projects.lft >= 3 AND projects.rgt <= 8)
  ↳ app/models/issue_query.rb:359:in `issue_count'

after:

EXISTS (
    SELECT 1
    FROM journals
    INNER JOIN journal_details ON journals.id = journal_details.journal_id
    WHERE (
        (
            journals.private_notes = FALSE
            OR journals.user_id = ?
            OR (projects.status <> 9 AND projects.status <> 10)
        )
        AND journals.journalized_type = 'Issue'
        AND journals.journalized_id = issues.id
        AND journal_details.property = 'attr'
        AND journal_details.prop_key = 'status_id'
        AND journal_details.old_value = '?'
    )
)
OR issues.status_id = '?'

   (201.6ms)  SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "issue_status
es" ON "issue_statuses"."id" = "issues"."status_id" WHERE (projects.status <> 9 AND projects.status <> 10 AND EXISTS (SELECT 1 AS one
 FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((NOT (EXISTS (SELECT 1 FROM journals I
NNER JOIN journal_details ON journals.id = journal_details.journal_id WHERE ((journals.private_notes = FALSE OR journals.user_id = 3 
OR (projects.status <> 9 AND projects.status <> 10)) AND journals.journalized_type = 'Issue' AND journals.journalized_id = issues.id 
AND journal_details.property = 'attr' AND journal_details.prop_key = 'status_id' AND journal_details.old_value IN ('2'))) OR issues.s
tatus_id IN ('2'))) AND projects.lft >= 3 AND projects.rgt <= 8)
  ↳ app/models/issue_query.rb:359:in `issue_count'