GitLab Next

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