Menu

PostgreSQL 인덱스 CREATE INDEX: 종류와 예제 정리

CREATE INDEX는 기본으로 B-tree 인덱스를 만들어, PostgreSQL이 테이블 전체를 읽지 않고 행을 찾게 합니다. 다른 종류(GIN, GiST, BRIN, hash)는 JSONB, 배열, 전문 검색, 범위, 아주 큰 테이블을 다룹니다.

이 페이지에는 실행 가능한 에디터가 있습니다. 편집하고 실행하면 결과를 바로 볼 수 있습니다.

CREATE INDEX name ON table (column);은 PostgreSQL이 테이블 전체를 읽지 않고도 맞는 행을 찾게 해 주는 인덱스를 만듭니다. 기본 종류는 B-tree이며 =, <, >, BETWEEN, IN, IS NULL, ORDER BY에 쓰입니다. 다른 종류는 USING으로 고릅니다: gin, gist, spgist, brin, hash.

실행 계획의 첫 줄은 Index Scan using users_email_idx on users입니다. PostgreSQL이 인덱스에서 값을 찾고 맞는 행만 읽습니다. CREATE INDEX 줄을 지우고 다시 실행하면 10,000행을 모두 읽는 Seq Scan on users가 보입니다.

행이 수십 개뿐인 테이블에서는 인덱스가 있어도 플래너가 순차 스캔을 고르는 일이 많습니다. 페이지 몇 개를 읽는 편이 더 싸기 때문입니다. 실행 계획 읽는 법은 EXPLAIN ANALYZE에서 설명합니다.

인덱스 종류 고르기

종류쓰는 곳
B-tree (기본)동등 비교, 범위, 정렬, LIKE 'prefix%'(C 콜레이션이나 text_pattern_ops 사용 시)
GIN여러 부분으로 된 값: jsonb(@>, ?), 배열(@>, &&), 전문 검색 tsvector
GiST기하 데이터, 범위(&&, 겹침), 전문 검색, 최근접 이웃 ORDER BY ... <->
SP-GiST겹치지 않는 영역으로 나뉘는 데이터: 점, IP 주소, 텍스트 접두사
BRIN값이 물리적 순서를 따르는 아주 큰 테이블, 예를 들어 추가만 되는 created_at
Hash동등 비교만 가능. B-tree가 같은 일과 그 이상을 하므로 거의 고르지 않음

jsonb 컬럼에 GIN 인덱스를 두면 포함 검사 쿼리가 인덱스를 씁니다:

실행 계획에는 GIN 인덱스에 대한 Bitmap Index Scan이 보이고, 이것이 맞는 행을 읽는 Bitmap Heap Scan으로 이어집니다. 연산자는 JSONB 페이지에서 다룹니다.

여러 컬럼에 인덱스 걸기

복합 B-tree 인덱스는 첫 번째 컬럼으로 정렬하고, 첫 번째 컬럼의 각 값 안에서 두 번째 컬럼으로 정렬합니다. 선두 컬럼으로 거르거나, 선두 컬럼과 그다음 컬럼들로 거르는 쿼리에 쓰입니다. 두 번째 컬럼만으로 거르면 얻는 것이 거의 없으니, 항상 =로 거르는 컬럼을 맨 앞에 두세요.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE(PostgreSQL 11+)는 정렬 기준으로 쓰지 않는 컬럼을 인덱스에 추가로 저장합니다: CREATE INDEX ON orders (customer_id) INCLUDE (total);. 그러면 그 컬럼들만 읽는 쿼리는 인덱스만으로 답할 수 있는데, 이것이 Index Only Scan입니다.

유니크 인덱스와 부분 인덱스: 일부 행에만 규칙 적용하기

CREATE UNIQUE INDEX는 중복 값을 거부합니다. 부분 인덱스는 WHERE를 더해 조건에 맞는 행만 인덱싱합니다. 크기가 더 작고, 유일성 규칙을 일부 행에만 적용할 수 있습니다:

둘 중 하나만 삭제되지 않은 행이므로 두 행 모두 들어갑니다. 같은 이메일로 세 번째 활성 행을 넣으면 duplicate key value violates unique constraint "accounts_email_live" 오류가 납니다. 쿼리의 WHERE가 인덱스 조건(여기서는 WHERE NOT deleted)을 함축할 때만 부분 인덱스를 씁니다.

식에 인덱스 걸기

인덱스에 식의 결과를 저장할 수 있습니다. 쿼리가 그 인덱스를 쓰려면 같은 식을 그대로 써야 하며, email에 건 인덱스는 WHERE lower(email) = ...에 도움이 되지 않습니다:

WHERE가 lower(email)을 그대로 반복하므로 실행 계획이 users_email_lower_idx를 씁니다. 식은 immutable이어야 하므로 now()나 설정에 따라 달라지는 함수는 거부됩니다.

운영 중인 데이터베이스에서 인덱스 조회, 생성, 삭제하기

pg_indexes는 모든 인덱스를, 그 인덱스를 다시 만드는 문장과 함께 보여 줍니다:

기본 키와 UNIQUE 제약 조건이 각자 인덱스 users_pkey와 users_email_key를 만들었습니다. psql에서는 \d users가 테이블 컬럼 아래에 같은 목록을 보여 줍니다.

쓰기가 들어오는 테이블에서는 CONCURRENTLY로 인덱스를 만들고 지워야 쓰기가 막히지 않습니다. 이 형태는 트랜잭션 블록 안에서 실행할 수 없습니다. 동시 생성이 실패하면 INVALID 인덱스가 남는데, 지우고 다시 만들어야 합니다:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

자주 묻는 질문

PostgreSQL은 인덱스를 자동으로 만드나요?

PRIMARY KEY, UNIQUE, 배제 제약 조건에만 만듭니다. 외래 키 컬럼에는 인덱스가 생기지 않으므로, 그 컬럼으로 조인하거나 참조되는 테이블에서 행을 지운다면 직접 추가하세요.

PostgreSQL이 왜 인덱스를 쓰지 않나요?

흔한 이유는 이렇습니다. 테이블이 작아 통째로 읽는 편이 더 싸다, 쿼리가 행의 큰 비율을 돌려준다, WHERE가 인덱스에 없는 함수나 형 변환으로 컬럼을 감쌌다, 통계가 오래되었다(ANALYZE를 실행하세요). 어떤 실행 계획이 선택되었는지는 EXPLAIN으로 볼 수 있습니다.

인덱스가 몇 개면 너무 많은 건가요?

정해진 수는 없습니다. 인덱스마다 일부 읽기는 빨라지지만 그 테이블의 모든 INSERT와 대부분의 UPDATE는 느려지고, 디스크 공간도 차지합니다. pg_stat_user_indexes.idx_scan이 인덱스마다 얼마나 쓰였는지 보여 주므로, 계속 0인 인덱스는 지울 수 있습니다.

CREATE INDEX는 테이블을 잠그나요?

일반 CREATE INDEX는 끝날 때까지 테이블의 INSERT, UPDATE, DELETE를 막지만, 읽기는 계속됩니다. CREATE INDEX CONCURRENTLY는 만드는 동안 쓰기를 허용하지만, 더 오래 걸리고 트랜잭션 블록 안에서 실행할 수 없습니다.

Coddy 프로그래밍 언어 일러스트

Coddy로 코딩 배우기

시작하기