Postgres의 SELECT DISTINCT는 데이터가 커지면 느려집니다

AI 요약

Postgres 기반 큐에서 활성 파티션을 찾던 DBOS 팀은 단순해 보이는 SELECT DISTINCT 쿼리가 예상보다 훨씬 느려지는 문제를 발견했습니다. 각 큐는 사용자별 파티션처럼 나뉘며, 파티션마다 실행 중인 작업 수를 제한합니다. 작업을 꺼내기 전에 ENQUEUED 상태인 워크플로가 있는 파티션을 찾아야 합니다. 팀은 큐 이름, 워크플로 상태, 파티션 키를 묶은 인덱스를 만들었으므로, 고유한 파티션 키만 찾으면 될 것으로 예상했습니다.

고유 값 수가 적어도 전체 행을 읽습니다

파티션이 많고 각 파티션의 대기 작업이 적은 ‘넓고 얕은’ 워크로드에서는 쿼리가 잘 작동했습니다. 반면 파티션은 적지만 각 파티션에 대기 작업이 많이 쌓이는 ‘좁고 깊은’ 워크로드에서는 문제가 드러났습니다. 활성 파티션 수가 적어도 쿼리는 수 초가 걸렸습니다. 파티션 수를 10으로 고정하고 파티션마다 행 수를 100개에서 100만 개까지 늘린 벤치마크에서도 지연 시간이 행 수에 비례해 증가했습니다.

쿼리 계획을 살펴보니 Postgres는 조건에 맞는 인덱스 항목을 전부 읽고 각 행의 파티션 키가 고유한지 확인하고 있었습니다. 예를 들어 파티션 키 세 개를 찾으려고 백만 행을 읽는 식입니다. 작성자는 인덱스가 정렬돼 있으니 각 키에서 한 행씩 건너뛰며 O(활성 파티션 수)만큼만 작업할 것으로 기대했지만, 실제 계획은 전체 인덱스 스캔에 가까웠습니다.

Skip scan과 loose index scan은 다릅니다

글은 Postgres의 인덱스 스캔 연산자가 조건에 맞는 인덱스 값을 모두 가져오므로 이 쿼리에 적합한 대안이 없다고 설명합니다. MySQL의 loose index scan은 조건을 만족하는 고유 값만 가져오는 기능입니다. Postgres 18에는 다중 열 인덱스에서 왼쪽 첫 열이 아닌 열을 검색할 때 행을 건너뛰는 skip scan 최적화가 추가됐지만, 글의 사례처럼 조건에 맞는 모든 행을 읽는 SELECT DISTINCT 문제를 해결하지는 않는다고 합니다. Postgres에 loose index scan을 추가하려던 시도는 2018년에 시작됐지만, 4년간의 작업과 메인테이너 교체를 거쳐 중단됐다고 덧붙입니다.

재귀 CTE로 다음 고유 값을 찾습니다

팀은 재귀 공통 테이블 식(CTE)을 이용한 우회 쿼리를 제시합니다. 첫 반복에서 가장 작은 파티션 키를 찾고, 다음 반복부터는 직전 값보다 큰 다음 고유 키를 찾습니다. 매 반복마다 정렬된 인덱스에서 SELECT min()으로 값 하나만 가져오므로, 전체 작업량은 고유 파티션 수에 비례합니다. 파티션 수를 10으로 고정하고 파티션당 행 수를 1천에서 100만까지 바꾼 벤치마크에서는 행 수가 커져도 중앙값 지연 시간이 달라지지 않았다고 보고합니다.

Hacker News 반응

