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에는 인덱스 관련 구문이 없다.