여러 인덱스 결합하기

여러 인덱스 결합하기

PostgreSQL에서는 하나의 인덱스 스캔만으로 처리하기 어려운 쿼리 조건이 있을 때, 여러 인덱스를 함께 사용하는 방법을 제공해요. 이번에는 그 동작 방식과 인덱스 설계 시 어떤 점을 고려해야 하는지 알아볼게요.

출처: 공식문서

단일 인덱스 스캔의 한계

단일 인덱스 스캔은 인덱스의 컬럼과 해당 연산자 클래스의 연산자를 사용하면서 AND로 결합된 쿼리 절만 사용할 수 있어요. 예를 들어 (a, b) 인덱스가 있을 때 WHERE a = 5 AND b = 6 같은 조건은 인덱스를 사용할 수 있지만, WHERE a = 5 OR b = 6 같은 조건은 직접 인덱스를 사용할 수 없어요.

여러 인덱스 결합 기능

PostgreSQL은 여러 인덱스 스캔을 결합해서 AND와 OR 조건을 처리할 수 있어요. 예를 들어 WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 같은 쿼리는 x 인덱스를 각각 네 번 스캔해서 결과를 OR로 합칠 수 있어요. 또 x와 y에 각각 인덱스가 있다면 WHERE x = 5 AND y = 6 같은 쿼리는 각 인덱스를 사용해 결과를 AND로 결합할 수 있어요.

비트맵 스캔 방식

여러 인덱스를 결합할 때 시스템은 각 인덱스를 스캔하고, 조건에 맞는 테이블 행 위치를 나타내는 비트맵을 메모리에 만들어요. 그런 다음 쿼리에 따라 비트맵을 AND나 OR로 결합하고, 마지막으로 실제 테이블 행을 물리적 순서대로 방문해 반환해요. 이 때문에 원래 인덱스의 정렬 순서는 사라지고, ORDER BY 절이 있으면 별도의 정렬 단계가 필요해져요.

플래너의 선택과 설계 트레이드오프

추가 인덱스 스캔은 시간이 더 걸리기 때문에 플래너는 단순 인덱스 스캔을 선택할 때도 있어요. 그리고 실제 애플리케이션에서는 다양한 인덱스 조합이 유용할 수 있어서, 개발자는 어떤 인덱스를 제공할지 결정해야 해요. 다중 컬럼 인덱스가 최선일 때도 있지만, 별도 인덱스를 만들고 인덱스 결합 기능에 의존하는 게 나을 때도 있어요.

예를 들어 워크로드에 x만 사용하는 쿼리, y만 사용하는 쿼리, 둘 다 사용하는 쿼리가 섞여 있다면 x와 y에 각각 별도 인덱스를 만들고 인덱스 결합을 사용할 수 있어요. 또는 (x, y) 다중 컬럼 인덱스를 만들 수도 있어요. 이 인덱스는 두 컬럼을 모두 사용하는 쿼리에는 더 효율적이지만, y만 사용하는 쿼리에는 덜 유용해요. 그 유용성은 B-tree 인덱스 스킵 스캔 최적화가 얼마나 효과적인지에 달려 있어요. x의 고유 값이 수백 개 이하라면 스킵 스캔이 y 값을 찾는 검색을 합리적으로 효율적으로 수행할 수 있어요.

(x, y) 다중 컬럼 인덱스와 y에 대한 별도 인덱스를 조합하는 것도 좋은 선택이에요. x만 사용하는 쿼리에는 다중 컬럼 인덱스를 사용할 수 있지만, x 단독 인덱스보다 크고 느려요. 마지막 대안은 세 인덱스를 모두 만드는 것이지만, 테이블이 업데이트보다 검색이 훨씬 자주 발생하고 세 가지 쿼리 유형이 모두 흔할 때만 합리적이에요. 특정 유형이 훨씬 드물다면, 흔한 유형에 가장 잘 맞는 두 인덱스만 만드는 게 좋아요.

더 알아보기 (Learn more)