Filtering issues by label produces complicated query with GROUP BY
Filtering issues by label seems overly complex. For example, filtering by ~bug and ~P4 produces the below query. Let's look into how this complexity can be lowered, maybe we can even remove the GROUP BY
here.
See https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/6218#note_90247101 for the motivation.
SELECT issues.*
,(
SELECT MIN("label_priorities"."priority")
FROM "labels"
INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id"
LEFT JOIN "label_priorities" ON "labels"."id" = "label_priorities"."label_id"
WHERE (label_priorities.project_id = issues.project_id)
AND (label_links.target_id = issues.id)
AND "label_links"."target_type" = 'Issue'
) AS highest_priority
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
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"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (
issues.confidential IS NOT TRUE
OR (
issues.confidential = TRUE
AND (
issues.author_id = 443319
OR EXISTS (
SELECT TRUE
FROM issue_assignees
WHERE user_id = 443319
AND issue_id = issues.id
)
OR issues.project_id IN (
SELECT "projects"."id"
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
WHERE "project_authorizations"."user_id" = 443319
AND (project_authorizations.access_level >= 20)
)
)
)
)
AND "projects"."namespace_id" IN (
WITH RECURSIVE "base_and_descendants" AS (
SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 9970
UNION
SELECT "namespaces".*
FROM "namespaces"
,"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id"
)
SELECT "id"
FROM "base_and_descendants" AS "namespaces"
)
AND (
EXISTS (
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 443319
AND (project_authorizations.project_id = projects.id)
)
OR projects.visibility_level IN (
10
,20
)
)
AND (
"project_features"."issues_access_level" IN (
NULL
,20
)
OR (
"project_features"."issues_access_level" = 10
AND EXISTS (
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 443319
AND (project_authorizations.project_id = projects.id)
)
)
)
AND ("issues"."state" IN ('opened'))
AND "labels"."title" IN (
'bug'
,'P4'
)
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 = 3412464) LIMIT 1
)
)
GROUP BY "issues"."id"
HAVING COUNT(DISTINCT labels.title) = 2
ORDER BY relative_position ASC NULLS LAST
,highest_priority ASC NULLS LAST
,id DESC LIMIT 20 OFFSET 0