CREATE INDEX

지정한 릴레이션(테이블 또는 구체화 뷰)의 특정 컬럼(들)에 새 인덱스(index)를 만드는 명령이에요. 데이터를 빠르게 조회할 수 있게 해주는 핵심 성능 도구입니다.

출처: PostgreSQL 문서

본문

문법 (Synopsis)

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

설명 (Description)

CREATE INDEX는 지정한 릴레이션(테이블 또는 구체화 뷰)의 지정한 컬럼(들)에 인덱스를 만듭니다. 인덱스는 주로 데이터베이스 성능을 높이는 데 쓰여요(부적절하게 쓰면 오히려 더 느려질 수 있습니다).

인덱스의 키 필드(들)는 컬럼 이름으로, 또는 괄호로 감싼 표현식으로 지정됩니다. 인덱스 메서드가 다중 컬럼 인덱스를 지원하면 여러 필드를 지정할 수 있어요.

인덱스 필드는 테이블 행의 한 개 이상 컬럼 값에서 계산된 표현식일 수 있습니다. 이 기능을 이용하면 기본 데이터를 어떤 방식으로 변환한 것을 기준으로 데이터에 빠르게 접근할 수 있어요. 예를 들어 upper(col)로 계산된 인덱스는 WHERE upper(col) = 'JIM' 절이 인덱스를 쓰게 해줍니다.

PostgreSQL은 B-tree, hash, GiST, SP-GiST, GIN, BRIN 인덱스 메서드를 제공합니다. 사용자가 자신만의 인덱스 메서드를 정의할 수도 있지만 꽤 복잡합니다.

WHERE 절이 있으면 부분 인덱스(partial index)가 만들어집니다. 부분 인덱스는 테이블의 일부에 대한 항목만 담는 인덱스로, 보통 나머지보다 인덱싱에 더 유용한 부분이에요. 예를 들어 청구된 주문과 청구되지 않은 주문이 모두 있는 테이블에서, 청구되지 않은 주문이 전체의 작은 비율이지만 자주 쓰이는 구간이라면 그 부분에만 인덱스를 만들어 성능을 높일 수 있습니다. 또 다른 응용으로 WHERE와 UNIQUE를 함께 써 테이블의 부분 집합에 대해 유일성을 강제하는 방법이 있어요. 자세한 논의는 11.8절을 참고하세요.

WHERE 절에 쓰인 표현식은 밑바탕 테이블의 컬럼만 참조할 수 있지만, 인덱싱되는 컬럼뿐 아니라 모든 컬럼을 쓸 수 있어요. 현재 WHERE에는 서브쿼리와 집계 표현식도 금지됩니다. 같은 제한이 표현식인 인덱스 필드에도 적용됩니다.

인덱스 정의에 쓰인 모든 함수와 연산자는 '불변(immutable)'이어야 해요. 즉 그 결과가 인자에만 의존하고 어떤 외부 영향(다른 테이블의 내용이나 현재 시간 같은)에도 의존하지 않아야 합니다. 이 제한은 인덱스의 동작이 잘 정의되도록 보장해요. 인덱스 표현식이나 WHERE 절에서 사용자 정의 함수를 쓰려면 함수를 만들 때 불변으로 표시하는 것을 기억하세요.

파라미터 (Parameters)

