레이블 별 필터링

소개

GitLab에는 이슈, 병합 요청 및 에픽에 할당할 수 있는 레이블이 있습니다. 이러한 객체의 레이블은 다 대 다의 관계로 다형성 label_links 테이블을 통해 관련되어 있습니다.

여러 레이블을 기준으로 이러한 객체를 필터링하려면, 예를 들어 ‘레이블 ~Plan 및 레이블 ~backend가 있는 모든 열린 이슈’ 같은 경우 GROUP BY 절을 포함한 쿼리를 생성합니다. 간단한 형태로는 다음과 같습니다. 

SELECT
    issues.*
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
    issues.project_id = 13083
    AND (issues.state IN ('opened'))
    AND labels.title IN ('Plan',
        'backend')
GROUP BY
    issues.id
HAVING (COUNT(DISTINCT labels.title) = 2)
ORDER BY
    issues.updated_at DESC,
    issues.id DESC
LIMIT 20 OFFSET 0

특히 다음 사항에 주목하십시오.

  1. 우리는 GROUP BY issues.id를 사용하여 …
  2. 모든 일치하는 이슈가 두 개의 레이블을 모두 갖도록 하기 위해 HAVING (COUNT(DISTINCT labels.title) = 2) 조건을 사용합니다.

이것은 이상적인 것보다는 복잡합니다. 이렇게 함으로써 쿼리 작성이 더 많은 오류에 노출될 수 있습니다(예: 이슈 #15557).

시도 A: WHERE EXISTS

시도 A1: WHERE EXISTS와 함께 여러 하위쿼리 사용

이슈 #37137 및 관련 병합 요청에서 우리는 GROUP BY를 여러 WHERE EXISTS 사용으로 대체해 보았습니다. 위의 예시를 기준으로, 결과는 다음과 같습니다. 

WHERE (EXISTS (
        SELECT
            TRUE
        FROM
            label_links
            INNER JOIN labels ON labels.id = label_links.label_id
        WHERE
            labels.title = 'Plan'
            AND target_type = 'Issue'
            AND target_id = issues.id))
AND (EXISTS (
        SELECT
            TRUE
        FROM
            label_links
            INNER JOIN labels ON labels.id = label_links.label_id
        WHERE
            labels.title = 'backend'
            AND target_type = 'Issue'
            AND target_id = issues.id))

이 방식은 스키마 변경 없이 작동했지만 쿼리의 가독성을 다소 향상시켰지만 쿼리 성능은 향상되지 않았습니다.

시도 A2: WHERE EXISTS 구문에서 레이블 ID 사용

병합 요청 #34503에서 우리는 A1과 유사한 방식을 따랐습니다. 그러나 이번에는 필터에 사용된 레이블의 ID를 따로 조회하여 JOIN을 회피하고 label_links.label_id로 직접 필터링하는 접근을 했습니다. 또한 이 쿼리의 속도를 높이기 위해 label_links에 대해 target_id, label_id, 및 target_type 열에 대한 새 인덱스를 추가했습니다.

레이블 ID를 찾는 것은 간단하지 않았습니다. 왜냐하면 단일 루트 네임스페이스 내에서 동일한 제목을 갖는 레이블이 여러 개 있을 수 있기 때문입니다. 이 문제를 해결하기 위해 레이블 ID를 제목으로 그룹화하고 EXISTS 절에서 ID 배열을 사용했습니다.

이로써 상당한 성능 향상을 이룩했습니다. 그러나 이 최적화는 프로젝트나 그룹 컨텍스트가 없는 대시보드 페이지에는 적용할 수 없었습니다. 여기서 레이블 ID를 쉽게 검색할 수 없었기 때문입니다. 왜냐하면 이는 사용자가 액세스하는 모든 프로젝트와 그룹 전체를 검색해야 하기 때문이었습니다.

시도 B: 배열 열을 사용한 정규화

쿼리를 위해 label_links 테이블을 정규화하는 것에 대해 이슈 #49651에서 두 가지 옵션인 레이블 ID 및 제목을 고려했습니다.

이러한 것은 issues, merge_requests, 그리고 epics에서의 배열 열로 생각할 수 있습니다. issues.label_ids는 레이블 ID의 배열 열이 되고 issues.label_titles는 레이블 제목의 배열이 될 것입니다.

이러한 배열 열은 일치를 향상하기 위해 GIN 인덱스와 함께 사용할 수 있습니다.

시도 B1: 각 객체에 대한 레이블 ID 저장

이것은 제목보다는 강력한 장점을 가지고 있습니다.

  1. 레이블이 삭제되지 않거나 프로젝트가 이동하지 않는 이상, 비정규화된 열을 대규모로 업데이트할 필요가 없습니다.
  2. 제목보다 더 적은 저장소를 사용합니다.

불행하게도 우리의 응용프로그램 설계가 이를 어렵게 만듭니다. 레이블 ID만 쉽게 조회할 수 있다면 처음 문서에서 INNER JOIN labels를 필요로 하지 않을 것입니다. GitLab은 사용자가 프로젝트와 그룹을 거쳐 레이블 제목에 의해 필터링할 수 있는데, 따라서 레이블 ~Plan에 의해 생성되는 필터에는 여러 개의 고유 ID를 가진 레이블이 포함될 수 있습니다. 이러한 다양한 ID에 대해 사용자가 알 필요가 없으므로, 다음 데이터 집합이 주어졌을 때:

프로젝트 ~Plan 레이블 ID ~backend 레이블 ID
A 11 12
B 21 22
C 31 32

우리는 다음과 같은 것이 필요할 것입니다. 

WHERE
    label_ids @> ARRAY[11, 12]
    OR label_ids @> ARRAY[21, 22]
    OR label_ids @> ARRAY[31, 32]

이것은 더욱 복잡해질 수 있으며, 때로는 같은 객체에 적용될 수 있는 두 개의 서로 다른 ID를 가진 ~backend 레이블이 있을 수 있기 때문에 조합의 수가 더 늘어날 수 있습니다.

시도 B2: 각 객체에 대한 레이블 제목 저장

객체를 업데이트하는 관점에서 이것은 최악의 선택지입니다. 객체를 업데이트해야 하는 경우에는 다음과 같을 때입니다.

  1. 객체가 한 프로젝트에서 다른 프로젝트로 이동될 때
  2. 프로젝트가 한 그룹에서 다른 그룹으로 이동될 때
  3. 레이블이 이름이 변경될 때
  4. 레이블이 삭제될 때

이는 또한 훨씬 더 많은 저장소를 사용합니다. 그러나, 쿼리하는 것은 간단합니다. 

WHERE
    label_titles @> ARRAY['Plan', 'backend']

그리고 이슈 #49651의 테스트에서 이것이 빠를 수 있음을 보여주었습니다.

그러나 현재로서는 이러한 단점이 장점을 능가합니다.

결론

우리는 정규화가 필요없는 A2 방법을 발견했으며 이로 인해 쿼리 성능이 상당히 향상되었습니다.
이는 모든 경우에 적용되는 것은 아니지만, 나머지 경우에는 A1 방법을 적용하여 모든 시나리오에서 ‘GROUP BY’ 및 ‘HAVING’ 절을 제거할 수 있었습니다.

이로써 쿼리가 간소화되었고, 가장 일반적인 경우에 성능이 향상되었습니다.