| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ]
name
ON
table
[ USING
method
]
( {
column
| (
expression
) } [
opclass
] [, ...] )
[ WITH (
storage_parameter
=
value
[, ... ] ) ]
[ TABLESPACE
tablespace
]
[ WHERE
predicate
]
CREATE INDEX는, 지정한 테이블상에 name 인덱스를 만듭니다. 인덱스는 주로 데이타베이스의 성능을 향상하기 위해서 사용됩니다. (그러나, 인덱스의 부적절한 사용은 성능의 저하로 연결될 가능성이 있습니다).
인덱스의 키필드는, 열명, 또는 괄호에 둘러싸인 식으로서 지정됩니다. 인덱스 메소드가 복수열에 대한 인덱스를 지원하는 경우는, 복수의 필드를 지정할 수 있습니다.
인덱스의 필드로서 테이블행의 한 개 또는 한 개 이상의 열의 값을 계산하는 식을 지정할 수 있습니다. 이 기능은, 기본 데이터에 약간의 변환을 더한 값을 기초로 하는 데이터에 빠른 액세스를 실행하는 수단으로서 사용할 수 있습니다. 예를 들면,upper(col)라고 하는 계산에 근거하는 인덱스가 있으면,WHERE upper(col) = 'JIM'구에서는 인덱스를 사용할 수가 있습니다.
PostgreSQL는 B-tree, hash, GiST, GIN의 인덱스 메소드를 제공 합니다. 사용자가 독자적으로 인덱스 메소드를 정의할 수도 있습니다만, 이것은 상당히 복잡합니다.
WHERE구가 존재하는 경우,부분 인덱스가 작성됩니다. 부분 인덱스는, 테이블의 일부, 통상은, 테이블 중 에서 보다 인덱스가 유용한 부분만의 엔트리를 가지는 인덱스입니다. 예를 들면, 지불 끝난 주문과 미지불의 주문을 정보로 가지는 테이블이 있어, 테이블 전체에 있어서의 미지불의 주문의 비율이 작고, 한편, 빈번하게 사용되는 경우, 미지불의 주문에만 인덱스를 작성하는 것으로 성능을 향상할 수 있습니다. 부분 인덱스의 그 외의 이용 방법으로서UNIQUE첨부의WHERE를 사용해, 테이블의 일부에 일의성을 강제하는 것이 있습니다. 자세한 것은Section 11.7을 참조해 주세요.
WHERE구 내에서 사용된 식은, 원래의 테이블의 열만을 참조할 수 있습니다. 그러나, 인덱스를 부가하는 열 만이 아니고, 모든 열을 사용할 수가 있습니다. 또, 현재, 서브쿼리와 집계식에 대해서는,WHERE로 사용할 수가 없습니다. 동일한 제한은, 식에 있는 인덱스의 필드에도 적용됩니다.
인덱스의 정의로 사용되는 모든 함수와 연산자는,"불변"(immutable)이 아니면 안됩니다. 즉, 결과는 입력 인수에게만에 의존해야 하고,외부로부터의 영향(다른 테이블의 내용이나 지금 시각과 같은) 을 받아서는 안됩니다. 이 제한에 의해, 인덱스의 동작이 충분히 정의되고 있는 것이 보증됩니다. 인덱스식이나WHERE구에 사용자 정의의 함수를 사용하는 경우, 함수를 작성할 때, IMMUTABLE(불변) 옵션을 붙이는 것을 기억해 두세요.
인덱스를(이미 데이터가 있는 상태로) 작성할 때, 테이블에 시간 데이터를 추가할 때에, 테이블 내의 값이 중복 하고 있지 않는가를 검사합니다. 중복 엔트리를 생성하는 데이터의 삽입 또는 갱신은 에러를 발생 시킵니다.
이 옵션을 사용하면,PostgreSQL는, 대상 테이블에 대한 동시 삽입, 갱신, 삭제를 방지하는 것 같은 락을 획득하지 않고 인덱스를 작성합니다. 표준 인덱스 작성 처리에서는, 완료할 때까지 대상 테이블에의 기입에 락이 걸립니다(읽기에는 락이 걸리지 않습니다). 이 옵션을 사용할 때에 주의해야 할 점이 다수 있습니다. Building Indexes Concurrently (을)를 참조해 주세요.
작성하는 인덱스의 이름입니다. 이 이름에는, 스키마명을 포함할 수 없습니다. 인덱스는, 항상 그 부모 테이블과 같은 스키마에서 작성됩니다.
인덱스를 작성하는 테이블의 이름(검증을 거친 스키마)입니다.
사용하는 인덱스 메소드의 이름입니다. btree,hash,gist,gin로부터 선택합니다. 디폴트 메소드는btree입니다.
테이블의 열의 이름입니다.
테이블 상의 하나 또는 하나 이상의 열을 사용한 식입니다. 통상 이 식은, 구문에서 보여진 대로 괄호로 둘러쌀 필요가 있습니다. 그러나, 식이 함수 호출 형식이 되어 있는 경우는 괄호를 생략 할 수가 있습니다.
연산자 클래스의 이름입니다. 자세한 것은 아래를 참조해 주세요.
인덱스 고유의 격납 파라미터의 이름입니다. 자세한 것은 아래를 참조해 주세요.
인덱스를 생성하는 테이블스페이스입니다. 지정되지 않은 경우, default_tablespace를 사용하거나 또는, default_tablespace 에 문자열이 없는 경우에 데이터베이스의 디폴트 테이블스페이스를 사용하세요.
부분 인덱스용의 제약식입니다.
WITH구에서는, 인덱스에 대해격납 파라미터를 지정할 수 있습니다. 인덱스 메소드는 각각 고유의 설정 가능한 격납 파라미터를 가질 수가 있습니다. 내장된 인덱스는 모두 하나의 매개변수를 취합니다.
인덱스용의 fillfactor는 비율(퍼센트)로, 인덱스 메소드가 인덱스 페이지를 정리할 때 에 어느 정도 페이지를 사용할까를 결정하는 것입니다. B-tree에서는, 리프 페이지는 초기 인덱스 구축시와 우측(키값이 큰 방향)으로 인덱스를 확장할 때에 이 비율분 페이지를 사용합니다. 그 후 페이지 모든 것이 완전하게 사용되면, 인덱스의 효과가 점차 저하되도록 분할됩니다. B-tree의 디폴트의 fillfactor는 90입니다만, 10에서 100까지의 임의의 값을 설정할 수가 있습니다. 테이블의 변화가 거의 없는 경우, 100이 최선으로 인덱스의 물리 사이즈를 최소화할 수 있습니다. 갱신이 매우 많은 경우는, 보다 작은 fillfactor를 설정하는 것으로, 페이지 분할의 빈도를 줄일 수가 있습니다. 이 다른 인덱스 메소드에서는, fillfactor가 대체로 같습니다만, 다른 의미로 사용합니다. 메소드에 의해 fillfactor의 디폴트 값은 다릅니다.
인덱스의 작성이, 통상의 데이터베이스 조작에 영향을 주는 일이 있습니다. 통상 PostgreSQL는, 대상 테이블에 대한 기입에 락을 걸고 난 뒤, 대상 테이블을 한 번 스캔한 것으로 전체 인덱스의 구축을 실행합니다. 다른 트랜잭션은 테이블을 읽어낼 수 있습니다만, 대상 테이블내의 행을 삽입, 갱신, 삭제하려고 하면, 인덱스 작성이 완료할 때까지 블록 됩니다. 활발한 운용 상태의 데이터베이스 시스템의 경우, 이것은 중대한 영향을 줍니다. 매우 대규모 테이블에 대한 인덱스 작성은 몇 시간이나 걸리는 것이 있을 수 있습니다. 또 소규모의 테이블이어도, 인덱스 작성에 의해, 운용 상태의 시스템에서는 받아들여지지 않는 기간 동안, 기입 락이 걸릴 가능성이 있습니다.
PostgreSQL는 기입에 락을 걸지 않고, 인덱스 작성을 지원하고 있습니다. CREATE INDEX의CONCURRENTLY옵션을 지정하여, 이 방식이 거행됩니다. 이 옵션을 사용하면,PostgreSQL는 테이블을 2회 스캔 하지 않으면 안됩니다. 게다가 기존의 실행중인 모든 트랜잭션이 끝날 때까지 대기하지 않으면 안됩니다. 따라서, 이 방식은 표준 인덱스 작성의 방식보다 총 작업시간이 더 걸려, 또, 완료할 때까지의 시간이 매우 길어집니다. 그러나, 인덱스 작성중에 통상의 조작을 계속 실시할 수 있기 때문에, 이 방식은 운용 환경에서의 신규 인덱스 작성에 유용합니다. 물론, 인덱스 작성에 의해 CPU나 입출력에 부하가 걸리기 때문에, 다른 조작이 저속이 될 가능성이 있습니다.
일의성 인덱스에 있어서의 일의성 위반 등, 테이블의 2번째 스캔중에 문제가 발생하면,CREATE INDEX는 실패합니다만,"무효인"인덱스가 남게 됩니다. 이러한 인덱스는 완전하지 않을 수 있기 때문에, 쿼리에서는 무시됩니다. 그러나, 갱신시에 오버헤드가 걸립니다. 이러한 경우의 추천 복구 방법은, 인덱스를 삭제해, 재차CREATE INDEX CONCURRENTLY를 실행하는 것입니다. (그 밖에도REINDEX를 사용한 인덱스를 재작성 하는 방법도 있습니다. 그러나,REINDEX는 동시 작성을 지원하지 않기 때문에, 이 방법은 매력이 없습니다. )
이 그 밖에 일의성 인덱스를 동시 작성하는 경우의 주의 사항이 있습니다. 2번째의 테이블 스캔이 시작되는 시점에서, 다른 트랜잭션에 대한 일의성 제약이 이미 유효하다는 점입니다. 이것은, 인덱스를 사용할 수 있게 되기 전이나 인덱스 작성이 최종적으로 실패했다고 해도, 제약 위반이 다른 쿼리에 보고되는 것을 의미합니다. 또한, 2번째의 스캔 중에 실패한 경우도,"무효인"인덱스에 의한 일의성 제약은 계속 강요 됩니다.
식 인덱스나 부분 인덱스의 동시 작성도 지원되고 있습니다. 식의 평가중에 에러가 발생했을 경우도, 위에서 설명한 일의성 제약 위반과 같은 상황이 발생합니다.
동일 테이블에 대한 통상의 인덱스 작성 처리는 복수 병행해 실시할 수가 있습니다. 그러나, 어느 테이블에 대한 인덱스의 동시 작성은 한 번에 1개 밖에 실시할 수가 없습니다. 또, 어느 경우에서도 인덱스 작성중에 테이블 상의 스키마 변경은 할 수 없습니다. 이 그 밖에, 통상의CREATE INDEX커멘드는 트랜잭션 블록내에서 실행시킬 수가 있습니다만,CREATE INDEX CONCURRENTLY는 실행시킬 수가 없다고 하는 차이점이 있습니다.
인덱스가, 어떠한 때에 사용되어 어떠한 때에 사용되지 않는가, 또, 어떠한 경우에 유용한 것에 관한 정보에 대해서는Chapter 11을 참조해 주세요.
현재는, B-tree와 GiST 인덱스 메소드만이, 복수열에 대한 인덱스를 지원하고 있습니다. 지정할 수 있는 열은, 디폴트에서는 32개까지입니다(이 제한은PostgreSQL구축 시에 변경할 수 있습니다). 현재, B-tree만이 일의성 인덱스를 지원하고 있습니다.
연산자 클래스는, 인덱스의 각각의 열로 지정할 수가 있습니다. 연산자 클래스는, 그 열의 인덱스가 사용하는 연산자를 식별합니다. 예를 들면, 4바이트 정수에 대한 B-tree 인덱스에는,int4_ops클래스를 사용합니다. 이 연산자 클래스에는, 4바이트 정수의 비교 함수가 포함되어 있습니다. 실제의 사용에서는, 통상, 열의 데이터형의 디폴트 연산자 클래스에서 충분합니다. 연산자 클래스를 갖는 주된 이유는, 데이터형안에는 의미가 있는 순서를 2개 이상 가지는 것이 있을 수 있기 때문입니다. 예를 들면, 절대값 또는 실수부를 사용해 복소수의 데이터형을 정렬 하고 싶은 경우가 있을 수 있습니다. 이것을 실현하려면 , 데이터형으로서 2개의 연산자 클래스를 정의해, 인덱스를 만들 때에 적절한 클래스를 선택합니다. 연산자 클래스에 대한 자세한 것은,Section 11.8및Section 33.14를 참조해 주세요.
인덱스를 삭제하려면, DROP INDEX 를 사용해 주세요.
인덱스는 디폴트로 IS NULL구를 사용하지 않습니다. 이 경우, 인덱스를 사용하는 가장 좋은 방법은, IS NULL를 사용하여 부분 인덱스를 생성하는 것입니다.
이전의PostgreSQL릴리스에는 R-tree 인덱스 메소드가 있었습니다. GiST 메소드에 비해 큰 이점이 없었었기 때문에, 이 메소드는 삭제되었습니다. 오래된 데이터베이스로부터 GiST에의 변환을 간단하게 하기 위해,USING rtree가 지정되었을 경우,CREATE INDEX는USING gist라고 해석합니다.
films테이블의title열에 B-tree 인덱스를 작성합니다.
CREATE UNIQUE INDEX title_idx ON films (title);
대문자 소문자를 구별하지 않는 검색이 효율적이 되도록,lower(title)식에 대해서 인덱스를 작성합니다.
CREATE INDEX lower_title_idx ON films ((lower(title)));
디폴트 이외의 fill 팩터를 가지는 인덱스를 작성합니다.
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
films테이블상의code열에 대한 인덱스를 작성하고, 또, 이 인덱스를indexspace테이블스페이스 내에 생성하려면 다음과 같이 하세요.
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
대상 테이블에의 기입에 락을 걸지 않고, 인덱스를 작성합니다.
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);