인덱스 개요

인덱스 개요 (Introduction to Indexes)

테이블이 커질수록 원하는 행을 찾는 일이 점점 느려지기 시작하면 "인덱스를 만들어야겠다"는 생각이 들죠. 이 페이지에서는 인덱스가 왜 필요한지, 어떤 형태로 동작하는지 기본 개념을 정리해 볼게요.

출처: 공식문서

인덱스의 필요성: 전체 스캔은 비효율적이에요

다음과 같은 테이블이 있다고 가정해 볼게요.

CREATE TABLE test1 (
    id integer,
    content varchar
);

이제 애플리케이션이 이런 쿼리를 자주 수행한다고 해요.

SELECT content FROM test1 WHERE id = 'constant';

아무 준비가 없다면 시스템은 test1 테이블 전체를 행 하나하나 훑어서(전체 스캔) 일치하는 행을 찾아야 해요. 테이블에 행이 많고, 결과로 돌아오는 행이 몇 개(어쩌면 0개나 1개)뿐이라면 이건 확실히 비효율적이죠. 하지만 시스템에 id 컬럼에 대한 인덱스를 유지하라고 지시하면, 일치하는 행을 찾는 더 효율적인 방법을 쓸 수 있어요. 가령 검색 트리를 몇 단계만 내려가면 되는 식이죠.

비소설 책의 색인에 비유해 봐요

이 접근 방식은 대부분의 비소설 책에서 쓰는 방식과 같아요. 독자들이 자주 찾는 용어·개념을 책 맨 뒤에 알파벳순 색인으로 모아 두죠. 관심 있는 독자는 책 전체를 읽을 필요 없이 색인을 훑어서 해당 페이지로 바로 넘어갈 수 있어요. 저자가 독자들이 찾을 만한 항목을 미리 예상하는 것처럼, 데이터베이스 프로그래머의 역할도 어떤 인덱스가 유용할지 미리 내다보는 거예요.

인덱스 만들고, 제거하고, 신경 쓸 일

id 컬럼에 인덱스를 만들려면 아래처럼 하면 돼요.

CREATE INDEX test1_id_index ON test1 (id);

test1_id_index 라는 이름은 자유롭게 정할 수 있는데, 나중에 이 인덱스가 뭘 위해 만들어졌는지 기억할 수 있도록 의미 있는 이름을 고르는 게 좋아요.

인덱스를 제거하려면 DROP INDEX 를 쓰면 되고, 인덱스는 테이블에 언제든 추가하거나 제거할 수 있어요.

한번 만든 인덱스는 그 뒤로 별다른 조치가 필요 없어요. 시스템이 테이블이 수정될 때 인덱스를 자동으로 갱신하고, 쿼리에서 전체 스캔보다 효율적이라고 판단되면 인덱스를 사용해서 쿼리를 처리하거든요. 다만 쿼리 플래너가 현명한 결정을 내리도록 통계를 갱신하려면 ANALYZE 명령을 주기적으로 실행해 주는 게 좋아요. 인덱스가 실제로 사용되는지, 왜 사용되지 않는지를 확인하는 방법은 관련 문서 챕터를 참고하세요.

인덱스가 도움이 되는 쿼리

인덱스는 검색 조건이 붙은 UPDATE 와 DELETE 명령에서도 이득이에요. 조인 검색에도 쓸 수 있고, 조인 조건에 포함된 컬럼에 정의한 인덱스는 조인이 있는 쿼리를 크게 빨라지게 해줘요.

일반적으로 PostgreSQL 인덱스는 다음 형태의 WHERE 또는 JOIN 절이 하나 이상 들어있는 쿼리를 최적화하는 데 쓸 수 있어요.

indexed-column  indexable-operator  comparison-value

여기서 indexed-column은 인덱스가 정의된 컬럼이나 표현식이고, indexable-operator는 그 인덱스 컬럼의 연산자 클래스에 속하는 연산자예요. comparison-value는 휘발성(volatile)이 아니고 인덱스 테이블을 참조하지 않는 어떤 표현식이든 됩니다.

쿼리 플래너는 이런 형태의 인덱스 가능 절을 다른 SQL 구문에서 뽑아낼 수도 있어요. 가령 comparison-value operator indexed-column 형태의 절은, 원래 연산자에 그 인덱스 연산자 클래스의 멤버인 교환 연산자(commutator)가 있다면 인덱스 가능한 형태로 뒤집을 수 있죠.

인덱스 생성의 비용

큰 테이블에 인덱스를 만드는 일은 오래 걸릴 수 있어요. 기본적으로 PostgreSQL은 인덱스 생성 중에도 테이블에 대한 읽기(SELECT)는 병렬로 허용하지만, 쓰기(INSERT, UPDATE, DELETE)는 인덱스 빌드가 끝날 때까지 차단해요. 운영 환경에서는 이게 받아들여지기 어려울 때가 많은데요, 쓰기를 병렬로 허용하는 방법도 있지만 몇 가지 주의사항이 있으니 관련 문서의 "Building Indexes Concurrently"를 참고하세요.

인덱스가 만들어지면 시스템은 인덱스를 테이블과 동기화된 상태로 유지해야 해요. 이건 데이터 조작 연산에 부담(오버헤드)을 더하고, 인덱스는 힙 전용 튜플(heap-only tuple) 생성까지 막을 수 있어요. 그래서 쿼리에서 거의 또는 전혀 사용되지 않는 인덱스는 제거하는 게 좋아요.

인덱스 개요