Defect #24433

The changeset display is slow when changeset_issues has very many records

Added by Hirokazu Onozato 5 months ago. Updated 5 months ago.

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

0%

Category:Performance
Target version:3.4.0
Resolution:Fixed Affected version:3.3.0

Description

In issue page, the changeset display is slow when changeset_issues has very many records.

The following query is executed.

SELECT
  `changesets`.*
FROM
  `changesets`
  INNER JOIN `repositories`
    ON `repositories`.`id` = `changesets`.`repository_id`
  INNER JOIN `projects`
    ON `projects`.`id` = `repositories`.`project_id`
  INNER JOIN `changesets_issues`
    ON `changesets`.`id` = `changesets_issues`.`changeset_id`
WHERE
  `changesets_issues`.`issue_id` = 1
  ...

There is no INDEX in issue_id of changesets_issues table.

  • db/schema.rb
      create_table "changesets_issues", id: false, force: :cascade do |t|
        t.integer "changeset_id", null: false
        t.integer "issue_id",     null: false
      end
    
      add_index "changesets_issues", ["changeset_id", "issue_id"], name: "changesets_issues_ids", unique: true, using: :btree
    

As a result, this query may be slow if there are a very many records in the changesets_issues table.

When the number of records in the changesets_issues table was about 1 million, it took 2 or 3 seconds to execute this query.
Adding INDEX to issue_id improved the performance of this query to a few milliseconds.

Associated revisions

Revision 16007
Added by Jean-Philippe Lang 5 months ago

Adds an index on issue_id to changesets_issues (#24433).

History

#1 Updated by Toshi MARUYAMA 5 months ago

  • Target version set to 3.4.0

#2 Updated by Jean-Philippe Lang 5 months ago

  • Status changed from New to Resolved
  • Assignee set to Jean-Philippe Lang

Index added in r16007.

Maybe we should also add an index on changeset_id to speed up the display of related issues on the changeset view. Do you have performance issues on the changeset view too?

#3 Updated by Hirokazu Onozato 5 months ago

Thank you for fixing.

Maybe we should also add an index on changeset_id to speed up the display of related issues on the changeset view. Do you have performance issues on the changeset view too?

There is no performance problem in changeset view.
Multiple column INDEX of changesets_id and issue_id seems to be used.

#4 Updated by Jean-Philippe Lang 5 months ago

  • Status changed from Resolved to Closed
  • Resolution set to Fixed

OK, thanks for your feedback.

Also available in: Atom PDF