UNIQUE — 인덱스를 만들 때(데이터가 이미 있으면)와 데이터가 추가될 때마다 테이블의 중복 값을 검사하게 해요. 중복 항목이 되게 하는 삽입·갱신 시도는 오류를 만들어냅니다. 유일 인덱스를 파티션 테이블에 적용할 때는 추가 제한이 적용됩니다(see CREATE TABLE).
CONCURRENTLY — 이 옵션을 쓰면 PostgreSQL은 테이블에 대한 동시 삽입·갱신·삭제를 막는 잠금을 전혀 잡지 않고 인덱스를 만듭니다. 반면 표준 인덱스 빌드는 완료될 때까지 테이블에 쓰기(읽기는 아님)를 잠급니다. 이 옵션을 쓸 때 알아둘 주의점이 몇 가지 있습니다 — 아래 '인덱스를 동시에 만들기(Building Indexes Concurrently)'를 참고하세요. 임시 테이블의 경우 CREATE INDEX는 항상 비-동시입니다. 다른 세션이 접근할 수 없고 비-동시 인덱스 생성이 더 싸기 때문입니다.
IF NOT EXISTS — 같은 이름의 릴레이션이 이미 있어도 오류를 내지 않아요. 이 경우 공지(notice)만 발행됩니다. 다만 기존 인덱스가 만들려던 인덱스와 같을 거라는 보장은 없습니다. IF NOT EXISTS를 지정하면 인덱스 이름이 필수예요.
INCLUDE — 선택적 INCLUDE 절은 비-키(non-key) 컬럼으로 인덱스에 포함될 컬럼 목록을 지정해요. 비-키 컬럼은 인덱스 스캔 검색 조건에 쓸 수 없고, 인덱스가 강제하는 어떤 유일성·제외 제약을 위해서는 무시됩니다. 하지만 인덱스 전용 스캔(index-only scan)은 인덱스 항목에서 직접 사용할 수 있으므로 인덱스의 테이블을 방문하지 않고 비-키 컬럼의 내용을 반환할 수 있어요. 따라서 비-키 컬럼을 추가하면 그렇지 않으면 쓸 수 없었던 쿼리에 인덱스 전용 스캔을 쓸 수 있게 됩니다. 특히 넓은 컬럼은 인덱스에 비-키 컬럼을 추가하는 데 보수적이 되는 게 현명해요. 인덱스 튜플이 인덱스 유형에 허용된 최대 크기를 초과하면 데이터 삽입이 실패합니다. 어쨌든 비-키 컬럼은 인덱스의 테이블 데이터를 중복해서 인덱스 크기를 부풀려 검색을 느리게 할 가능성이 있어요. 또한 B-tree 중복 제거(deduplication)는 비-키 컬럼이 있는 인덱스에는 결코 사용되지 않습니다. INCLUDE 절에 나열된 컬럼은 적절한 연산자 클래스가 필요 없어요. 클래스는 주어진 접근 메서드에 대한 연산자 클래스가 정의되지 않은 데이터 타입의 컬럼도 포함할 수 있습니다. 포함된 컬럼으로 표현식은 지원되지 않는데, 인덱스 전용 스캔에 쓸 수 없기 때문이에요. 현재 B-tree, GiST, SP-GiST 인덱스 접근 메서드가 이 기능을 지원합니다. 이 인덱스들에서 INCLUDE 절에 나열된 컬럼의 값은 힙 튜플에 해당하는 리프 튜플에 포함되지만, 트리 탐색에 쓰이는 상위 수준 인덱스 항목에는 포함되지 않아요.
name — 만들 인덱스의 이름. 여기에는 스키마 이름을 넣을 수 없습니다. 인덱스는 항상 부모 테이블과 같은 스키마에 만들어져요. 인덱스 이름은 그 스키마의 다른 어떤 릴레이션(테이블, 시퀀스, 인덱스, 뷰, 구체화 뷰, 외부 테이블) 이름과 달라야 합니다. 이름을 생략하면 PostgreSQL은 부모 테이블 이름과 인덱싱된 컬럼 이름을 바탕으로 적절한 이름을 고릅니다.
ONLY — 테이블이 파티션되어 있으면 파티션에 인덱스를 만드는 재귀를 하지 않음을 나타냄. 기본은 재귀하는 것입니다.
table_name — 인덱싱할 테이블의 이름(스키마 한정 가능).
method — 사용할 인덱스 메서드의 이름. 선택지는 btree, hash, gist, spgist, gin, brin, 또는 bloom 같은 사용자 설치 접근 메서드입니다. 기본 메서드는 btree예요.
column_name — 테이블의 컬럼 이름.
expression — 테이블의 한 개 이상 컬럼에 기반한 표현식. 문법에 표시된 것처럼 보통 주변에 괄호를 써야 해요. 하지만 표현식이 함수 호출 형태이면 괄호를 생략할 수 있습니다.
collation — 인덱스에 사용할 콜레이션의 이름. 기본적으로 인덱스는 인덱싱될 컬럼에 선언된 콜레이션이나 인덱싱될 표현식의 결과 콜레이션을 사용합니다. 비-기본 콜레이션을 가진 인덱스는 비-기본 콜레이션을 쓰는 표현식이 포함된 쿼리에 유용할 수 있어요.
opclass — 연산자 클래스의 이름. 자세한 내용은 아래를 참고하세요.
opclass_parameter — 연산자 클래스 파라미터의 이름. 자세한 내용은 아래를 참고하세요.
ASC — 오름차순 정렬 순서를 지정함(기본).
DESC — 내림차순 정렬 순서를 지정함.
NULLS FIRST — null이 비-null보다 앞에 정렬되도록 지정함. DESC를 지정했을 때의 기본입니다.
NULLS LAST — null이 비-null보다 뒤에 정렬되도록 지정함. DESC를 지정하지 않았을 때의 기본입니다.
NULLS DISTINCT / NULLS NOT DISTINCT — 유일 인덱스에서 null 값이 서로 다르게(같지 않게) 간주되어야 하는지 지정해요. 기본은 서로 다르게 간주하는 것이어서, 유일 인덱스가 한 컬럼에 여러 null 값을 포함할 수 있습니다.
storage_parameter — 인덱스 메서드 특정 저장 파라미터의 이름. 아래 '인덱스 저장 파라미터(Index Storage Parameters)'를 참고하세요.
tablespace_name — 인덱스를 만들 테이블스페이스. 지정하지 않으면 default_tablespace를 따르고, 임시 테이블의 인덱스는 temp_tablespaces를 따릅니다.
predicate — 부분 인덱스의 제약 표현식.

