<div data-type="callout" data-emoji="👀" data-background-color="rgba(235, 236, 237, 0.3)" style="background-color: rgba(235, 236, 237, 0.3);">SQL 성능 최적화, 이번 기회에 제대로 공부하고 싶다면!SQL 성능 최적화, 데이터 모델링 뿐만 아니라 LTR 분석 등 다양한 분석 프레임워크까지 배울 수 있는 <a href="https://datarian.io/bootcamp/sql-master" target="_blank">SQL 마스터반</a> 커리큘럼을 살펴보세요. 쿠팡, 리디, 잡플래닛 출신 데이터 분석가들이 실무에 꼭 필요한 내용만 골라 담았어요.</div>부분 인덱스는 테이블의 하위 집합 위에 구축된 인덱스로, 하위 집합은 조건식에 의해 정의됩니다. 이 인덱스에는 특정 조건을 만족하는 일부 테이블 행만 포함됩니다. 부분 인덱스는 특수한 기능이지만, 부분 인덱스가 유용한 몇 가지 상황이 있습니다.부분 인덱스를 사용하는 주된 이유 중 하나는 전체 테이블에서 많은 비중을 차지하는 값을 인덱싱하지 않기 위해서입니다. 이런 값을 검색하는 쿼리는 어차피 인덱스를 사용하지 않으므로(*역자주: 이런 경우 인덱스 스캔을 거쳐 값을 찾는 것보다 바로 전체 테이블을 스캔하는 것이 더 빠르기 때문입니다. 인덱스는 전체 테이블에서 적은 비중을 차지하는 값을 찾을 때 더 효율적입니다.) 이러한 행을 인덱스에 보관할 필요가 없습니다. 이렇게 부분만 인덱스로 저장하면 인덱스의 크기가 줄어들어 인덱스를 사용하는 쿼리의 속도가 빨라집니다. 또한 모든 경우에 인덱스를 업데이트할 필요가 없으므로 테이블 업데이트 작업의 속도도 빨라집니다. 이 아이디어를 적용한 ‘예시 11.1. 흔한 값을 제외하도록 부분 인덱스 설정하기’를 봅시다.<h1 id="a22c683c3a4b43bbafd17927b1cf76af">예시 11.1. 흔한 값을 제외하도록 부분 인덱스 설정하기</h1>웹 서버 액세스 로그를 데이터베이스에 저장한다고 가정해 보겠습니다. 대부분의 액세스는 조직의 IP 주소 범위에서 발생하지만 일부는 다른 곳(예: 전화 접속을 사용하는 직원)에서 발생합니다. IP로 검색하는 것이 주로 외부 액세스에 대한 것이라면 조직의 서브넷에 해당하는 IP 범위를 인덱스로 만들 필요가 없을 것입니다.다음과 같은 테이블이 있다고 가정해 보겠습니다.<pre><code class="language-sql">CREATE TABLE access_log (
 url varchar,
 client_ip inet,
 ...
);</code></pre>이 예제에 적합한 부분 인덱스를 만들기위해 다음과 같은 명령을 사용합니다.<pre><code class="language-sql">CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip &gt; inet &#039;192.168.100.0&#039; AND
 client_ip &lt; inet &#039;192.168.100.255&#039;);</code></pre>이 인덱스를 사용할 수 있는 일반적인 쿼리는 다음과 같습니다. 여기서 쿼리의 IP 주소는 부분 인덱스에 포함됩니다. <pre><code class="language-sql">SELECT *
