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