인덱스 저장 파라미터 (Index Storage Parameters)

선택적 WITH 절은 인덱스의 저장 파라미터를 지정해요. 각 인덱스 메서드는 자신만의 허용 저장 파라미터 집합을 가집니다.

B-tree, hash, GiST, SP-GiST 인덱스 메서드는 모두 이 파라미터를 받아들입니다:

fillfactor (integer) — 인덱스 메서드가 인덱스 페이지를 얼마나 꽉 채우려고 할지 제어해요. B-tree는 초기 인덱스 빌드 때와 오른쪽으로 인덱스를 확장할 때(새로운 최대 키 값 추가) 리프 페이지를 이 백분율까지 채웁니다. 이후 페이지가 완전히 꽉 차면 분할되어 디스크상의 인덱스 구조가 단편화됩니다. B-tree는 기본 fillfactor 90을 쓰지만 10에서 100 사이의 어떤 정수 값도 선택할 수 있어요. 삽입/갱신이 많이 예상되는 테이블의 B-tree 인덱스는 (테이블에 대량 적재를 한 뒤) CREATE INDEX 시점에 더 낮은 fillfactor 설정이 도움이 될 수 있습니다. 50-90 범위의 값은 B-tree 인덱스 초기에 페이지 분할 속도를 유용하게 '완화'할 수 있어요(이렇게 fillfactor를 낮추면 페이지 분할의 절대 횟수까지 낮출 수 있지만 이 효과는 워크로드에 크게 의존합니다). 65.1.4.2절에서 설명하는 B-tree 상향식 인덱스 삭제 기법은 페이지에 '추가' 튜플 버전을 저장할 '여유' 공간이 있는 것에 의존하므로 fillfactor의 영향을 받을 수 있습니다(보통 그 영향은 크지 않지만). 다른 특정 경우에는 공간 활용을 극대화하는 방법으로 CREATE INDEX 시점에 fillfactor를 100으로 높이는 게 유용할 수 있어요. 테이블이 정적(즉 삽입/갱신의 영향을 결코 받지 않을)이라고 완전히 확신할 때만 이걸 고려해야 합니다. 그 외에는 fillfactor 100 설정이 성능을 해칠 위험이 있어요. 몇 번의 갱신이나 삽입만으로도 페이지 분할이 갑자기 쏟아질 수 있기 때문입니다. 다른 인덱스 메서드는 fillfactor를 다르지만 대략 유사한 방식으로 쓰며, 기본 fillfactor는 메서드마다 다릅니다.
deduplicate_items (boolean) — 65.1.4.3절에서 설명하는 B-tree 중복 제거 기법 사용을 제어해요. ON 또는 OFF로 설정해 최적화를 켜거나 끕니다. (ON과 OFF의 대체 철자는 19.1절에서 설명하는 대로 허용됩니다.) 기본은 ON이에요. 참고: ALTER INDEX로 deduplicate_items를 끄면 미래의 삽입이 중복 제거를 촉발하지 못하게 하지, 기존 posting list 튜플이 표준 튜플 표현을 쓰게 만들지는 않습니다.

GiST 인덱스는 추가로 이 파라미터를 받아들입니다:

