레이블로 필터링하기

소개

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를 가져오기 위한 별도의 쿼리를 실행하여 EXISTS 절에서 JOIN을 피하고 label_links.label_id로 직접 필터링했습니다. 이 쿼리를 가속화하기 위해 target_id, label_id, target_type 열에 대한 label_links에 새 인덱스를 추가했습니다.

레이블 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 절을 제거할 수 있었습니다.

이로 인해 쿼리가 단순해지고 가장 일반적인 경우의 성능이 개선되었습니다.