Load epic issue metadata info in batches (!54677) · Merge requests · GitLab.org / GitLab

Jan Provaznik requested to merge epic_count_query into master Feb 19, 2021

What does this MR do?

Improves performance of loading epic issue info. The problem is that for a set of epics we need to load all epic issues count/weight including their descendants. If we used a subquery to load epics and its descendants, then postgres used different (slower) query plan if number of epics reached a threshold (~200).

In this case to assure we don't use a slow plan, we load epic ids separately and then load metadata in a separate query. Some other options (and their downsides) are described in #322013 (comment 512616735)

Also a composite index on epic_issues table is added as it improves the query performance (#322013 (comment 512636731))

DB migration

== 20210219111040 AddEpicIssueCompositeIndex: migrating =======================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:epic_issues, [:epic_id, :issue_id], {:name=>"index_epic_issues_on_epic_id_and_issue_id", :algorithm=>:concurrently})
   -> 0.0014s
-- add_index(:epic_issues, [:epic_id, :issue_id], {:name=>"index_epic_issues_on_epic_id_and_issue_id", :algorithm=>:concurrently})
   -> 0.0198s
== 20210219111040 AddEpicIssueCompositeIndex: migrated (0.0216s) ==============

$ bundle exec rake db:rollback
== 20210219111040 AddEpicIssueCompositeIndex: reverting =======================
-- transaction_open?()
   -> 0.0000s
-- indexes(:epic_issues)
   -> 0.0017s
-- remove_index(:epic_issues, {:algorithm=>:concurrently, :name=>"index_epic_issues_on_epic_id_and_issue_id"})
   -> 0.0122s
== 20210219111040 AddEpicIssueCompositeIndex: reverted (0.0144s) ==============

DB queries:

query to get epic base and descendant ids: https://explain.depesz.com/s/Ox5T

WITH RECURSIVE "base_and_descendants" AS (
  (
    SELECT "epics".* FROM "epics" WHERE "epics"."id" IN (77997, 74158, 73492, 112147, 141749, 121866, 121137, 120387, 119419, 115368, 115218, 115206, 100753, 99516, 99191, 93593, 93315, 91701, 91619, 91168, 90629, 86306, 86143, 86142, 86089, 86054, 85863, 85862, 85625, 85321, 85266, 83948, 83697, 81768, 81765, 81683, 81200, 80501, 80405, 80283, 80129, 79944, 78413, 77085, 71915, 71595, 68941, 68939, 68860, 61962, 60608, 59511, 57568, 56284, 55532, 54725, 52335, 47461, 46936, 39679, 38258, 27993, 27184, 26374, 18761, 17742, 17596, 16033, 15762, 14751, 13209, 11939, 11664, 10370, 7882, 6998, 5734, 5643, 941, 502, 59628, 8505, 95814, 91096, 90006, 68280, 10285, 111984, 91552, 90701, 81649, 97672, 91722, 91548, 83491, 82385, 77983, 58232, 57280, 5636, 5602)
  ) UNION (
    SELECT "epics".* FROM "epics", "base_and_descendants" WHERE "epics"."parent_id" = "base_and_descendants"."id"
  )
)
SELECT "id" FROM "base_and_descendants" AS "epics"

Time: 574.910 ms
  - planning: 4.043 ms
  - execution: 570.867 ms (estimated* for prod: 0.023...0.572 s)
    - I/O read: 545.116 ms
    - I/O write: N/A

Shared buffers:
  - hits: 1229 (~9.60 MiB) from the buffer pool
  - reads: 608 (~4.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 10 (~80.00 KiB)
  - writes: 0

query to get epic issue metadata for batch of epic ids (this is still quite slow but by far much fast than the original query): https://explain.depesz.com/s/WQjD

SELECT
  epics.id, epics.iid, epics.parent_id, epics.state_id AS epic_state_id, issues.state_id AS issues_state_id, COUNT(issues) AS issues_count, SUM(COALESCE(issues.weight, 0)) AS issues_weight_sum
FROM
  "epics" LEFT OUTER JOIN "epic_issues" ON "epic_issues"."epic_id" = "epics"."id"
LEFT OUTER JOIN "issues" ON "issues"."id" = "epic_issues"."issue_id"
WHERE "epics"."id" IN (77997, 74158, 73492, 112147, 141749, 121866, 121137, 120387, 119419, 115368, 115218, 115206, 100753, 99516, 99191, 93593, 93315, 91701, 91619, 91168, 90629, 86306, 86143, 86142, 86089, 86054, 85863, 85862, 85625, 85321, 85266, 83948, 83697, 81768, 81765, 81683, 81200, 80501, 80405, 80283, 80129, 79944, 78413, 77085, 71915, 71595, 68941, 68939, 68860, 61962, 60608, 59511, 57568, 56284, 55532, 54725, 52335, 47461, 46936, 39679, 38258, 27993, 27184, 26374, 18761, 17742, 17596, 16033, 15762, 14751, 13209, 11939, 11664, 10370, 7882, 6998, 5734, 5643, 941, 502, 59628, 8505, 95814, 91096, 90006, 68280, 10285, 111984, 91552, 90701, 81649, 97672, 91722, 91548, 83491, 82385, 77983, 58232, 57280, 5636, 5602)
GROUP BY issues.state_id, epics.id, epics.iid, epics.parent_id, epics.state_id LIMIT 100001

Time: 4.313 s
  - planning: 4.969 ms
  - execution: 4.308 s (estimated* for prod: 0.222...4.309 s)
    - I/O read: 4.129 s
    - I/O write: N/A

Shared buffers:
  - hits: 4064 (~31.80 MiB) from the buffer pool
  - reads: 2808 (~21.90 MiB) from the OS file cache, including disk I/O
  - dirtied: 84 (~672.00 KiB)
  - writes: 0

Related to #322013 (closed)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Feb 19, 2021 by Jan Provaznik

Load epic issue metadata info in batches