buffering (enum) — 65.2.4.1절에서 설명하는 버퍼링 빌드 기법으로 인덱스를 만들지 제어해요. OFF는 버퍼링을 비활성화, ON은 활성화, AUTO는 처음엔 비활성화되지만 인덱스 크기가 effective_cache_size에 도달하면 그 자리에서 켭니다. 기본은 AUTO예요. 정렬 빌드가 가능하면 buffering=ON을 지정하지 않는 한 버퍼링 빌드 대신 정렬 빌드가 쓰인다는 점을 참고하세요.

GIN 인덱스는 이 파라미터들을 받아들입니다:

fastupdate (boolean) — 65.4.4.1절에서 설명하는 빠른 갱신 기법 사용을 제어해요. ON은 빠른 갱신을 켜고 OFF는 끕니다. 기본은 ON이에요. 참고: ALTER INDEX로 fastupdate를 끄면 미래의 삽입이 대기 중인(pending) 인덱스 항목 목록으로 들어가지 못하게 하지, 기존 항목을 비우지는 않습니다. 대기 목록이 비워지도록 이후에 테이블을 VACUUM하거나 gin_clean_pending_list 함수를 호출하고 싶을 수 있어요.
gin_pending_list_limit (integer) — 이 인덱스에 대한 gin_pending_list_limit의 전역 설정을 덮어씁니다. 이 값은 킬로바이트로 지정됩니다.

BRIN 인덱스는 이 파라미터들을 받아들입니다:

pages_per_range (integer) — BRIN 인덱스의 각 항목에 대해 하나의 블록 범위를 이루는 테이블 블록 수를 정의해요(자세한 내용은 65.5.1절 참고). 기본은 128입니다.
autosummarize (boolean) — 다음 페이지 범위에서 삽입이 감지될 때마다 이전 페이지 범위에 대한 요약 실행을 큐에 넣을지 정의해요(자세한 내용은 65.5.1.1절 참고). 기본은 off입니다.

인덱스를 동시에 만들기 (Building Indexes Concurrently)

인덱스를 만드는 것은 데이터베이스의 정상 운영을 방해할 수 있어요. 보통 PostgreSQL은 인덱싱할 테이블을 쓰기에 대해 잠그고 단일 테이블 스캔으로 전체 인덱스 빌드를 수행합니다. 다른 트랜잭션은 여전히 테이블을 읽을 수 있지만, 행을 삽입·갱신·삭제하려 하면 인덱스 빌드가 끝날 때까지 막힙니다. 시스템이 운영 중인 프로덕션 데이터베이스라면 이는 심각한 영향을 줄 수 있어요. 매우 큰 테이블은 인덱싱에 여러 시간이 걸릴 수 있고, 더 작은 테이블에서도 인덱스 빌드가 프로덕션 시스템에 받아들일 수 없을 정도로 오래 writer를 잠글 수 있습니다.

PostgreSQL은 쓰기를 잠그지 않고 인덱스를 만드는 것을 지원합니다. 이 방법은 CREATE INDEX의 CONCURRENTLY 옵션을 지정해 호출됩니다. 이 옵션을 쓰면 PostgreSQL은 테이블 스캔을 두 번 수행해야 하고, 게다가 인덱스를 잠재적으로 수정하거나 사용할 수 있는 모든 기존 트랜잭션이 종료될 때까지 기다려야 해요. 따라서 이 방법은 표준 인덱스 빌드보다 전체 작업이 더 필요하고 완료에 훨씬 오래 걸립니다. 하지만 인덱스를 만드는 동안 정상 운영이 계속되게 하므로, 프로덕션 환경에서 새 인덱스를 추가하는 데 유용해요. 물론 인덱스 생성이 부과하는 추가 CPU·I/O 부하가 다른 연산을 느리게 할 수 있습니다.

동시 인덱스 빌드에서 인덱스는 실제로 한 트랜잭션에서 시스템 카탈로그에 '무효(invalid)' 인덱스로 들어가고, 그다음 두 개의 더 많은 트랜잭션에서 두 번의 테이블 스캔이 일어납니다. 각 테이블 스캔 전에 인덱스 빌드는 테이블을 수정한 기존 트랜잭션들이 종료될 때까지 기다려야 해요. 두 번째 스캔 후에는 두 번째 스캔보다 먼저인 스냅샷(13장 참고)을 가진 트랜잭션들이 종료될 때까지 기다려야 합니다. 여기에는 관련 인덱스가 부분 인덱스이거나 단순 컬럼 참조가 아닌 컬럼을 가진 경우, 다른 테이블의 동시 인덱스 빌드의 어떤 단계가 쓴 트랜잭션도 포함됩니다. 그러고 나서 마침내 인덱스가 '유효'로 표시되고 사용 준비가 되며 CREATE INDEX 명령이 종료됩니다. 하지만 그래도 인덱스가 쿼리에 즉시 사용 가능하지 않을 수 있어요. 최악의 경우 인덱스 빌드 시작보다 앞선 트랜잭션이 존재하는 한 사용할 수 없습니다.

