인덱스 개요
PostgreSQL 공식 문서 모아보기
인덱스 개요
인덱스 타입: B-Tree, Hash
멀티 컬럼 인덱스
인덱스와 ORDER BY
인덱스 조합 기능 (Combining Multiple Indexes)
고유 인덱스 (Unique Indexes)
연산 결과에 인덱스 걸기 (Indexes on Expressions)
부분 인덱스 (Partial Indexes)
EXPLAIN 사용법
인덱스는 데이터베이스 성능을 향상시키는 일반적인 방법입니다. 인덱스를 사용하면 인덱스가 없을 때보다 훨씬 빠르게 특정 행을 찾을 수 있습니다. 하지만 인덱스는 데이터베이스 시스템 전체에 오버헤드를 추가하므로 현명하게 사용해야 합니다.
이와 유사한 테이블이 있다고 가정해 보겠습니다.
CREATE TABLE test1 (
    id integer,
    content varchar
);
그리고 애플리케이션에서 아래 쿼리를 많이 실행한다고 생각해보겠습니다.
SELECT content FROM test1 WHERE id = {입력 받은 값};
사전 준비가 없다면 데이터베이스는 일치하는 모든 항목을 찾기 위해 전체 test1 테이블을 행 단위로 스캔해야 합니다. test1에 많은 행이 있고 이러한 쿼리에서 반환되는 행이 매우 적다면(0개 또는 1개), 테이블 전체를 스캔하는 것은 매우 비효율적입니다. ID 열에 대한 인덱스가 있으면 일치하는 행을 찾는 데 더 효율적인 방법을 사용할 수 있습니다. 예를 들어, 검색 트리에서 몇 단계만 들어가면 찾을 수 있다던지요. (*역자주: 인덱스 알고리즘도 여러 종류가 있지만, 예를 든 것처럼 검색 트리류가 가장 많이 사용됩니다.)
인덱스를 책에 비유하기도 하는데요. 논픽션 책을 보면 독자들이 자주 찾는 용어와 개념을 책의 맨 끝에 가나다 또는 알파벳 순으로 색인을 만들어놓습니다. 독자는 관심 있는 부분을 찾기 위해 책 전체를 읽을 필요 없이 색인을 훑어보고 바로 해당 페이지로 넘어갈 수 있습니다. 독자가 어떤 항목을 찾을 가능성이 높은지 고민하여 색인을 만드는게 저자의 할 일 인 것처럼, 어떤 인덱스가 유용할지 예측하는 것은 데이터베이스 프로그래머가 할 일입니다.
다음 명령을 사용하여 ID 열에 인덱스를 만들 수 있습니다:
CREATE INDEX test1_id_index ON test1 (id);
test1_id_index 는 인덱스의 이름인데요. 자유롭게 정할 수 있지만, 이왕이면 나중에 인덱스의 용도를 기억할 수 있는 이름을 사용하는 것이 좋습니다.
인덱스를 제거하려면 DROP INDEX 명령을 사용합니다. 인덱스는 언제든지 테이블에 추가하거나 제거할 수 있습니다.
인덱스가 생성되면 사용자가 더 이상 할 일이 없습니다. 테이블이 수정되면 시스템에서 인덱스를 자동으로 업데이트하고, 테이블 전체를 순차적으로 읽는 것보다 인덱스를 사용하는 것이 더 효율적이라고 판단되면 인덱스를 사용합니다. 한 가지 유의사항이 있다면, 정기적으로 ANALYZE 명령을 실행하여 쿼리 플래너(*역자주: 옵티마이저라고도 부르며 데이터의 분포, 인덱스의 유뮤 등을 고려해 실행 계획을 세우고 각 계획의 비용을 계산하는 DBMS 내부의 프로세스입니다. 데이터 베이스의 성능을 결정하는 가장 핵심적인 기능이라고 볼 수 있습니다.)가 현명한 결정을 내릴 수 있도록 통계를 업데이트 해주는 것이 좋습니다. 인덱스 사용 여부, 플래너가 인덱스를 사용하지 않는 상황 등에 대한 자세한 내용이 궁금하다면 Chapter 14. Performance Tips를 참고해 주세요.
인덱스는 검색 조건이 있는 업데이트 및 삭제 명령, 조인 등 다양한 상황에 활용할 수 있습니다. 조인 조건으로 사용된 열에 인덱스가 정의되어 있다면 그렇지 않은 경우보다 쿼리 속도가 크게 개선될 수 있습니다.
일반적으로 PostgreSQL 인덱스는 다음과 같은 형식의 WHERE 또는 JOIN 절을 하나 이상 포함하는 쿼리를 최적화하는 데 사용할 수 있습니다.
indexed-column indexable-operator comparison-value
여기서 indexed-column은 인덱스가 정의된 열 또는 표현식입니다. indexable-operator는 특정 인덱스에서 사용할 수 있는 연산자를 뜻합니다. (*역자주: 여기에서 연산자란 =, <, > 등을 의미하며 인덱스 알고리즘마다 적용할 수 있는 연산자가 다릅니다. 예를 들어, 해시 인덱스의 경우 = 연산자를 사용하는 경우에는 인덱스를 활용할 수 있지만 <, > 등 다른 연산자를 사용하는 경우에는 만들어놓은 인덱스를 활용할 수 없습니다. 이에 대한 자세한 내용은 다음 글에 나와있습니다.) 그리고 comparison-value은 변동성이 없고 인덱스의 테이블을 참조하지 않는 모든 표현식이 될 수 있습니다.
위 형식과 정확하게 일치하지 않더라도, 쿼리 플래너는 경우에 따라 인덱싱이 가능한 부분을 찾아 적용하기도 합니다. 간단한 예를 들어, 원래 쿼리가 사용하는 연산자가 인덱스 적용이 가능한 연산자, 즉 indexable-operator가 아닌 경우에도 인덱스 적용이 가능한 연산자로 산식을 바꿀 수 있다면 임의로 변형하여 인덱스를 적용해주기도 합니다.
comparison-value operator indexed-column
큰 테이블에 인덱스를 생성하는 데 시간이 오래 걸릴 수 있습니다. 기본적으로 PostgreSQL은 인덱스 생성과 동시에 테이블에서 읽기(SELECT 문)를 수행할 수 있도록 허용하지만, 인덱스 빌드가 완료될 때까지 쓰기(INSERT, UPDATE, DELETE)는 제한됩니다. 프로덕션 환경에서는 이러한 상황이 허용되지 않는 경우가 많으니 주의가 필요합니다. 인덱스 생성과 동시에 쓰기를 허용할 수는 있지만 몇 가지 주의해야 할 사항이 있습니다. 자세한 내용은 Building Indexes Concurrently를 참조하세요.
인덱스가 생성된 후에는 시스템에서 테이블과 동기화된 상태로 유지합니다. 데이터 조작 작업에 오버헤드가 추가됩니다. 인덱스는 heap-only tuples를 만들지 못하게 할 수도 있습니다. 따라서 쿼리에 거의 사용되지 않거나 전혀 사용되지 않는 인덱스는 제거해야 합니다.
Editor 선미’s comment
이 글의 원문은 PostgreSQL 공식문서 “Chapter 11. Indexes”, “Chapter 11. Indexes - 11.1. Introduction”입니다. PostgreSQL 버전 16을 기준으로 작성되었습니다.
단순 데이터 추출에서 넘어서 쿼리의 효율적인 실행에 대해 고민하고 있다면 지나칠 수 없는 토픽이 바로 이 인덱스입니다. 인덱스를 잘 사용하면 테이블을 순차적으로 모두 스캔하는(TABLE FULL SCAN)보다 훨씬 효율적으로 데이터 필터링, 조인 등을 할 수 있습니다. 책의 맨 뒷면의 색인 또는 목차에 종종 비유되는데 여러 방면에서 아주 적절한 비유라고 생각합니다. 하지만 이 글에도 쓰여있듯이, 인덱스 생성과 관리에는 컴퓨팅 자원이 필요합니다. 인덱스 자체로 디스크의 저장 공간을 사용하고, 인덱스를 생성하고 업데이트 하는 작업에 연산 자원도 활용되지요. 과도한 인덱스의 활용은 INSERT, UPDATE, DELETE 처리 속도가 느려지는 주 원인입니다. 잘 사용할 수 있는 방법을 고민해야 합니다.
이 번역 작업이 여러분이 인덱스에 대해 공부하는데 도움이 되었으면 좋겠습니다. PostgreSQL 공식 문서가 인덱스에 대해 잘 쓰여져 있어 이전부터 번역을 꼭 해오고 싶었는데요. 흔쾌히 허가해주신 PostgreSQL CoC(Code of Conduct)팀 감사합니다. 이해를 돕기 위해 역자의 의역과 주석이 섞여있으니 만약 번역과 원문 간에 차이가 있는 경우 원문을 우선적으로 생각해주세요. 번역 오류는 contact@datarian.io로 제보해주세요.


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

인덱스 타입: B-Tree, Hash

conversion_type

시리즈 내 순서

conversion_text

멤버 전용

대표 이미지

공개 가능

작성일

예상 읽기 시간

전환 텍스트

글쓴이

시리즈

상단 노출

전환 유형

요약

제목

작업을 하는 가까운 곳에 붙여주세요. SQL의 고급 문법과 친해지는데 도움이 됐으면 좋겠습니다.

실전 데이터 분석을 위한 SQL 치트 시트

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

인덱스 개요

인덱스 개요

Contents

함께 읽어보면 좋은 글

Google BigQuery (2) UNNEST의 필요성과 사용법

SQL 코딩 테스트 연습 사이트 4개 장단점 비교

인덱스와 ORDER BY

데이터 분석 소식을
한 달에 한 번 받아보세요

인덱스 개요

Contents

함께 읽어보면 좋은 글

Google BigQuery (2) UNNEST의 필요성과 사용법

SQL 코딩 테스트 연습 사이트 4개 장단점 비교

인덱스와 ORDER BY

데이터 분석 소식을 한 달에 한 번 받아보세요

데이터 분석 소식을
한 달에 한 번 받아보세요