• @DiabloD3 — 맞습니다. DISTINCT는 결과를 먼저 정렬한다고 자세히 문서화돼 있습니다. 이 사례를 보면 작성자는 GROUP BY를 몰랐고, 인덱스나 ANALYZE도 몰랐던 것 같습니다. Postgres 18의 새 skip scan 인덱싱도 도움이 될 수 있으니 플래너가 그 계획을 선택하게 하면 됩니다.
  • @Dylan16807 — GROUP BY가 이 문제를 해결하나요? 글에서는 skip scan이 여기서는 도움이 안 된다고 설명합니다. 인덱스도 여러 차례 다뤘고, 쿼리 계획을 확인했다고 명시했습니다.
  • @gfody — 아닙니다. 열이나 입력이 인덱스에 있는지, 정렬돼 있는지에 따라 HashAggregate, GroupAggregate, Unique 중 하나를 사용합니다.
  • @SkiFire13 — 인덱스 열에 GROUP BY를 쓰면 왜 해결되지 않느냐는 질문에 답하자면, 같은 문제가 생깁니다. 쿼리 플래너 관점에서 SELECT DISTINCT와 GROUP BY는 같습니다.
• @tpetry — 글을 읽기는 했나요? 완벽한 인덱스를 써도 도움이 안 된다고 보여줍니다. 현재 Skip Scan은 DISTINCT 쿼리에 쓰이지 않습니다.
• @nattaylor — Loose index scan은 바로 이런 용도입니다. MySQL은 지원하지만 Postgres는 아직 지원하지 않습니다.
  • @nikolatt — 최근 Postgres 릴리스를 따라가지 못했지만, 이미 추가됐을 줄 알았습니다. Timescale/Tigerdata는 Postgres에서 비슷한 인덱스 스캔을 구현했습니다.
• @sandeepkd — 회사 공동 창업자가 Postgres 창시자라고 나와 있는데, 글에 새로울 내용이 없어서 믿기 어렵습니다. 누구나 직접 겪으며 배울 수는 있지만, 사업을 운영한다면 전문가에게 도움을 구하는 방법도 있습니다. 제가 경험한 바로는 이런 쿼리는 어떻게 해도 확장되지 않습니다. 이미 이런 쿼리에 많이 투자했다면 DBA를 고용하고, 아직 초기라면 성능에 맞게 데이터를 모델링할 아키텍트를 고용하세요.
  • @Dylan16807 — 무엇을 기준으로 확장되지 않는다는 건가요? 인덱스에 서로 다른 값이 m개 있으면 이 방식으로 나열하는 데 O(m log n)이 걸립니다. 데이터가 얼마나 많든 여러 용도에 충분한 성능입니다.
  • @adrianN — 실행 시간은 대체로 규모가 커져도 잘 적용됩니다. 다만 점근 표기법에 대한 감각이 부족한 사람이 많습니다. 상수와 낮은 차수 항은 실제로 중요하지만 점근 표기법에서는 드러나지 않습니다.
• @procaryote — 저는 SELECT DISTINCT를 경고 신호로 보기 시작했습니다. 잘못된 코드에서 자주 발견되기 때문입니다. 고유성 제약 조건을 몰라서 나중에 중복을 제거하거나, 조인 조건을 빠뜨렸거나, 한 번 문제를 해결했던 방식이라 아무 데나 붙이는 경우가 있습니다. 잘 생각해서 DISTINCT를 쓰는 사람보다 그런 사람이 더 많습니다.
  • @Dylan16807 — 전체 행에 DISTINCT를 적용한다면 확실히 경고 신호입니다. 이 사례는 훨씬 무해해 보입니다.
  • @wvbdmp — 용도를 나눠 봐야 합니다. 임시 리포트나 성능에 문제가 없는 장기 리포트에서는 DISTINCT를 자주 씁니다. 스키마를 관리하는 애플리케이션 코드에는 쓰지 않겠지만, 탐색이나 서드파티 데이터베이스의 사용자 지정 리포트에는 좋습니다.
• @atemerev — 더 나은 쿼리 계획을 강제로 만드는 수동 우회책이 필요하다면, Postgres가 자동으로 더 나은 계획을 세우도록 자체 기능에 포함해야 합니다.
  • @SkiFire13 — 수동 우회 방식은 Postgres 위키에도 문서화돼 있습니다.

원문: DBOS / 번역·요약: Trawling