라벨별 필터링

소개

GitLab에는 이슈, Merge Request 및 이야기에 할당할 수 있는 라벨이 있습니다. 이러한 객체의 라벨은 다대다 관계로 다형성 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 및 관련 Merge Request에서 우리는 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 사용

Merge Request #34503에서 우리는 A1에 유사한 접근 방식을 취했습니다. 그러나 이번에는 필터에서 사용된 라벨의 ID를 따로 조회하여 JOIN없이 EXISTS 절에서 label_links.label_id로 직접 필터링하였습니다. 또한 이 쿼리의 속도를 높이기 위해 label_links에 target_id, label_id, target_type 열에 대한 새로운 인덱스를 추가했습니다.

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

결과적으로 성능이 크게 향상되었습니다. 그러나 이 최적화는 프로젝트나 그룹 컨텍스트가없는 대시 보드 페이지에는 적용할 수 없었습니다. 여기서 라벨 ID를 쉽게 찾을 수 없었기 때문에 사용자가 액세스 할 수있는 모든 프로젝트 및 그룹을 대상으로 검색해야했습니다.

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

우리는 issue, merge_requests, epics에 대해 배열 열로 생각할 수 있는 label IDs와 title 두 가지 옵션으로 쿼리를 위해 label_links 테이블을 정규화하는 것을 이슈 #49651에서 논의했습니다.

이러한 배열 열은 일치를 개선하기 위해 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 절을 제거할 수있었습니다.

이렇게 하면 쿼리가 단순화되고 가장 일반적인 경우에 성능이 향상되었습니다.