FROM access_log
WHERE url = &#039;/index.html&#039; AND client_ip = inet &#039;212.78.10.32&#039;;</code></pre>다음 쿼리는 인덱스에서 제외된 IP 주소를 사용하므로 부분 인덱스를 사용할 수 없습니다.<pre><code class="language-sql">SELECT *
FROM access_log
WHERE url = &#039;/index.html&#039; AND client_ip = inet &#039;192.168.100.23&#039;;</code></pre>이러한 종류의 부분 인덱스는 제외 할 값을 미리 결정해야 하므로 데이터 분포가 변경되지 않는 경우에 사용하는 것이 좋습니다. 데이터 분포가 바뀐다면 다시 만들어줘야 하므로 유지 관리 비용이 발생할 수 있습니다.부분 인덱스의 또 다른 용도는 일반적으로 쿼리를 실행할 때 자주 찾는 값만 인덱스로 만들어 놓는 것입니다. 이렇게 하면 위에 나열된 것과 동일한 이점이 있지만, 이외의 값을 찾을 때 인덱스를 사용할 수 없는 단점이 있습니다. 이런 시나리오를 위해 부분 인덱스를 설정하려면 많은 주의와 실험이 필요합니다. 아래 ‘예시 11.2 자주 찾는 값에만 부분 인덱스 설정하기’를 살펴봅시다.<h1 id="9b3ff758194846008d0f58c5383321c2">예시 11.2. 자주 찾는 값에만 부분 인덱스 설정하기</h1>계산이 된 주문과 계산되지 않은 주문이 모두 포함된 테이블이 있을 때, 만약 계산되지 않은 주문이 전체 테이블에서 차지하는 비중은 작지만 가장 많이 액세스되는 행인 경우을 가정해보겠습니다. 이런 경우에는 계산되지 않은 행에만 인덱스를 생성하여 성능을 개선할 수 있습니다. 인덱스를 생성하는 명령은 다음과 같습니다.<pre><code class="language-sql">CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;</code></pre>이 인덱스를 사용할 수 있는 쿼리는 다음과 같습니다.<pre><code class="language-sql">SELECT * FROM orders WHERE billed is not true AND order_nr &lt; 10000;</code></pre>이 인덱스는 <code>order_nr</code>을 전혀 포함하지 않는 쿼리에도 사용할 수 있습니다.<pre><code class="language-sql">SELECT * FROM orders WHERE billed is not true AND amount &gt; 5000.00;</code></pre>이는 시스템이 전체 인덱스를 스캔해야 하므로 <code>amount</code> 컬럼의 부분 인덱스만큼 효율적이지 않습니다. 하지만 계산되지 않은 주문이 상대적으로 적은 경우에는 이 부분 인덱스를 사용하는 것이 효과적일 수 있습니다.이 쿼리는 이 인덱스를 사용할 수 없습니다. 주문 3501은 계산이 된 주문일수도, 그렇지 않을 수도 있기 때문입니다.<pre><code class="language-sql">SELECT * FROM orders WHERE order_nr = 3501;</code></pre>이 예시는 인덱스 컬럼과 부분 인덱스를 정의하기 위해 사용하는 컬럼이 일치할 필요가 없음을 보여줍니다. 쿼리가 부분 인덱스를 타기 위해서는 쿼리에 사용된 조건과 부분 인덱스 정의 조건이 일치해야 합니다. PostgreSQL은 다른 형식으로 쓰여진 두 조건식의 수학적 연관성을 정교하게 찾아내어 부분 인덱스를 적용해주지는 않습니다. “x &lt; 1” 결과가 “x &lt;2”에 포함된다는 정도의 간단한 부등식 계산 정도는 가능합니다만, 이외의 경우에는 조건이 정확히 일치해야만 부분 인덱스를 적용해줍니다. 이렇게 조건을 비교해서 부분 인덱스 사용 여부를 판단하는 연산은 쿼리의 실행 단계가 아니라, 실행의 이전 단계인 쿼리 실행 계획을 만드는 단계에서 합니다.부분 인덱스의 세 번째 용도는 첫 번째, 두 번째 예시와는 달리 쿼리를 실행하기 위한 용도로 부분 인덱스를 정의하는 것이 아닙니다. 이 아이디어는 테이블의 하위 집합에 대해 고유 인덱스를 생성합니다. 이렇게 하면 부분 인덱스에 해당하는 구간에만 고유성을 적용하고 그렇지 않은 행에는 고유 제한을 두지 않을 수 있습니다. ‘예시 11.3. 고유 부분 인덱스 설정’에서 살펴보겠습니다.<h1 id="6822b4971d7f48b688b9240e745e5fae">예시 11.3. 고유 부분 인덱스 설정</h1>실험 결과를 설명하는 테이블 <code>tests</code>가 있다고 가정해 보겠습니다. <pre><code class="language-sql">CREATE TABLE tests (
 subject text,
 target text,
 success boolean,
 ...
);</code></pre>주어진 과목(<code>subject</code>)과 목표(<code>target</code>) 조합에 대해 &quot;성공한&quot; 항목은 하나만 있지만 &quot;실패한&quot; 항목은 얼마든지 있을 수 있습니다. 다음은 이를 수행하는 한 가지 방법입니다:<pre><code class="language-sql">CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;</code></pre>이는 성공한 테스트가 적고 실패한 테스트가 많은 경우에 특히 효율적인 접근 방식입니다. <code>IS NULL</code> 제한이 있는 고유 부분 인덱스를 생성하여 열에 하나의 null 만 허용할 수도 있습니다. (*역자주: 일반적인 고유 인덱스는 여러개의 NULL 값을 허용합니다. 자세한 내용은 <a href="https://datarian.io/blog/postgresql-unique-indexes" target="_blank">고유 인덱스 (Unique Indexes)</a>를 참고해주세요.)마지막으로, 부분 인덱스를 사용하여 시스템이 쿼리 실행 계획을 선택하는데 영향을 줄 수도 있습니다. 특이한 분포를 가진 데이터 세트는 시스템이 사용해서는 안 되는 인덱스를 사용하게 만들 수도 있습니다. 이 경우 문제가 되는 쿼리에 인덱스를 사용할 수 없도록, 전체 인덱스 대신 부분 인덱스를 설정할 수 있습니다. 하지만 일반적으로는 PostgreSQL이 인덱스 사용에 대해 합리적인 선택을 하며(예를 들어 흔한 값을 검색할 때에는 별다른 설정 없이도 인덱스 사용을 피하기 때문에 11.3. 예시는 인덱스의 사이즈를 줄이는 데에 의미가 있는 예시이지 unsuccessful에 해당하는 값을 추출할 때 인덱스 사용을 제한하기 위한 것은 아닙니다.), 심하게 잘못된 계획 선택은 버그 보고의 원인이 됩니다.부분 인덱스를 설정할 때에는 적어도 쿼리 플래너가 알고 있는 만큼 인덱스가 작동되기 적절한 상황을 알고 있어야 합니다. 이러한 지식을 형성하려면 PostgreSQL의 인덱스 작동 방식에 대한 경험과 이해가 필요합니다. 대부분의 경우, 일반 인덱스에 비해 부분 인덱스의 이점은 미미합니다. ‘예시 11.4. 부분 인덱스를 파티셔닝 대신 사용하지 않기’에서와 같이 오히려 역효과가 나는 경우도 있습니다.<h1 id="c29454bd13f04e3f9b61e27540bd2d93">예시 11.4. 부분 인덱스를 파티셔닝 대신 사용하지 않기</h1>다음과 같이 겹치지 않게 부분 인덱스를 대량으로 만들고 싶을 수도 있습니다.<pre><code class="language-sql">CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category =N;</code></pre>이것은 나쁜 생각입니다! 대부분의 경우에 다음과 같이 선언된 일반 인덱스를 사용하는 것이 더 낫습니다. (<a href="https://datarian.io/blog/postgresql-multicolumn-indexes" target="_blank">멀티 컬럼 인덱스</a>에 설명된 이유로 <code>category</code> 열을 먼저 배치합니다.)<pre><code class="language-sql">CREATE INDEX mytable_cat_data ON mytable (category, data);</code></pre>이 큰 인덱스의 검색은 작은 인덱스의 검색보다 트리 레벨을 몇 개 더 내려가야 할 수도 있지만, 플래너가 여러 부분 인덱스 중 적절한 것을 선택하는 것보다는 전자가 쉬울 것입니다. 문제의 핵심은 시스템이 부분 인덱스 간의 관계를 이해하지 못하고 각 인덱스가 현재 쿼리에 적용 가능한지 확인하기 위해 힘들게 테스트를 한다는 것입니다.테이블이 너무 커서 일반 단일 인덱스를 사용하는 것이 정말 좋지 않다면 대신 파티셔닝을 사용하는 것을 고려해야 합니다. <a href="https://www.postgresql.org/docs/16/ddl-partitioning.html" target="_blank">Chapter 5. Data Definition - 5.11. Table Partitioning</a> 참고해주세요.부분 인덱스에 대한 자세한 내용은  <a href="https://www.postgresql.org/docs/16/biblio.html#STON89B" target="_blank">[ston89b]</a>, <a href="https://www.postgresql.org/docs/16/biblio.html#OLSON93" target="_blank">[olson93]</a>, <a href="https://www.postgresql.org/docs/16/biblio.html#SESHADRI95" target="_blank">[seshadri95]</a>에서 확인할 수 있습니다.<div data-type="callout" data-emoji="✍🏻">Editor 선미’s comment이 글의 원문은 PostgreSQL 공식문서 “<a href="https://www.postgresql.org/docs/16/indexes-partial.html" target="_blank">Chapter 11. Indexes - 11.8. Partial Indexes</a>”입니다. PostgreSQL 버전 16을 기준으로 작성되었습니다. 매우 높은 빈도로 실행되는 쿼리의 성능을 극한으로 끌어올려야 하는, 매우 드문 상황에 처해있지 않은 이상 일반 일덱스를 사용하고 쿼리 플래너를 믿읍시다. 그러려고 DBMS를 사용하는 것이니까요. 쿼리의 실행 계획을 만드는 옵티마이저는 DBMS 개발자 분들의 노력을 자양분 삼아 시간이 갈수록 점점 더 똑똑해지고 있습니다.이 번역 작업이 여러분이 인덱스에 대해 공부하는데 도움이 되었으면 좋겠습니다. PostgreSQL 공식 문서가 인덱스에 대해 잘 쓰여져 있어 이전부터 번역을 꼭 해오고 싶었는데요. 흔쾌히 허가해주신 PostgreSQL CoC(Code of Conduct)팀 감사합니다. 이해를 돕기 위해 역자의 의역이 섞여있으니 만약 번역과 원문 간에 차이가 있는 경우 원문을 우선적으로 생각해주세요. 번역 오류는 <a href="mailto:contact@datarian.io" target="_blank">contact@datarian.io</a>로 제보해주세요.</div>

