CREATE INDEX

CREATE INDEX — 새 인덱스 정의

요약

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] 이름 ] ON [ ONLY ] 테이블이름 [ USING 색인방법 ]
    ( { 칼럼이름 | ( 표현식 ) } [ COLLATE 문자정렬규칙 ] [ 연산자클래스 [ ( 연산자클래스_매개변수 =  [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( 칼럼이름 [, ...] ) ]
    [ WITH ( 저장_매개변수 [= ] [, ... ] ) ]
    [ TABLESPACE 테이블스페이스이름 ]
    [ WHERE 조건절 ]

설명

CREATE INDEX 명령은 해당 릴레이션 (테이블이나 구체화한 뷰)의 특정 칼럼을 기준으로 인덱스를 만든다. 인덱스를 만드는 첫번째 목적은 데이터베이스 성능을 높이는데 있다. (물론 잘못 사용하면 성능을 떨어뜨리기도 한다.)

인덱스 키 필드는 칼럼 이름이나, 어떤 계산식을 사용한다. 색인 방법이 다중 칼럼 인덱스를 지원하면, 다중 필드를 지정해서 인덱스를 만들 수 있다. (색인 방법: btree, hash ... 이런 인덱스 종류를 말한다 - 옮긴이)

인덱스 필드로 사용할 수 있는 계산식은 그 계산 결과가 인덱스 자료로 사용되는 것이다. 그 계산 되는 자료는 테이블의 특정 칼럼일 수도 있고, 여러 칼럼일 수도 있다. 이 기능은 조회 쿼리에서 어떤 계산식에 의한 결과를 빠르게 찾는데 도움을 준다. 예를 들어, 대소문자를 구분하지 않고, 무조건 대문자로 어떤 자료를 찾으려고 할때, 인덱스 필드로 upper(col) 계산식을 사용해서 인덱스를 만들고, 조회 쿼리에서 WHERE upper(col) = 'JIM' 구문을 사용하면, 그 인덱스를 사용할 수 있다.

PostgreSQL에서 기본 제공하는 색인 방법은 B-tree, hash, GiST, SP-GiST, GIN, BRIN 이다. 또한 사용자가 직접 이 색인 방법을 만들어 사용할 수 있지만, 상당히 복잡한 과정을 거친다.

인덱스를 만드는 구문에서 WHERE 조건절을 사용해서 부분 인덱스를 만들 수도 있다. 부분 인덱스란 테이블 전체 자료를 대상으로 하지 않고, 특정 부분 자료를 대상으로 인덱스를 만드는 것이다. 예를 들어, 주문 테이블에서 청구 상태 기준으로 미청구 주문이 적고, 청구 주문이 많은데, 업무에서는 이 미청구 주문을 대상으로 하는 작업이 많은 경우, 인덱스를 미청구 주문을 대상으로만 만들어 성능을 높일 수 있다. (인덱스 크기가 줄어들 것이며, 그 만큼 디스크 사용량이 줄기 때문이다 - 옮긴이) 또 다른 사용법으로, UNIQUE 인덱스를 만들 수 없는 자료인데, WHERE 조건절을 이용해서, 유일성을 보장하는 인덱스로 만들어 사용하는 것이다. 이 부분은 11.8절에서 자세히 다룬다.

단순 인덱스는 단일, 또는 지정한 칼럼들만 대상으로 하지만, WHERE 조건절을 하면 그 인덱스용 칼럼 뿐만 아니라, 그 외 칼럼들에 대해서도 조건을 적용할 수 있어 꽤 유용하다. 현재, 이 WHERE 조건절에서는 서브쿼리와 집계 작업용 계산식은 사용할 수 없다. 이 제약은 인덱스 칼럼 대신 사용할 수 있는 계산식에서도 같다.

인덱스를 만들 때 사용하는 함수나 연산자는 반드시 immutable 속성이 있어야 하는 것이여야 한다. 이 속성은 입력에 대한 출력값이 항상 같다는 것을 뜻한다(어떤 테이블을 참조하거나, 현재 시간을 출력하는 작업은 이런 경우가 아니다). 이렇게 immutable 속성이 있어야 인덱스 역할을 제대로 하기 때문이다. 사용자 정의 함수를 사용해서 인덱스 필드로 사용하거나, WHERE 조건절에서 사용하려고 한다면, 반드시 이 점을 기억해야 한다.

매개 변수

UNIQUE

유니크 인덱스를 만들 때 사용. 해당 필드 값은 해당 테이블에 유일하게 있어야 함을 뜻한다. 같은 자료를 입력 하거나, 이미 있는 값으로 바꾸려고 하면 오류로 처리한다.

하위 파티션 테이블에 유니크 인덱스를 적용할 때는 추가 제한이 있다. CREATE TABLE 참조.

CONCURRENTLY

이 옵션을 사용하면, PostgreSQL 서버는 해당 테이블의 insert, update, delete 작업을 허용하면서 인덱스를 만든다. 일반적 인덱스 만들기는 해당 테이블을 읽기 전용으로 잠근다. 이 옵션을 사용할 때는 여러 제약 조건들이 있다. 이 옵션을 사용하려면 테이블을 잠그지 않는 인덱스 만들기 설명서를 꼭 읽고 사용해야 한다.

For temporary tables, CREATE INDEX is always non-concurrent, as no other session can access them, and non-concurrent index creation is cheaper.

IF NOT EXISTS

해당 이름과 같은 인덱스가 이미 있어도 오류로 처리하지 않고, 알림 메시지만 보여주고 마친다. 물론 같은 이름의 개체가 정확히 같은 인덱스인지는 확인하지 않는다.

INCLUDE

The optional INCLUDE clause specifies a list of columns which will be included in the index as non-key columns. A non-key column cannot be used in an index scan search qualification, and it is disregarded for purposes of any uniqueness or exclusion constraint enforced by the index. However, an index-only scan can return the contents of non-key columns without having to visit the index's table, since they are available directly from the index entry. Thus, addition of non-key columns allows index-only scans to be used for queries that otherwise could not use them. Furthermore, B-tree deduplication is never used with indexes that have a non-key column.

It's wise to be conservative about adding non-key columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches.

Columns listed in the INCLUDE clause don't need appropriate operator classes; the clause can include columns whose data types don't have operator classes defined for a given access method.

Expressions are not supported as included columns since they cannot be used in index-only scans.

Currently, the B-tree and the GiST index access methods support this feature. In B-tree and the GiST indexes, the values of columns listed in the INCLUDE clause are included in leaf tuples which correspond to heap tuples, but are not included in upper-level index entries used for tree navigation.

이름

새로 만들 인덱스 이름. 이 이름에는 스키마 이름을 지정하지 않는다. 인덱스의 소속 스키마는 항상 그 테이블의 스키마와 같기 때문이다. 이 이름을 생략하는 경우, PostgreSQL에서는 해당 테이블과 해당 칼럼 이름의 조함으로 이 인덱스 이름을 자동으로 만들어 사용한다.

ONLY

Indicates not to recurse creating indexes on partitions, if the table is partitioned. The default is to recurse.

테이블이름

대상 테이블 이름. (스키마 이름 포함)

색인방법

해당 인덱스의 색인 방법을 지정한다. 사용할 수 있는 값: btree, hash, gist, spgist, gin, brin. btree가 기본값이다.

칼럼이름

해당 테이블의 칼럼 이름.

표현식

하나의 표현식은 해당 테이블의 하나 또는 그 이상의 칼럼을 기반으로 한다. 표현식은 일반적으로 괄호로 둘러싼다. 한 함수만 호출하는 식이라면, 그럴 필요는 없다.

문자정렬규칙

해당 인덱스가 문자열 자료를 대상으로 한다면, 그 자료의 정렬 규칙을 지정한다. 기본적으로 그 해당 자료의 자료형 기본 문자 정렬 규칙을 사용한다. 이 옵션을 사용해서 특정 정렬 규칙을 사용했다면, 조회 쿼리에서도 이 정렬 규칙을 지정해야, 만든 인덱스를 사용해서 조회 작업을 진행한다.

연산자클래스

연산자 클래스 이름. 아래에서 설명함.

연산자클래스_매개변수

The name of an operator class parameter. See below for details.

ASC

오름 차순 정렬 (기본값).

DESC

내림 차순 정렬.

NULLS FIRST

null 값 먼저 정렬. DESC 인덱스인 경우는 기본값.

NULLS LAST

null 값 나중에 정렬. DESC 옵션을 지정하지 않은 인덱스인 경우는 기본값.

저장_매개변수

색인 방법에서 사용하는 저장 매개 변수 이름. 인덱스 저장 매개 변수 참조.

테이블스페이스이름

이 인덱스가 저장될 테이블스페이스 이름. 지정하지 않으면, default_tablespace 설정값의 테이블스페이스가 사용되며, 임시 테이블의 인덱스라면, temp_tablespaces 설정값의 테이블스페이스가 사용된다.

조건절

부분 인덱스를 만들 조건절 표현식.

인덱스 저장 매개 변수

WITH 옵션을 이용해서, 인덱스용 저장 매개 변수 값을 지정할 수 있다. 각 색인 방법은 그들만의 고유 매개 변수도 있다. 다음은 B-tree, hash, GiST, SP-GiST 인덱스 방법에서 사용할 수 있는 매개 변수들이다:

fillfactor (integer)

fillfactor 변수는 인덱스 페이지에 얼마만큼의 자료를 보관할 것인지를 퍼센트 값으로 지정한다. B-tree 인덱스인 경우 초기 인덱스를 만들 때, 리프 페이지는 이 값 만큼만 자료를 채운다. 또한 인덱스 오른쪽(가장 큰 값을 처리하는 경우)에 자료를 채울 때도 이 값 기준으로 처리된다. 인덱스 페이지가 꽉 차면, 그 페이지는 나뉜다. 이런 작업으로 인덱스 효율성은 점점 떨어지게 된다. B-tree 인덱스의 fillfactor 기본값은 90이며, 10부터 100까지 지정할 수 있다. 테이블의 자료가 더 이상 바뀌지 않는다면, 100으로 설정해서 물리적인 인덱스 크기를 최소화 할 수 있지만, 잦은 자료 변경이 있다면, 이 값을 줄여 인덱스 페이지 분기 작업을 최소화 하는 것이 더 효율적인다. 다른 색인 방법에서는 fillfactor 기본값이 각각 다르지만, 대략 비슷한 방법으로 이 값을 사용한다.

B-tree indexes additionally accept this parameter:

deduplicate_items (boolean)

Controls usage of the B-tree deduplication technique described in 63.4.2절. Set to ON or OFF to enable or disable the optimization. (Alternative spellings of ON and OFF are allowed as described in 19.1절.) The default is ON.

참고

Turning deduplicate_items off via ALTER INDEX prevents future insertions from triggering deduplication, but does not in itself make existing posting list tuples use the standard tuple representation.

GiST 인덱스에서는 추가적으로 다음 매개 변수를 사용할 수 있다:

buffering (enum)

인덱스를 만들 때, 64.4.1절에서 설명하고 있는 버퍼링 빌드 방법을 사용할지를 지정한다. OFF면 사용 하지 않고, ON이면 사용한다, AUTO면 사용하고 있지 않다가, 인덱스 크기가 effective_cache_size 값보다 커지만 활성화 된다. 기본값은 AUTO.

GIN 인덱스에서는 다음 매개 변수들을 사용할 수 있다:

fastupdate (boolean)

66.4.1절에서 설명하고 있는 자동 업데이트 기능을 사용할 것인지를 지정한다. 값은 불리언형으로 ON이면 활성화, OFF면 비활성화한다. 기본값은 ON.

참고

ALTER INDEX 명령으로 fastupdate 설정값을 off로 하면 그 작업 뒤 발생하는 인덱스 작업은 적용되지만, 기존 이미 인덱스 작업된 자료에 대해서는 반영하지 않는다. 해당 인덱스 전체를 대상으로 재반영하려면, VACUUM 명령이나, gin_clean_pending_list 함수를 호출 해서 반영해야 한다.

gin_pending_list_limit (integer)

해당 인덱스 개별 gin_pending_list_limit 매개 변수값 지정. 단위는 킬로바이트다.

BRIN 인덱스에서 다음 매개 변수들을 사용할 수 있다:

pages_per_range (integer)

하나의 BRIN 인덱스 페이지에서 다루는 테이블 블럭 수(67.1절 참조). 기본값은 128.

autosummarize (boolean)

다음 페이지 대상으로 insert 작업이 있으면, 이전 페이지 범위에 대한 자동 요약 작업을 할지 지정 한다.

테이블을 잠그지 않는 인덱스 만들기

인덱스를 만드는 작업은 데이터베이스 일반적인 작업을 방해할 수 있다. PostgreSQL에서는 인덱스를 만드는 중에는 그 테이블에 대해서 쓰기가 금지 된다. 이것이 기본 특성이다. 물론 읽기 작업은 가능하지만, insert, update, delete 작업은 그 인덱스가 다 만들어지기 전까지 대기한다. 운영 환경이라면, 이 특성은 여러 문제점을 일으킬 수 있다. 해당 테이블이 아주 큰 테이블이라면, 인덱스 만드는 작업은 몇 시간이 걸릴 수도 있다. 이런 경우, 그 시간 동안 서비스를 정상적으로 제공한다는 것은 무리다.

이런 문제를 해결하기 위해, PostgreSQL에서는 테이블을 잠그지 않고, 인덱스를 만들 수 있는 방법을 제공한다. CREATE INDEX 명령에서 CONCURRENTLY 옵션을 사용하는 것이다. 이 옵션을 사용하면, PostgreSQL 서버는 해당 테이블에 대해서 두 번 탐색한다. 또한, 이 인덱스 작업을 시작하기 전 수행된 자료 조작 트랜잭션들이 모두 종료 될 때까지 기다렸다가 작업을 진행한다. (명령을 실행하고, 다른 세션에서 이 명령으로 인덱스 만들기 작업을 시작했는지, 다른 트랜잭션들 때문에 이 명령이 대기 중인지 확인할 필요가 있다. - 옮긴이) 이런 이유로, (테이블을 잠그는) 일반적인 인덱스 만들기보다 시간이 더 많이 걸린다. 하지만, 이렇게 인덱스를 만들면, 만드는 동안에도 일반적인 작업들을 할 수 있어, 운영 환경에서 새 인덱스를 만들 때 유용하게 쓰인다. 물론 인덱스 만드는 작업에 따른 CPU, I/O 추가 비용은 발생하고, 이 때문에, 다른 작업들이 느려질 수 있다.

이 테이블을 잠그지 않는 인덱스 만들기는 하나의 트랜잭션으로 해당 인덱스가 시스템 카탈로그에 추가되고, 다음, 두번의 테이블 탐색 작업은 두 개 이상의 트랜잭션으로 처리된다. 첫번째 테이블 탐색 전에, 인덱스 만들기 작업은 이미 있는, 해당 테이블 대상 변경 작업용 모든 트랜잭션이 종료되기를 기다린다. 두번째 테이블 탐색 후에, 인덱스 만들기 작업은, 이 테이블 탐색이 끝나기 전에 만들어진 모든 스냅샷 트랜잭션 (13장 참고)이 종료 되기를 기다린다. including transactions used by any phase of concurrent index builds on other tables. 모든 작업이 완료되면, 해당 인덱스를 사용 가능한 인덱스로 표시하고, CREATE INDEX 명령을 종료한다. 하지만, 그 인덱스가 즉시 쿼리에서 사용되지 않을 수도 있다: 최악의 경우는 해당 테이블을 사용하는 트랜잭션이 제때에 종료되지 않아, 아에 해당 인덱스를 사용하지 못하는 상황이 발생할 수도 있다.

인덱스 만들기 작업 중 교착상태 deadlock나 유니크 제약 조건 위반 같은 문제가 발생하면, 그 작업은 중지된다. 하지만, 이 때 해당 인덱스를 없애는 것이 아니라, INVALID 인덱스로 남겨둔다. 이 인덱스는 불완적한 것이기 때문에 쿼리에서 사용되지는 않지만, 인덱스 변경 작업은 계속 되어 불필요한 비용이 발생한다. psql에서 이런 잘못된 인덱스를 확인하는 방법은 \d 명령 결과에서 인덱스 설명에 INVALID가 있는지 확인하는 것이다.

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

이런 경우 복구 방법은 해당 인덱스를 지우고, 다시 CREATE INDEX CONCURRENTLY 명령을 실행하는 것이다. (Another possibility is to rebuild the index with REINDEX INDEX CONCURRENTLY)

테이블을 잠그지 않고, 유니크 인덱스를 만들 때 주의해야할 또 다른 사항은, 두번째 테이블 탐색 전에 이미 다른 트랜잭션에서 유니크 제약 조건을 위반하는 경우가 발생할 수 있다는 것이다. 인덱스를 만들고 유효한 인덱스로 표시하기 전이나, 아니면, 아에 인덱스를 만들기 작업 실패로 처리하기 전에, 다른 쿼리에서 유니크 제약 조건 위반 오류가 발생할 수 있다. 또한, 이 실패가 두번째 탐색 작업 중에 발생하면, 이 잘못된 인덱스 때문에, 향후 계속 해서 유니크 제약 조건 위반 문제가 발생할 수도 있다.

표현식 기반 인덱스나 부분 인덱스도 해당 테이블을 잠그지 않고 만들수 있다. 이 때도 유니크 제약 조건 문제처럼 이 비슷한 문제가 생길 수 있기 때문에 주의해야 한다.

테이블을 잠그는 일반 인덱스 만들기는 동일 테이블에 대해서 여러 인덱스를 동시에 만들 수 있지만, 테이블을 잠그지 않는 인덱스 만들기는 한 테이블에 대해서 하나의 인덱스만 만들 수 있다. 모든 인덱스 만들기 작업 중에는 테이블 정의 변경 작업은 불가능하다. 테이블을 잠그는 일반 인덱스 만들기는 트랜잭션 내에서 사용할 수 있지만, 테이블을 잠그지 않는 인덱스 만들기는 트랜잭션 내에서 사용할 수 없다.

Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a metadata only operation.

참고

어떤 경우는 인덱스를 사용하고, 어떤 경우는 인덱스를 사용하지 않는지, 이런 개별 상황에 대한 자세한 설명은 11장에서 자세히 다룬다.

현재, B-tree, GiST, GIN, BRIN 인덱스만 다중 칼럼 인덱스를 만들 수 있다. 최대 32 개까지만 허용한다. (이 값은 PostgreSQL 빌드 때 바꿀 수 있다.) 현재, B-tree 인덱스만 유니크 인덱스를 만들 수 있다.

인덱스를 만들 때, 연산자 클래스를 지정할 수 있다. 이 연산자 클래스의 매개 변수도 추가할 수 있다. 연산자 클래스란 해당 칼럼용 인덱스에서 사용되는 연산자 식별자다. 예를 들어, 4 바이트 정수 칼럼이라면, int4_ops 연산자 클래스가 사용된다. 이 연산자 클래스에 4 바이트 정수 비교 함수가 포함되어있다. 일반적으로 해당 자료형에 대한 기본 연산자 클래스를 사용한다. 이 연산자 클래스를 지정하는 경우는 특정 자료형에 대해서 독특한 연산으로 검색하고자 할 때 사용한다. 예를 들어, 복소수 자료인 경우, 그 절대값이나, 실수부만 대상으로 자료를 정렬하려고 할 때 그에 맞는 연산자를 클래스를 지정해서 인덱스를 만든다. 또한 하나의 인덱스 대상에 대해서 각각 다른 연산자 클래스를 지정해서 여러 개의 인덱스를 만들 수 있으며, 이 경우 실행 계획기에는 상황에 맞게 해당 인덱스를 선택해서 작업한다. 연산자 클래스에 대한 자세한 정보는 11.10절37.16절에서 다룬다.

When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

현재 B-tree 색인 방법을 사용하는 경우에만 ASC, DESC, NULLS FIRST, NULLS LAST 옵션을 사용할 수 있다. 실행 계획에 따라 인덱스 순방향, 역방향 검색이 모두 가능하기 때문에, 단일 칼럼 인덱스에서는 DESC 옵션을 사용할 필요는 없다 — 이 경우는 이미 그 옵션이 없어도 잘 사용할 수 있다. 이 옵션은 SELECT ... ORDER BY x ASC, y DESC 형태의 쿼리와 같이 다중 칼럼을 사용하고, 그 정렬 방법이 다른 경우에 쓰인다. NULLS 옵션은 null 값 자료 출력 위치를 변경 해야 하는 경우에 사용된다.

The system regularly collects statistics on all of a table's columns. Newly-created non-expression indexes can immediately use these statistics to determine an index's usefulness. For new expression indexes, it is necessary to run ANALYZE or wait for the autovacuum daemon to analyze the table to generate statistics for these indexes.

대부분의 색인 방법은 maintenance_work_mem 설정값에 따라 인덱스 만드는 속도가 달라진다. 이 설정값이 크면 그 만큼 인덱스 만드는 속도가 빨라 진다. 물론 너무 크게 지정해서 시스템 스왑 메모리를 사용한다면, 속도를 보장하지는 못한다.

PostgreSQL can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as parallel index build. For index methods that support building indexes in parallel (currently, only B-tree), maintenance_work_mem specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any.

Parallel index builds may benefit from increasing maintenance_work_mem where an equivalent serial index build will see little or no benefit. Note that maintenance_work_mem may influence the number of worker processes requested, since parallel workers must have at least a 32MB share of the total maintenance_work_mem budget. There must also be a remaining 32MB share for the leader process. Increasing max_parallel_maintenance_workers may allow more workers to be used, which will reduce the time needed for index creation, so long as the index build is not already I/O bound. Of course, there should also be sufficient CPU capacity that would otherwise lie idle.

Setting a value for parallel_workers via ALTER TABLE directly controls how many parallel worker processes will be requested by a CREATE INDEX against the table. This bypasses the cost model completely, and prevents maintenance_work_mem from affecting how many parallel workers are requested. Setting parallel_workers to 0 via ALTER TABLE will disable parallel index builds on the table in all cases.

작은 정보

You might want to reset parallel_workers after setting it as part of tuning an index build. This avoids inadvertent changes to query plans, since parallel_workers affects all parallel table scans.

While CREATE INDEX with the CONCURRENTLY option supports parallel builds without special restrictions, only the first table scan is actually performed in parallel.

인덱스를 지울 때는 DROP INDEX 명령을 이용한다.

Like any long-running transaction, CREATE INDEX on a table can affect which tuples can be removed by concurrent VACUUM on any other table.

이전 PostgreSQL에서는 R-tree 인덱스도 있었으나, GiST 인덱스가 더 좋아 이제는 사용하지 않는다. USING rtree 옵션을 사용하면, CREATE INDEX 명령은 자동으로 USING gist 바꿔 명령이 실행된다.

예제

films 테이블 title 칼럼 대상 유니크 B-tree 인덱스를 만드는 경우:

CREATE UNIQUE INDEX title_idx ON films (title);

To create a unique B-tree index on the column title with included columns director and rating in the table films:

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

To create a B-Tree index with deduplication disabled:

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);

fastupdate 기능을 끄고, GIN 색인 방법으로 인덱스를 만드는 경우:

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

테이블스페이스를 지정한 경우:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

지점 정보를 박스 공간으로 바꿔 그것을 GiST 인덱스로 만들고, 그 인덱스를 이용해서 자료를 찾는 경우:

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

해당 테이블 대상 DML 쿼리를 허용하면서 인덱스를 만드는 경우:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

호환성

CREATE INDEX 구문은 PostgreSQL 확장 구문이다. 표준 SQL에는 인덱스 관련 구문이 없다.

관련 항목

ALTER INDEX, DROP INDEX, REINDEX