인덱스와 ORDER BY

인덱스와 ORDER BY

인덱스는 원하는 행을 찾는 데만 쓰이는 게 아니에요. 행을 정렬된 순서대로 꺼내주는 역할도 할 수 있어요. 이렇게 되면 ORDER BY 때문에 따로 정렬 단계를 거치지 않아도 되죠. 다만 PostgreSQL이 지원하는 인덱스 유형 중에서 정렬된 출력을 만들 수 있는 건 지금은 B-tree뿐이고, 나머지 유형은 행을 어떤 순서로 돌려줄지 정해져 있지 않아요. 이번에는 B-tree 인덱스가 정렬을 어떻게 활용하는지, 어떤 케이스에서 진짜 이득인지 살펴볼게요.

출처: PostgreSQL 공식 문서 — 11.4. Indexes and ORDER BY

인덱스 스캔 vs 명시적 정렬

플래너는 ORDER BY 조건을 만족하는 방법을 두 가지로 고려해요. 하나는 그 조건에 맞는 인덱스를 훑는 방법, 그리고 다른 하나는 테이블을 물리적 순서대로 훑으면서 따로 정렬을 하는 방법이에요. 테이블의 상당 부분을 읽어야 하는 질의라면 명시적 정렬이 더 빠른 경우가 많아요. 순차 접근 패턴 덕에 디스크 I/O가 적기 때문이죠. 반대로 몇 개의 행만 가져오면 되는 질의에서는 인덱스가 더 유용해요.

특히 중요한 경우는 ORDER BY가 LIMIT n 과 함께 쓰일 때예요. 명시적 정렬은 첫 n개 행을 찾으려면 데이터를 전부 처리해야 하지만, ORDER BY에 맞는 인덱스가 있으면 첫 n개 행을 나머지를 전혀 훑지 않고 바로 꺼낼 수 있어요.

기본 정렬 순서

B-tree 인덱스는 기본적으로 오름차순, NULL은 마지막에 두고 항목을 저장해요(값이 같으면 테이블 TID를 동점자 처리용 열로 취급해요). 그래서 열 x의 인덱스를 정방향으로 훑으면 ORDER BY x(더 풀어 쓰면 ORDER BY x ASC NULLS LAST)를 만족하는 결과가 나와요. 인덱스를 역방향으로 훑으면 ORDER BY x DESC(ORDER BY x DESC NULLS FIRST — DESC의 기본이 NULLS FIRST라서)를 만족하는 결과를 얻을 수 있어요.

인덱스를 만들 때 ASC, DESC, NULLS FIRST, NULLS LAST 옵션을 주면 B-tree 인덱스의 정렬 순서를 조정할 수 있어요. 예를 들면 이렇게요.

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

NULL을 먼저 두고 오름차순으로 저장한 인덱스는 스캔 방향에 따라 ORDER BY x ASC NULLS FIRST나 ORDER BY x DESC NULLS LAST를 만족할 수 있어요.

네 가지 옵션은 왜 필요한가

"두 가지 옵션과 역방향 스캔만으로도 모든 ORDER BY 변형을 커버할 수 있는데, 왜 네 가지 옵션을 다 제공할까?"라는 의문이 들 수 있어요. 단일 열 인덱스에서는 실제로 옵션들이 중복이에요. 하지만 다중 열 인덱스에서는 유용해요. 두 열 (x, y) 인덱스를 생각해 볼게요. 정방향으로 훑으면 ORDER BY x, y, 역방향으로 훑으면 ORDER BY x DESC, y DESC를 만족할 수 있어요. 그런데 애플리케이션이 ORDER BY x ASC, y DESC를 자주 쓴다면요? 일반 인덱스로는 이 정렬을 만드는 방법이 없어요. 다만 인덱스를 (x ASC, y DESC) 또는 (x DESC, y ASC)로 정의하면 가능해져요.

비기본 정렬 순서를 가진 인덱스는 상당히 전문화된 기능이지만, 특정 질의에서 엄청난 속도 향상을 낼 수 있는 경우가 있어요. 그런 인덱스를 유지할 가치가 있느냐는 특수한 정렬이 필요한 질의를 얼마나 자주 쓰느냐에 달려 있어요.

더 알아보기