테이블을 스캔하는 동안 교착 상태나 유일 인덱스의 유일성 위반 같은 문제가 생기면 CREATE INDEX 명령은 실패하지만 '무효' 인덱스를 남겨둡니다. 이 인덱스는 불완전할 수 있으므로 쿼리 목적으로 무시되지만, 여전히 갱신 오버헤드를 소모합니다. psql \d 명령은 그런 인덱스를 INVALID로 보고합니다:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

이런 경우에 권장되는 복구 방법은 인덱스를 삭제하고 CREATE INDEX CONCURRENTLY를 다시 시도하는 것입니다. (또 다른 가능성은 REINDEX INDEX CONCURRENTLY로 인덱스를 다시 만드는 것입니다.)

유일 인덱스를 동시에 만들 때 또 다른 주의점은, 두 번째 테이블 스캔이 시작될 때 유일성 제약이 이미 다른 트랜잭션들에 대해 강제되고 있다는 것입니다. 이는 인덱스가 사용 가능해지기 전에, 또는 인덱스 빌드가 결국 실패하더라도, 제약 위반이 다른 쿼리에서 보고될 수 있음을 의미해요. 또한 두 번째 스캔에서 실패가 발생하면 '무효' 인덱스가 이후에도 그 유일성 제약을 계속 강제합니다.

표현식 인덱스와 부분 인덱스의 동시 빌드는 지원돼요. 이 표현식 평가에서 발생한 오류는 위에서 설명한 유일 제약 위반과 비슷한 동작을 일으킬 수 있습니다.

일반 인덱스 빌드는 같은 테이블에서 다른 일반 인덱스 빌드가 동시에 일어나는 것을 허용하지만, 한 테이블에서 동시 인덱스 빌드는 한 번에 하나만 일어날 수 있어요. 두 경우 모두 인덱스를 만드는 동안 테이블의 스키마 수정은 허용되지 않습니다. 또 다른 차이는 일반 CREATE INDEX 명령은 트랜잭션 블록 안에서 수행될 수 있지만 CREATE INDEX CONCURRENTLY는 그럴 수 없다는 점입니다.

파티션 테이블의 인덱스에 대한 동시 빌드는 현재 지원되지 않아요. 하지만 각 파티션에 인덱스를 개별적으로 동시에 만든 다음 최종적으로 파티션 인덱스를 비-동시로 만들어, 파티션 테이블에 대한 쓰기가 잠기는 시간을 줄일 수 있습니다. 이 경우 파티션 인덱스를 만드는 것은 메타데이터 전용 연산입니다.

주의 사항 (Notes)

인덱스를 언제 쓸 수 있고 언제 쓰지 않는지, 어떤 특정 상황에서 유용한지에 대한 정보는 11장을 참고하세요.

현재 B-tree, GiST, GIN, BRIN 인덱스 메서드만 다중 키-컬럼 인덱스를 지원합니다. 키 컬럼이 여러 개일 수 있는지는 INCLUDE 컬럼을 인덱스에 추가할 수 있는지와는 독립적이에요. 인덱스는 INCLUDE 컬럼을 포함해 최대 32개 컬럼을 가질 수 있습니다. (이 한도는 PostgreSQL을 빌드할 때 바꿀 수 있습니다.) 유일 인덱스를 지원하는 것은 현재 B-tree뿐입니다.

인덱스의 각 컬럼에 대해 선택적 파라미터가 있는 연산자 클래스를 지정할 수 있어요. 연산자 클래스는 그 컬럼에 대해 인덱스가 사용할 연산자를 식별합니다. 예를 들어 4바이트 정수의 B-tree 인덱스는 int4_ops 클래스를 사용할 것인데, 이 연산자 클래스는 4바이트 정수용 비교 함수를 포함합니다. 실제로는 컬럼 데이터 타입의 기본 연산자 클래스로 충분한 경우가 보통이에요. 연산자 클래스를 두는 주된 이유는 일부 데이터 타입에는 의미 있는 순서가 두 개 이상 있을 수 있기 때문입니다. 예를 들어 복소수 데이터 타입을 절대값으로 정렬할지 실수부로 정렬할지 정하고 싶을 수 있어요. 데이터 타입에 연산자 클래스를 두 개 정의한 다음 인덱스를 만들 때 적절한 클래스를 선택하면 됩니다. 연산자 클래스에 대한 자세한 정보는 11.10절과 36.16절에 있습니다.

