복합 기본 키를 사용하는 PostgreSQL SELECT DISTINCT 쿼리가 왜 이렇게 느린가요?

PostgreSQL SELECT DISTINCT 복합 키의 성능 문제

복합 기본 키(예: SELECT DISTINCT)가 있는 PostgreSQL 테이블에서 (product_id, trade_id)을 사용하면 놀라울 정도로 느릴 수 있습니다. 쿼리 플래너는 인덱스를 효율적으로 활용하는 대신 순차 스캔을 선택하는 경우가 많습니다.

왜 느린가요?

• 순차 스캔 기본 설정: 플래너는 인덱스가 있더라도 전체 테이블 스캔을 선호할 수 있으며 이로 인해 쿼리 시간이 상당히 길어집니다.
• 인덱스 건너뛰기 스캔 누락: PostgreSQL은 기본적으로 인덱스 내 중복 항목을 건너뛰어 고유 값을 효율적으로 검색하는 최적화인 인덱스 건너뛰기 스캔을 지원하지 않습니다.

해결책: CTE를 사용한 인덱스 건너뛰기 스캔 시뮬레이션

실제 인덱스 건너뛰기 스캔을 사용할 수는 없지만 재귀적 공통 테이블 표현식(CTE)을 사용하여 그 동작을 효과적으로 모방할 수 있습니다.

WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id
      ORDER  BY 1
      LIMIT  1
      ) l
   )
SELECT * FROM cte;

이 CTE는 효율성을 위해 product_id의 인덱스를 활용하여 정렬된 순서로 고유한 (product_id) 값을 반복합니다.

이 접근 방식의 장점

• 속도 향상: 이 방법을 사용하면 쿼리 실행 시간이 대폭 단축됩니다. 테스트 결과 225만 행 테이블에서 0.75밀리초로 단축된 것으로 나타났습니다.
• 인덱스 활용: 복합 기본 키 인덱스 (product_id, trade_id)와 (product_id)의 인덱스를 모두 효과적으로 활용합니다.
• 데이터 분포 불가지론: 테이블 내의 데이터 분포에 관계없이 성능이 일관되게 유지됩니다.

위 내용은 복합 기본 키를 사용하는 PostgreSQL SELECT DISTINCT 쿼리가 왜 이렇게 느린가요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!