쿼리의 효율적인 실행에 대해 고민하고 있다면 지나칠 수 없는 토픽이 바로 인덱스입니다

인덱스 개요

대부분의 데이터베이스 인덱스는 B-Tree 또는 개선된 버전인 B+Tree로 구현이 되어있습니다.

인덱스 타입: B-Tree, Hash

멀티 컬럼 인덱스는 자주 사용하지 않는 것이 좋습니다. 대부분의 상황에서 단일 컬럼에 대한 인덱스로 충분합니다.

멀티 컬럼 인덱스

인덱스는 쿼리에서 반환할 행을 찾는 기능을 외에 데이터를 정렬하는 기능을 제공하기도 합니다.

인덱스와 ORDER BY

멀티 컬럼 인덱스와 인덱스 조합 기능을 잘 구분하여 이해해주세요!

인덱스 조합 기능 (Combining Multiple Indexes)

고유성 제약이 걸려있는 컬럼과 기본 키의 경우, 생성 시 자동으로 B-Tree 인덱스가 만들어집니다.

고유 인덱스 (Unique Indexes)

함수 연산의 결과로도 인덱스를 정의할 수 있습니다.

연산 결과에 인덱스 걸기 (Indexes on Expressions)

매우 드문 상황에 처해있지 않은 이상 일반 일덱스를 사용하고 쿼리 플래너를 믿읍시다. 그러려고 DBMS를 사용하는 것이니까요.