CREATE INDEX가 파티션 테이블에 대해 호출되면 기본 동작은 모든 파티션으로 재귀해 모두 일치하는 인덱스를 갖도록 하는 것입니다. 각 파티션은 먼저 동등한 인덱스가 이미 존재하는지 검사되고, 있으면 그 인덱스가 파티션 인덱스로 만들어지는 인덱스에 붙어 그 인덱스가 부모 인덱스가 됩니다. 일치하는 인덱스가 없으면 새 인덱스가 만들어져 자동으로 붙는데, 각 파티션의 새 인덱스 이름은 명령에서 인덱스 이름을 지정하지 않은 것처럼 결정됩니다. ONLY 옵션을 지정하면 재귀가 수행되지 않고 인덱스는 무효로 표시됩니다. (ALTER INDEX ... ATTACH PARTITION은 모든 파티션이 일치하는 인덱스를 얻으면 인덱스를 유효로 표시합니다.) 단, CREATE TABLE ... PARTITION OF로 미래에 만들어지는 어떤 파티션이든 ONLY를 지정했는지와 무관하게 자동으로 일치하는 인덱스를 갖게 된다는 점을 참고하세요.

순서 있는 스캔을 지원하는 인덱스 메서드(현재 B-tree만)에서는 선택적 ASC, DESC, NULLS FIRST, NULLS LAST 절을 지정해 인덱스의 정렬 순서를 바꿀 수 있어요. 순서 있는 인덱스는 앞뒤로 스캔할 수 있으므로 단일 컬럼 DESC 인덱스를 만드는 것은 보통 유용하지 않아요. 그 정렬 순서는 이미 일반 인덱스로 사용할 수 있기 때문입니다. 이 옵션들의 가치는 혼합 순서 쿼리(예: SELECT ... ORDER BY x ASC, y DESC)가 요청한 정렬 순서에 맞는 다중 컬럼 인덱스를 만들 수 있다는 것입니다. NULLS 옵션은 인덱스에 의존해 정렬 단계를 피하는 쿼리에서 기본인 'nulls sort high' 대신 'nulls sort low' 동작을 지원해야 할 때 유용합니다.

시스템은 테이블의 모든 컬럼에 대한 통계를 정기적으로 수집합니다. 새로 만들어진 비-표현식 인덱스는 즉시 이 통계를 사용해 인덱스의 유용성을 결정할 수 있어요. 새 표현식 인덱스의 경우 이 인덱스들을 위한 통계를 생성하도록 ANALYZE를 실행하거나 autovacuum 데몬이 테이블을 분석하기를 기다려야 합니다.

CREATE INDEX가 실행되는 동안 search_path는 일시적으로 pg_catalog, pg_temp로 바뀝니다.

대부분의 인덱스 메서드에서 인덱스 생성 속도는 maintenance_work_mem 설정에 의존합니다. 값이 클수록 인덱스 생성에 필요한 시간이 줄어들지만, 실제로 사용 가능한 메모리 양보다 크게 만들어 머신을 스와핑으로 몰아넣지 않도록 주의해야 해요.

PostgreSQL은 여러 CPU를 활용해 테이블 행을 더 빨리 처리하면서 인덱스를 만들 수 있어요. 이 기능을 병렬 인덱스 빌드(parallel index build)라고 합니다. 병렬로 인덱스를 만드는 것을 지원하는 인덱스 메서드(현재 B-tree, GIN, BRIN)에서 maintenance_work_mem은 worker 프로세스가 몇 개 시작됐는지와 무관하게 각 인덱스 빌드 연산이 전체적으로 사용할 수 있는 최대 메모리 양을 지정합니다. 일반적으로 비용 모델이 worker 프로세스를 몇 개 요청할지(있다면) 자동으로 결정합니다.

