복합 기본 키가 있는 Postgres 테이블에서 내 SELECT DISTINCT 쿼리가 느린 이유는 무엇이며 성능을 어떻게 향상시킬 수 있습니까?

Postgres에서 느린 SELECT DISTINCT 쿼리 최적화

이 문서에서는 복합 기본 키가 있는 대규모 Postgres 테이블에서 SELECT DISTINCT 쿼리를 실행할 때 발생하는 성능 문제를 다룹니다. 거의 200만 행이 포함된 테이블과 복합 기본 키(product_id, trade_id)가 포함된 특정 시나리오를 검사합니다. SELECT DISTINCT product_id 쿼리는 기본 키 인덱스로 인해 이상적으로 빨라야 하지만 예기치 않게 성능이 저하되었습니다.

근본 원인 분석:

쿼리 플래너가 인덱스를 활용하지 않고 순차 스캔을 선택한 것이 병목 현상으로 식별되었습니다. 이는 테이블의 데이터 분포에 기인합니다. 고유한 제품 ID가 40개만 존재하므로 인덱스 값 반복 수준이 높습니다. 이로 인해 수많은 인덱스 조사와 비효율적인 순차 액세스가 발생합니다.

효과적인 솔루션: 재귀적 CTE

이러한 제한을 피하고 인덱싱을 효율적으로 활용하기 위해 SELECT DISTINCT:

에 대한 우수한 대안으로 재귀 공통 테이블 표현식(CTE)이 제안되었습니다.

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses required
   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  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;</code>

이 재귀 CTE는 인덱스 건너뛰기 스캔을 효과적으로 모방합니다. 정렬된 순서로 고유한 product_id 값을 반복적으로 검색하여 비효율적인 순차 스캔과 관련된 성능 저하를 방지합니다. 이 접근 방식의 성능을 최적화하려면 product_id 열에 인덱스를 사용하는 것이 중요합니다.

중요 사항: Postgres의 인덱스 건너뛰기 스캔 기능이 개발 중이지만 이 CTE 기반 해결 방법은 설명된 시나리오에 대한 강력하고 효율적인 솔루션을 제공하여 쿼리 성능을 크게 향상시킵니다.

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