PostgreSQL의 복합 기본 키가 있는 테이블에서 SELECT DISTINCT가 느린 이유는 무엇이며 어떻게 최적화할 수 있습니까?

PostgreSQL에서 복합 기본 키를 사용하는 테이블 SELECT DISTINCT 쿼리 속도가 느린 이유와 최적화 방법

PostgreSQL 데이터베이스에서 SELECT DISTINCT 쿼리 실행 속도는 테이블 구조와 데이터 분포에 따라 달라집니다. tickers 테이블의 product_id 열은 복합 기본 키의 일부이므로 해당 열에 인덱싱되지만 SELECT DISTINCT product_id FROM tickers을 사용하여 고유한 product_id을 가져오는 쿼리는 기본적으로 순차 스캔을 수행합니다.

성능 저하 이유

의 성능이 저하되는 주된 이유는 테이블에 product_id의 중복된 값이 있기 때문입니다. 이는 검색된 각 고유 product_id에 대해 PostgreSQL이 전체 테이블을 스캔하여 중복이 없는지 확인해야 함을 의미합니다.

해결책: 인덱스 건너뛰기 스캔 시뮬레이션

PostgreSQL은 아직 기본적으로 인덱스 건너뛰기 스캔을 지원하지 않으므로 재귀 CTE(공통 테이블 표현식)를 사용하여 이 동작을 시뮬레이션할 수 있습니다. 이 CTE는 반복적으로 중복 항목을 검색하고 삭제하여 각 고유 product_id의 인스턴스를 하나만 남기고 모두 효과적으로 필터링합니다.

개선된 솔루션

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- 括号必需
   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
   )
TABLE  cte;</code>

이 쿼리는 수평 조인을 사용하여 정렬된 테이블을 순회하고 orderBy 을 사용하여 고유한 product_id 값을 검색합니다.

결론

CTE 방법을 사용하여 인덱스 건너뛰기 스캔을 시뮬레이션함으로써 SELECT DISTINCT product_id 쿼리의 실행 시간을 크게 향상시킬 수 있으며, 이를 통해 tickers 테이블에서 고유한 product_id을 검색하는 데 필요한 시간을 줄일 수 있습니다.

위 내용은 PostgreSQL의 복합 기본 키가 있는 테이블에서 SELECT DISTINCT가 느린 이유는 무엇이며 어떻게 최적화할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!