병렬 인덱스 빌드는 동등한 직렬 인덱스 빌드가 이점을 거의 못 보는 곳에서 maintenance_work_mem을 늘리는 이점을 볼 수 있어요. maintenance_work_mem은 요청되는 worker 프로세스 수에 영향을 줄 수 있는데, 병렬 worker는 총 maintenance_work_mem 예산의 최소 32MB 몫을 가져야 하기 때문입니다. 리더 프로세스에도 32MB 몫이 남아 있어야 합니다. max_parallel_maintenance_workers를 늘리면 더 많은 worker를 쓸 수 있어, 인덱스 빌드가 이미 I/O에 묶여 있지 않다면 인덱스 생성 시간을 줄여줍니다. 물론 그렇지 않으면 놀고 있을 충분한 CPU 용량도 있어야 합니다.

ALTER TABLE로 parallel_workers 값을 설정하면 그 테이블에 대한 CREATE INDEX가 요청할 병렬 worker 프로세스 수를 직접 제어해요. 이는 비용 모델을 완전히 우회하고 maintenance_work_mem이 요청되는 병렬 worker 수에 영향을 주는 것을 막습니다. ALTER TABLE로 parallel_workers를 0으로 설정하면 어떤 경우에도 테이블의 병렬 인덱스 빌드를 비활성화합니다.

팁

인덱스 빌드 튜닝의 일부로 parallel_workers를 설정한 뒤에는 리셋하고 싶을 수 있어요. parallel_workers는 모든 병렬 테이블 스캔에 영향을 주므로, 쿼리 계획의 우발적 변경을 피하는 데 도움이 됩니다.

CONCURRENTLY 옵션을 가진 CREATE INDEX는 특별한 제한 없이 병렬 빌드를 지원하지만, 실제로 병렬로 수행되는 것은 첫 번째 테이블 스캔뿐이에요.

인덱스를 제거하려면 DROP INDEX를 사용하세요.

다른 장기 실행 트랜잭션과 마찬가지로, 테이블에 대한 CREATE INDEX는 다른 테이블의 동시 VACUUM이 제거할 수 있는 튜플에 영향을 줄 수 있어요.

이전 PostgreSQL 릴리스에는 R-tree 인덱스 메서드도 있었습니다. 이 메서드는 GiST 메서드에 비해 중요한 이점이 없어서 제거되었어요. USING rtree가 지정되면 CREATE INDEX는 오래된 데이터베이스를 GiST로 변환하기 쉽도록 그것을 USING gist로 해석합니다.

CREATE INDEX를 실행하는 각 백엔드는 pg_stat_progress_create_index 뷰에 진행 상황을 보고합니다. 자세한 내용은 27.4.4절을 참고하세요.

예제 (Examples)

films 테이블의 title 컬럼에 유일한 B-tree 인덱스 만들기:

CREATE UNIQUE INDEX title_idx ON films (title);

films 테이블의 title 컬럼에 포함 컬럼 director와 rating을 둔 유일한 B-tree 인덱스 만들기:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

중복 제거를 끈 B-Tree 인덱스 만들기:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

lower(title) 표현식에 인덱스 만들어 대소문자 무시 검색을 효율적으로 하기:

CREATE INDEX ON films ((lower(title)));

(이 예에서는 인덱스 이름을 생략하기로 했으므로, 시스템이 보통 films_lower_idx 같은 이름을 고릅니다.)

비-기본 콜레이션으로 인덱스 만들기:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

비-기본 null 정렬 순서로 인덱스 만들기:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

비-기본 fillfactor로 인덱스 만들기:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

빠른 갱신을 끈 GIN 인덱스 만들기:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

films 테이블의 code 컬럼에 인덱스를 만들고 그 인덱스가 indexspace 테이블스페이스에 있게 하기:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

point 속성에 GiST 인덱스를 만들어 변환 함수 결과에 박스 연산자를 효율적으로 쓰기:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

테이블에 대한 쓰기를 잠그지 않고 인덱스 만들기:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

호환성 (Compatibility)

CREATE INDEX는 PostgreSQL 언어 확장이에요. SQL 표준에는 인덱스에 대한 규정이 없습니다.

함께 보기 (See Also)

ALTER INDEX, DROP INDEX, REINDEX, Section 27.4.4

더 알아보기 (Learn more)

DROP INDEX: 인덱스를 제거하는 명령.
REINDEX: 기존 인덱스를 다시 만드는 명령.
ALTER INDEX: 기존 인덱스의 정의를 변경하는 명령.