과도한 테이블 스캔을 피하기 위해 Postgres에서 그룹별 최대 쿼리를 어떻게 최적화할 수 있습니까?

그룹별 최대 쿼리 최적화

문제의 쿼리는 레코드 테이블의 각 고유 option_id에 대해 최대 id 값이 있는 행을 검색하는 것을 목표로 합니다. . 그러나 현재 구현에서는 과도한 테이블 스캔으로 인해 비효율성이 나타납니다.

현재 쿼리가 비효율적인 이유

문제는 행을 식별하는 데 사용되는 중첩 루프 조인에 있습니다. 최대 ID 값. 이 조인을 위해서는 Postgres가 전체 레코드 테이블을 여러 번 스캔해야 하므로 실행 시간과 리소스 소비가 높아집니다.

조회 테이블을 사용하는 대체 접근 방식

이 쿼리를 최적화하려면 , 옵션 ID를 레코드 테이블 내의 최대 ID에 매핑하는 옵션이라는 별도의 조회 테이블을 만드는 대체 접근 방식이 권장됩니다. records.option_id와 options.option_id 사이에 외래 키 제약 조건을 도입하면 참조 무결성이 보장됩니다.

CREATE TABLE options (
  option_id int PRIMARY KEY,
  option text UNIQUE NOT NULL
);

INSERT INTO options (option_id, option)
SELECT DISTINCT option_id, 'option' || option_id
FROM records;

상관 하위 쿼리를 사용하여 최적화된 쿼리

옵션 테이블이 있는 경우 , option_id를 기반으로 두 테이블을 효율적으로 조인하는 상관 하위 쿼리를 사용하여 원래 쿼리를 다시 작성할 수 있습니다. 필드.

SELECT o.option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY o.option_id;

대체 접근 방식의 이점

이 대체 접근 방식은 여러 가지 장점을 제공합니다.

• 옵션 테이블에서 해당 행을 검색합니다.
• 효율적인 상관관계를 활용합니다. 하위 쿼리를 사용하여 레코드에서 최대 ID 값을 검색합니다.
• 외래 키 제약 조건을 통해 참조 무결성을 유지합니다.

추가 최적화

(option_id, id DESC NULLS LAST)의 레코드 테이블에 대한 인덱스는 다음을 허용하여 성능을 더욱 향상시킬 수 있습니다. 하위 쿼리에 대해 인덱스 전용 스캔을 수행하는 Postgres.

위 내용은 과도한 테이블 스캔을 피하기 위해 Postgres에서 그룹별 최대 쿼리를 어떻게 최적화할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!