[SQL] 큰 테이블에 오프셋 최적화 쿼리

큰 테이블에 오프셋 최적화 쿼리

나는 테이블이

create table big_table (
id serial primary key,
-- other columns here
vote int
); 

이 테이블은 약 70 만 행, 나는 쿼리에 필요한, 매우 큰 것입니다 :

SELECT * FROM big_table
ORDER BY vote [ASC|DESC], id [ASC|DESC]
OFFSET x LIMIT n  -- I need this for pagination

아시다시피 x는 많은 수의 경우,이 같은 쿼리는 매우 느리다.

성능 최적화를 위해 나는 인덱스를 추가 :

create index vote_order_asc on big_table (vote asc, id asc);

과

create index vote_order_desc on big_table (vote desc, id desc);

SELECT 쿼리 위의 이러한 인덱스를 사용하지만 오프셋 대규모로 어쨌든 매우 느린 것을 보여줍니다 설명한다.

큰 테이블의 오프셋 (offset)와 나는 최적화 쿼리에 무엇을 할 수 있습니까? 아마도 PostgreSQL을 9.5 또는 최신 버전의 일부 기능이? 내가 검색했지만 아무것도 찾지 못했습니다.

해결법

1. ==============================

   1.큰 항상 느린 될 것입니다 OFFSET. 포스트 그레스는 모든 행을 주문하고 오프셋 당신에 눈에 보이는 사람을 계산한다. 직접 테이블에 인덱스 ROW_NUMBER를 추가 할 수 있습니다 이전의 모든 행을 건너 (또는 상기 ROW_NUMBER 포함 구체화 된 뷰를 생성)와 함께 작동하도록 WHERE ROW_NUMBER> X 대신 × 오프셋.

   큰 항상 느린 될 것입니다 OFFSET. 포스트 그레스는 모든 행을 주문하고 오프셋 당신에 눈에 보이는 사람을 계산한다. 직접 테이블에 인덱스 ROW_NUMBER를 추가 할 수 있습니다 이전의 모든 행을 건너 (또는 상기 ROW_NUMBER 포함 구체화 된 뷰를 생성)와 함께 작동하도록 WHERE ROW_NUMBER> X 대신 × 오프셋.

   그러나이 방법은 읽기 전용 (또는 대부분) 데이터 만 분별이다. 동시에 변경할 더 도전 할 수 테이블 데이터에 대해 동일한 구현. 당신은 정확히 원하는 동작을 정의하여 시작해야합니다.

   나는 매김에 대한 다른 접근 방식을 제안한다 :

   SELECT *
   FROM   big_table
   WHERE  (vote, id) > (vote_x, id_x)  -- ROW values
   ORDER  BY vote, id  -- needs to be deterministic
   LIMIT  n;
   

   vote_x 및 id_x 이전 페이지의 마지막 행에서 (DESC와 ASC 모두)입니다 경우. 또는 처음부터 뒤로 이동합니다.

   행 값을 비교하는 것은 당신이 이미 가지고있는 인덱스에서 지원 - 기능 그 ISO SQL 표준,하지만 모든 RDBMS 지원이 준수합니다.

   CREATE INDEX vote_order_asc ON big_table (vote, id);
   

   또는 내림차순을 위해 :

   SELECT *
   FROM   big_table
   WHERE  (vote, id) < (vote_x, id_x)  -- ROW values
   ORDER  BY vote DESC, id DESC
   LIMIT  n;
   

   같은 인덱스를 사용할 수 있습니다. 나는 당신이 당신의 열이 NOT NULL을 선언하거나 NULLS FIRST와 함께 자신을 익히 제안 | LAST 구조 :

   특히 두 가지를 참고 :

   관련 :

   마르쿠스 Winand I에 의해 특정 프레젠테이션에 참고 링크 :

2. ==============================

   2.당신은 테이블을 분할 시도?

   당신은 테이블을 분할 시도?

from https://stackoverflow.com/questions/34110504/optimize-query-with-offset-on-large-table by cc-by-sa and MIT license