연산 결과에 인덱스 걸기 (Indexes on Expressions)
SQL 성능 최적화, 이번 기회에 제대로 공부하고 싶다면!
SQL 성능 최적화, 데이터 모델링 뿐만 아니라 LTR 분석 등 다양한 분석 프레임워크까지 배울 수 있는 SQL 마스터반 커리큘럼을 살펴보세요. 쿠팡, 리디, 잡플래닛 출신 데이터 분석가들이 실무에 꼭 필요한 내용만 골라 담았어요.
인덱스 열은 기초 테이블의 컬럼일 필요는 없습니다. 테이블의 하나 이상의 열에서 계산된 함수나 스칼라 표현식일 수도 있습니다. 이 기능은 계산 결과를 기반으로 테이블에 빠르게 액세스하는 데 유용합니다.
예를 들어, 대소문자를 구분하지 않고 문자열 비교를 하고 싶을 때 LOWER() 함수를 일반적으로 사용하는데요.
SELECT * FROM test1 WHERE lower(col1) = 'value';
아래와 같이 LOWER(col1) 연산의 결과로 인덱스를 정의할 수 있습니다.
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
이 인덱스를 UNIQUE로 선언하면 col1 값이 대소문자 상관 없이 유일해야 한다는 제약 조건이 됩니다. 따라서 표현식에 인덱스를 거능 이 기능은 단순한 고유 제약 조건으로 정의할 수 없는 제약 조건을 적용하는 데 사용할 수도 있습니다.
또 다른 예로 다음과 같이 성과 이름을 붙여서 쿼리를 자주 수행하는 경우를 생각해볼 수 있습니다.
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
이런 경우 아래와 같은 인덱스를 만드는 것이 좋습니다.
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
두 번째 예시와 같은 CREATE INDEX 구문은 인덱스 표현식 주위에 괄호’( )’를 써야 합니다. 첫 번째 예에서처럼 표현식이 함수 호출일 때는 괄호를 생략할 수 있습니다.
인덱스 표현식은 유지 관리 비용이 상대적으로 많이 듭니다. 하지만 이미 계산된 결과가 인덱스에 저장되어 있기 때문에 인덱스 검색 중에는 저장 된 값을 가져다가 사용하기만 하면 됩니다. 위의 두 예에서 시스템은 쿼리를 WHERE indexedcolumn = '상수'로 간주하므로 검색 속도는 다른 간단한 인덱스 쿼리와 동일합니다. 따라서 식에 대한 인덱스는 삽입 및 업데이트 속도보다 검색 속도가 더 중요한 경우에 유용합니다.
Editor 선미’s comment
이 글의 원문은 PostgreSQL 공식문서 “Chapter 11. Indexes - 11.7. Indexes on Expressions”입니다. PostgreSQL 버전 16을 기준으로 작성되었습니다. 
표현식을 저장하는 인덱스의 유지 관리 비용이 단순 컬럼 값을 저장하는 인덱스보다 상대적으로 높은데요. 원인에 대해서 좀 더 알고 싶다면 원문을 참고해주세요. 데이터 모델링을 할 때 위의 성과 이름 예시처럼 따로 또 같이 봐야하는 데이터를 어떻게 저장해야할지 고민이 될 때가 있는데, Indexes on Expressions을 활용하는 것도 하나의 옵션이 될 수 있겠네요.
이 번역 작업이 여러분이 인덱스에 대해 공부하는데 도움이 되었으면 좋겠습니다. PostgreSQL 공식 문서가 인덱스에 대해 잘 쓰여져 있어 이전부터 번역을 꼭 해오고 싶었는데요. 흔쾌히 허가해주신 PostgreSQL CoC(Code of Conduct)팀 감사합니다. 이해를 돕기 위해 역자의 의역이 섞여있으니 만약 번역과 원문 간에 차이가 있는 경우 원문을 우선적으로 생각해주세요. 번역 오류는 contact@datarian.io로 제보해주세요.


conversion_type

시리즈 내 순서

conversion_text

멤버 전용

대표 이미지

공개 가능

작성일

예상 읽기 시간

전환 텍스트

글쓴이

시리즈

상단 노출

전환 유형

요약

제목

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

인덱스 개요

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

인덱스 타입: B-Tree, Hash

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

멀티 컬럼 인덱스

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

인덱스와 ORDER BY

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

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

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

고유 인덱스 (Unique Indexes)

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

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

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

부분 인덱스 (Partial Indexes)

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

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

Contents

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

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

함께 읽어보면 좋은 글

데이터 모델링의 종류 3가지와 ERD 그리는 방법

캠프에서 배운 SQL 실무에서 바로 써먹을 수 있어요

MySQL에 CSV 데이터 업로드하기

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

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

Contents

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

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

함께 읽어보면 좋은 글

데이터 모델링의 종류 3가지와 ERD 그리는 방법

캠프에서 배운 SQL 실무에서 바로 써먹을 수 있어요

MySQL에 CSV 데이터 업로드하기

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

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

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