부분 인덱스 (Partial Indexes)

EXPLAIN 명령을 사용하면 플래너가 어떤 쿼리 계획을 생성하는지 확인할 수 있습니다.

EXPLAIN 사용법

예시 11.1. 흔한 값을 제외하도록 부분 인덱스 설정하기

예시 11.2. 자주 찾는 값에만 부분 인덱스 설정하기

예시 11.3. 고유 부분 인덱스 설정

예시 11.4. 부분 인덱스를 파티셔닝 대신 사용하지 않기

부분 인덱스 (Partial Indexes)

내 쿼리가 왜 느린지 궁금했던 적 있다면?

Contents

데이터 분석 소식을
매월 받아보세요

🐘 PostgreSQL 공식 문서 번역 모아보기

함께 읽어보면 좋은 글

“카피바라도 할 수 있는 SQL 기초” 핸즈온 수업 자료

데이터 분석을 위한 SQL 참고도서 추천

SQL 기초 레벨 테스트 5문항

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요

부분 인덱스 (Partial Indexes)

내 쿼리가 왜 느린지 궁금했던 적 있다면?

Contents

데이터 분석 소식을 매월 받아보세요

🐘 PostgreSQL 공식 문서 번역 모아보기

함께 읽어보면 좋은 글

“카피바라도 할 수 있는 SQL 기초” 핸즈온 수업 자료

데이터 분석을 위한 SQL 참고도서 추천

SQL 기초 레벨 테스트 5문항

데이터 분석 소식을 매월 받아보세요데이터 분석 소식을 받아보세요

데이터 분석 소식을
매월 받아보세요

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요