Optimized issuable label search
What does this MR do?
This MR attempts to optimize all group and project level issuable queries where label filter condition is given.
Current State
Our current label search is based on the labels.title attribute.
Example skeleton query:
SELECT * FROM issues
INNER JOIN label_links ON label_links.target_id = issues.id AND label_links.target_type = 'Issue'
INNER JOIN labels on labels.id=label_links.label_id
WHERE
$COMPLEX_NAMESPACE_QUERY
labels.title = 'bug'The generated query does a double JOIN, which affects the lookup performance significantly.
In case we're looking for two labels, a GROUP BY with HAVING COUNT() = 2 construct is added. Unfortunately the count by state query which is used to render the counts on top is affected by this: forces us to load the grouped resultset in memory and count in ruby. See related code
Additionally the GROUP BY requires us to add the columns referenced in ORDER BY to the GROUP BY clause. This might cause unexpected bugs when a custom ordering is used in the finders: https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/concerns/issuable.rb#L307
This happened a few times:
Different Approach
If we assume that the labels which are assigned to Issuables are findable with the LabelsFinder class, then we can split the query into two part.
- Find all label ids for the given project or group by title. <50ms
- Construct an
EXISTSquery where we only check thelabel_linkstable. In this case joining thelabelsis not necessary.
SELECT * FROM issues
WHERE
$COMPLEX_NAMESPACE_QUERY
EXISTS (SELECT 1 FROM label_links where target_type = 'Issue' AND target_id = issues.target_id AND label_id = 1)For historical reason, one project might have several labels with the same title. For example we have about 20 bug labels in gitlab-org (rare case). This approach also works for that case: LabelsFinder will return all label ids, and we're passing them to the EXISTS query.
When two labels are given, we will add two EXISTS subqueries since it's an AND relationship:
SELECT * FROM issues
WHERE
$COMPLEX_NAMESPACE_QUERY
EXISTS (SELECT 1 FROM label_links where target_type = 'Issue' AND target_id = issues.target_id AND label_id = 1)
AND
EXISTS (SELECT 1 FROM label_links where target_type = 'Issue' AND target_id = issues.target_id AND label_id = 2)In order to have one common way of querying labels, this MR changes the the label queries where the group or project parameter is not given.
In this case we don't get any speed improvements, however this eliminates the GROUP BY clause completely.
Example:
EXISTS (SELECT 1 FROM label_links INNER JOIN labels on labels.id=label_links.label_id where target_type = 'Issue' AND target_id = issues.target_id AND labels.title = '$LABEL_TITLE')Performance comparison
Note: cached execution.
Find issues by one label in gitlab-org (~bug)
Old Count Query:
Query 4.8s
SELECT COUNT(*) AS count_all,
"issues"."state_id" AS issues_state_id
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND "labels"."title" = 'bug'
GROUP BY "issues"."state_id"
Old Paginated Query:
Query 1,4s
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" LEFT JOIN project_features ON projects.id = project_features.project_id LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ( (SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" FROM "base_and_descendants" AS "namespaces") AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4156052 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10, 20)) AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20, 30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4156052 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)))) AND ("issues"."state_id" IN (1)) AND "labels"."title" = 'bug' ORDER BY milestones.due_date IS NULL, milestones.id IS NULL, milestones.due_date DESC, "issues"."id" DESC LIMIT 20 OFFSET 0
New Count Query:
Query, 1.3s
SELECT COUNT(*) AS count_all,
"issues"."state_id" AS issues_state_id
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" in (1924053, 2191076, 2229310, 2251841, 2278648, 2379994, 2414262, 3519299, 3791725, 3892549, 3892770, 3899495, 3969396, 3999541, 3999564, 4007552, 4007738, 4049204, 4057321, 4063693, 4117093, 6190961, 7142293, 7157910, 7256789, 7717873, 7841215, 8761704, 8827899, 8828075, 8828148, 9084503, 10778837, 10778846, 11602405, 12073682, 12932265, 13682327, 15339054)
LIMIT 1))
GROUP BY "issues"."state_id"
New Paginated Query:
Query, 520ms
SELECT "issues".*
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND ("issues"."state_id" IN (1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" IN (1924053, 2191076, 2229310, 2251841, 2278648, 2379994, 2414262, 3519299, 3791725, 3892549, 3892770, 3899495, 3969396, 3999541, 3999564, 4007552, 4007738, 4049204, 4057321, 4063693, 4117093, 6190961, 7142293, 7157910, 7256789, 7717873, 7841215, 8761704, 8827899, 8828075, 8828148, 9084503, 10778837, 10778846, 11602405, 12073682, 12932265, 13682327, 15339054)
LIMIT 1))
ORDER BY milestones.due_date IS NULL,
milestones.id IS NULL,
milestones.due_date DESC,
"issues"."id" DESC
LIMIT 20
OFFSET 0
Find issues by three labels, project level (gitlab) (~bug, QA, Quality)
Old Count Query:
Old Paginated Query:
Query, 870ms
SELECT "issues".*
FROM "issues"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id
WHERE "issues"."project_id" = 278964
AND ("issues"."state_id" IN (1))
AND "labels"."title" IN ('QA',
'Quality',
'bug')
GROUP BY "issues"."id",
"milestones"."id",
"milestones"."due_date"
HAVING (COUNT(DISTINCT labels.title) = 3)
ORDER BY milestones.due_date IS NULL,
milestones.id IS NULL,
milestones.due_date DESC,
"issues"."id" DESC
LIMIT 20
OFFSET 0
New Paginated Query:
Query, 16ms
SELECT "issues".*
FROM "issues"
LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id
WHERE "issues"."project_id" = 278964
AND ("issues"."state_id" IN (1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" in (1924053, 2191076, 2229310, 2251841, 2278648, 2379994, 2414262, 3519299, 3791725, 3892549, 3892770, 3899495, 3969396, 3999541, 3999564, 4007552, 4007738, 4049204, 4057321, 4063693, 4117093, 6190961, 7142293, 7157910, 7256789, 7717873, 7841215, 8761704, 8827899, 8828075, 8828148, 9084503, 10778837, 10778846, 11602405, 12073682, 12932265, 13682327, 15339054)
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 3005495
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 2278656
LIMIT 1))
ORDER BY milestones.due_date IS NULL,
milestones.id IS NULL,
milestones.due_date DESC,
"issues"."id" DESC
LIMIT 20
OFFSET 0
Find issues by three labels in gitlab-org (~bug, QA, Quality)
Old Count Query:
Query, 3s
SELECT COUNT(*) AS count_all,
"issues"."id" AS issues_id,
"issues"."state_id" AS issues_state_id
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNE R
JOIN "label_links" ON "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_fe atures ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."t
ype" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects .id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations. project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND "labels"."title" IN ('QA',
'Quality',
'bug')
GROUP BY "issues"."id",
"issues"."state_id"
HAVING (COUNT(DISTINCT labels.title) = 3)
Old Paginated Query:
Query, 1.4s
SELECT "issues".*
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND ("issues"."state_id" IN (1))
AND "labels"."title" IN ('QA',
'Quality',
'bug')
GROUP BY "issues"."id",
"milestones"."id",
"milestones"."due_date"
HAVING (COUNT(DISTINCT labels.title) = 3)
ORDER BY milestones.due_date IS NULL,
milestones.id IS NULL,
milestones.due_date DESC,
"issues"."id" DESC
LIMIT 20
OFFSET 0
New Count Query:
Query, 64ms
SELECT COUNT(*) AS count_all,
"issues"."state_id" AS issues_state_id
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" in (1924053, 2191076, 2229310, 2251841, 2278648, 2379994, 2414262, 3519299, 3791725, 3892549, 3892770, 3899495, 3969396, 3999541, 3999564, 4007552, 4007738, 4049204, 4057321, 4063693, 4117093, 6190961, 7142293, 7157910, 7256789, 7717873, 7841215, 8761704, 8827899, 8828075, 8828148, 9084503, 10778837, 10778846, 11602405, 12073682, 12932265, 13682327, 15339054)
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 3005495
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 2278656
LIMIT 1))
GROUP BY "issues"."state_id"
New Paginated Query:
Query, 22ms
SELECT "issues".*
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
LEFT OUTER JOIN milestones ON issues.milestone_id = milestones.id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND ("issues"."state_id" IN (1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" in (1924053, 2191076, 2229310, 2251841, 2278648, 2379994, 2414262, 3519299, 3791725, 3892549, 3892770, 3899495, 3969396, 3999541, 3999564, 4007552, 4007738, 4049204, 4057321, 4063693, 4117093, 6190961, 7142293, 7157910, 7256789, 7717873, 7841215, 8761704, 8827899, 8828075, 8828148, 9084503, 10778837, 10778846, 11602405, 12073682, 12932265, 13682327, 15339054)
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 3005495
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" = "issues"."id"
AND "label_links"."label_id" = 2278656
LIMIT 1))
ORDER BY milestones.due_date IS NULL,
milestones.id IS NULL,
milestones.due_date DESC,
"issues"."id" DESC
LIMIT 20
OFFSET 0
Find merge requests by two labels in gitlab-org (feature, databaseapproved)
Old Count Query:
Query, 5.4s
SELECT COUNT(*) AS count_all,
"merge_requests"."id" AS merge_requests_id,
"merge_requests"."state_id" AS merge_requests_state_id
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "label_links" ON "label_links"."target_type" = 'MergeRequest'
AND "label_links"."target_id" = "merge_requests"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20,
30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND "labels"."title" IN ('database::approved',
'feature')
GROUP BY "merge_requests"."id",
"merge_requests"."state_id"
HAVING (COUNT(DISTINCT labels.title) = 2)
Old Paginated Query:
Query, 90ms
SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "label_links" ON "label_links"."target_type" = 'MergeRequest'
AND "label_links"."target_id" = "merge_requests"."id"
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20,
30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND ("merge_requests"."state_id" IN (1))
AND "labels"."title" IN ('database::approved',
'feature')
GROUP BY "merge_requests"."id"
HAVING (COUNT(DISTINCT labels.title) = 2)
ORDER BY "merge_requests"."updated_at" DESC,
"merge_requests"."id" DESC
New Count Query:
Query, 26ms
SELECT COUNT() AS count_all, "merge_requests"."state_id" AS merge_requests_state_id FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ( (SELECT "namespaces". FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" FROM "base_and_descendants" AS "namespaces") AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4156052 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10, 20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20, 30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 4156052 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)))) AND (EXISTS (SELECT "label_links".* FROM "label_links" WHERE "label_links"."target_type" = 'MergeRequest' AND "label_links"."target_id" = "merge_requests"."id" AND "label_links"."label_id" in (3519305, 10230929, 7402316) LIMIT 1)) AND (EXISTS (SELECT "label_links".* FROM "label_links" WHERE "label_links"."target_type" = 'MergeRequest' AND "label_links"."target_id" = "merge_requests"."id" AND "label_links"."label_id" = 11108306 LIMIT 1)) GROUP BY "merge_requests"."state_id"
New Paginated Query:
Query, 12ms
SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20,
30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND ("merge_requests"."state_id" IN (1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'MergeRequest'
AND "label_links"."target_id" = "merge_requests"."id"
AND "label_links"."label_id" in (3519305, 10230929, 7402316)
LIMIT 1))
AND (EXISTS
(SELECT "label_links".*
FROM "label_links"
WHERE "label_links"."target_type" = 'MergeRequest'
AND "label_links"."target_id" = "merge_requests"."id"
AND "label_links"."label_id" = 11108306
LIMIT 1))
ORDER BY "merge_requests"."updated_at" DESC,
"merge_requests"."id" DESC
Migration output
UP
== 20200629134747 AddExtraIndexToLabelLinks: migrating ========================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:label_links, [:target_id, :label_id, :target_type], {:name=>"index_on_label_links_all_columns", :algorithm=>:concurrently})
-> 0.0021s
-- add_index(:label_links, [:target_id, :label_id, :target_type], {:name=>"index_on_label_links_all_columns", :algorithm=>:concurrently})
-> 0.0136s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:label_links, [:label_id, :target_type], {:name=>"index_label_links_on_label_id_and_target_type", :algorithm=>:concurrently})
-> 0.0078s
-- add_index(:label_links, [:label_id, :target_type], {:name=>"index_label_links_on_label_id_and_target_type", :algorithm=>:concurrently})
-> 0.0100s
-- transaction_open?()
-> 0.0000s
-- indexes(:label_links)
-> 0.0096s
-- remove_index(:label_links, {:algorithm=>:concurrently, :name=>"index_label_links_on_label_id"})
-> 0.0036s
== 20200629134747 AddExtraIndexToLabelLinks: migrated (0.0495s) ===============DOWN
== 20200629134747 AddExtraIndexToLabelLinks: reverting ========================
-- transaction_open?()
-> 0.0000s
-- indexes(:label_links)
-> 0.0023s
-- remove_index(:label_links, {:algorithm=>:concurrently, :name=>"index_on_label_links_all_columns"})
-> 0.0014s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:label_links, :label_id, {:name=>"index_label_links_on_label_id", :algorithm=>:concurrently})
-> 0.0013s
-- add_index(:label_links, :label_id, {:name=>"index_label_links_on_label_id", :algorithm=>:concurrently})
-> 0.0122s
-- transaction_open?()
-> 0.0000s
-- indexes(:label_links)
-> 0.0019s
-- remove_index(:label_links, {:algorithm=>:concurrently, :name=>"index_label_links_on_label_id_and_target_type"})
-> 0.0011s
== 20200629134747 AddExtraIndexToLabelLinks: reverted (0.0208s) ===============Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
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 by Adam Hegyi