문서링크 : Postgres 공식문서 - Index
11.1 소개
아래와 같은 테이블이 있다고 가정합니다.
CREATE TABLE test1 (
id integer,
content varchar
);그리고 어플리케이션은 다음 형식의 쿼리가 발생하는 상황이 많습니다.
SELECT content FROM test1 WHERE id = content;별다른 조치가 없었다면, 시스템은 일치하는 것을 찾기위해 test1의 모든 행들을 스캔해야 합니다.
만약 test1 테이블에 엄청나게 많은 행들이 있고 그 중 찾고 있는 행은 극히 일부(0개이거나 1개)일 경우 이는 명백히 비효율적인 방식입니다.
하지만 만약 시스템이 id 컬럼에 대해 인덱스를 유지하도록 설정되어있다면, 더 효율적인 방식으로 일치하는 행들을 찾을 수 있습니다.
예를들어, 탐색 트리를 사용하면 몇 단계만 걸치면 찾을 수 있습니다.
대부분의 책에서도 이와 비슷한 방식을 사용합니다.
독자들이 자주 찾는 용어와 개념들을 책 끝 부분에 있는 알파벳 인덱스에 수집합니다.
이를 통해 독자는 인덱스를 비교적 빠르게 스캔해 관심있는 부분의 페이지를 찾을 수 있습니다.
책 전체를 모두 읽어보는 방식대신 말이죠.
독자들이 관심있어할 만한 것들을 예상하는 것이 작가의 일인 것처럼, 데이터베이스 프로그래머 또한 어떤 인덱스가 더 유용할 지 예측하는 일을 해야합니다.
아래의 명령어는 id 컬럼에 인덱스를 적용하는 문법입니다.
CREATE INDEX test1_id_index ON test1 (id);인덱스 이름인 test1_id_index는 예시이며 마음대로 지을 수 있습니다.
하지만 나중에 이 인덱스가 어떤 역할을 하는지 기억할 수 있는 이름을 선택하는 것이 좋습니다.
인덱스를 삭제하려면 DROP INDEX 명령어를 사용할 수 있습니다.
인덱스들은 언제나 테이블에 생성되고 삭제될 수 있습니다.
인덱스는 한 번 생성되면 추가 작업이 필요하지 않습니다.
시스템은 테이블이 수정될 때 인덱스를 업데이트하고, 순차 테이블 스캔보다 더 효율적이라고 판단하면 쿼리에서 인덱스를 사용합니다.
하지만 쿼리 플래너가 현명한 판단을 하고 있는지 ANALYZE 명령어를 통해 주기적으로 확인하는 것이 좋습니다. Chapter 14에서 인덱스 사용 여부를 확인하는 방법과 플래너가 인덱스를 사용하지 않기로 선택한 시기와 이유에 대해 더 자세히 알아볼 수 있습니다.
인덱스는 UPDATE나 DELETE 명령어를 수행할 대상을 찾는 것에도 적용되어 효율적으로 동작합니다.
게다가 조인이 들어가는 상황에서도 인덱스가 사용됩니다. 따라서 인덱스는 조인 조건이 걸려있는 컬럼에 정의하면 조인이 필요한 상황에서 속도가 상당히 향상되는 것을 볼 수 있습니다.
큰 테이블에 인덱스를 적용하는 것은 다소 오래 걸릴 수 있습니다.
기본적으로 PostgreSQL은 인덱스가 생성되는 동안에도 읽기 작업(Read)을 허용하지만 쓰기 작업(Insert, Update, Delete)은 인덱스 생성이 완료될 때까지 블록됩니다.
운영 환경에서 이는 치명적일 수 있는데 설정을 통해 인덱스 생성 중에도 쓰기 작업이 허용되도록 할 수 있지만 몇 가지 주의 사항을 고려해야 합니다.
인덱스가 생성된 후 시스템은 인덱스를 테이블과 동기화된 상태로 유지해야 합니다.
이로 인해 데이터 조작 작업에 오버 헤드가 추가됩니다.
또한 인덱스는 힙 전용 튜플 생성을 방지할 수도 있습니다.
그러므로 쿼리에서 드물게 혹은 아예 사용되지 않는 인덱스는 삭제해야 합니다.
11.2 인덱스 유형들
PostgreSQL은 B-tree, Hash, GIST, SP-GiST, GIN, BRIN 그리고 bloom 확장자 등 여러 개의 인덱스 유형을 제공합니다.
각 인덱스 유형들은 다양한 유형의 쿼리에 가장 적합한 알고리즘을 사용합니다.
기본적으로 CREATE INDEX 명령어를 사용하면 B-tree 유형으로 생성되는데 그 이유는 일반적으로 가장 많은 상황에서 적합한 알고리즘이기 때문입니다.
다른 인덱스 유형을 선택하려면 USING 키워드를 사용할 수 있습니다.
아래는 Hash 인덱스를 생성하는 예시입니다.
CREATE INDEX name ON table USING HASH (column);11.2.1 B-Tree
B-tree는 정렬된 데이터에서 동등 연산이나 범위 연산을 수행할 때 적합합니다.
특히 PostgreSQL의 쿼리 플래너는 아래 연산자 중 하나를 사용하는 비교에 인덱스된 열이 포함될때마다 B-tree 인덱스 사용을 고려합니다.
<
<=
=
=
>
BETWEEN과 IN 같은 연산자의 조합에 해당하는 구문은 B-tree 인덱스 검색을 통해 구현할 수 있습니다. 또한 IS NULL이나 IS NOT NULL 조건이 인덱스 컬럼에 걸려있는 상황에서도 B-tree 인덱스를 사용할 수 있습니다.
패턴이 상수이고 문자열의 시작 부분에 고정되어 있는 경우 최적화 프로그램은 패턴 일치 연산자인 LIKE 및 ~와 관련된 쿼리에 대해 B-tree 인덱스를 사용할 수도 있습니다. 예를 들어, col LIKE 'fo%' 나 col ~ '^foo'는 가능하지만 col LIKE '%bar'는 불가능합니다.
하지만 데이터베이스가 C locale을 사용하지 않는 경우 패턴 일치 쿼리의 인덱싱을 지원하기 위해 특수 연산자 클래스를 사용하여 인덱스를 생성해야 합니다. ( 자세한 내용은 Section 11.10 참고)
또한 B-tree 인덱스는 ILIKE나 ~* 연산자에도 사용할 수 있습니다만 패턴이 알파벳이 아닌 문자로 시작하는 경우에만 가능합니다. 즉, 대문자/소문자 변환의 영향을 받지 않는 문자여야 합니다.
B-tree 인덱스를 사용하여 정렬된 순서로 데이터를 탐색할 수도 있습니다.
이는 항상 단순 스캔이나 정렬보다 호율적이고 빠른 것은 아니지만 유용한 상황이 자주 있습니다.
11.2.2 Hash
Hash 인덱스는 인덱스된 열의 값에서 파생된 32 비트 해시 코드를 저장합니다.
따라서 단순한 동등 비교 연산만 처리할 수 있습니다. 쿼리 플래너는 인덱싱된 열이 동등 연산자를 사용한 비교에 포함될 때마다 해시 인덱스 사용을 고려합니다.
11.2.3 GiST
GiST 인덱스는 단일 종류의 인덱스가 아닙니다.
하지만 오히려 다양한 인덱싱 전략을 구현할 수 있는 인프라입니다.
따라서 GiST 인덱스를 사용할 수 있는 특정 연산자는 인덱스 전략에 따라 다릅니다.
예를 들어, PostgreSQL의 표준 배포판에는 여러 2차원 기하학적 데이터 유형에 대한 GiST 연산자 클래스가 포함되어 있으며 이러한 연산자를 사용하여 인덱스 쿼리를 지원합니다.
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
표준 배포판에 포함된 GiST 연산자 클래스는 표 68.1에 문서화되어 있습니다.
다른 많은 GiST 연산자 클래스들은 contrib 컬렌션이나 별도의 프로젝트로 사용할 수 있습니다.
또한 GiST 인덱스는 아래과 같은 최근접 이웃 탐색을 최적화할 수도 있습니다.
SELECT * FROM places ORDER BY location <-> point '(101, 456)' LIMIT 10;위 쿼리는 주어진 목표 지점에 가장 가까운 10개의 장소를 찾는 것입니다. 이를 수행하는 기능은 사용되는 특정 연산자 클래스에 따라 달라집니다. 이러한 방식으로 사용할 수 있는 연산자는 "순서 연산자" 열에 나열되어 있습니다.
11.2.4 SP-GiST
SP-GiST 인덱스는 GiST 인덱스와 마찬가지로 다양한 종류의 검색을 지원하는 인프라를 제공합니다. SP-GiST는 쿼드 트리, k-d 트리, 기수 트리와 같은 다양한 비균형 디스크 기반 데이터 구조를 구현할 수 있습니다. 예를 들어, PostgreSQL의 표준 배포판에는 2차원 포인트에 대한 SP-GiST 연산자 클래스가 포함되어 있으며, 이렇나 연산자를 사용하여 인덱싱된 쿼리를 지원합니다.
<<
>>
~=
<@
<<
||
표준 배포에 포함된 SP-GiST 연산자 클래스는 표 69.1에 설명되어 있습니다. 자세한 내용은 69장을 찹조하십시오.
GiST와 마찬가지로 SP-GiST는 가장 가까운 이웃 탐색을 지원합니다. 거리 순서를 지원하는 SP-GiST 연산자 클래스의 경우, 해당 연산자는 표 69.1의 순서 지정 연산자 열에 나열되어 있습니다.
11.2.5 GIN
GIN 인덱스는 배열과 같이 여러 구성 요소 값을 포함하는 데이터 값에 적합한 "역인덱스"입니다.
역인덱스에는 각 구성 요소 값에 대한 별도의 항목이 포함되어 있으며 특정 구성 요소 값이 있는지 테스트하는 쿼리를 효율적으로 처리할 수 있습니다.
GiST나 SP-GiST와 마찬가지로 GIN은 다양한 사용자 정의 인덱싱 전략을 지원할 수 있으며 GIN 인덱스를 사용할 수 있는 특정 연산자는 인덱싱 전략에 따라 달라질 수 있습니다. 예를 들어 PostgreSQL의 표준 배포에는 아래와 같은 연산자를 사용하여 인덱스 쿼리를 지원하는 배열용 GIN 연산자 클래스가 포함되어 있습니다.
<@
@>
=
&&
표준 배포판에 포함된 GIN 연산자 클래스는 표 70.1에 문서화되어 있습니다. 다른 많은 GIN 연산자 클래스는 contrib 컬렉션이나 별도의 프로젝트로 사용할 수 있습니다. 자세한 내용은 70장을 참조하세요.
11.2.6 BRIN
BRIN 인덱스는 테이블의 연속된 물리적 블록 범위에 저장된 값에 대한 요약을 저장합니다. 따라서 테이블 행의 물리적 순서와 값이 잘 연관되어 있는 열에 가장 효과적입니다.
GiST, SP-GiST, GIN과 마찬가지로 BRIN은 다양한 인덱싱 전략을 지원할 수 있으며 BRIN 인덱스를 사용할 수 있는 특정 연산자는 인덱싱 전략에 따라 달라집니다.
선형 정렬 순서를 갖는 데이터 유형의 경우, 인덱싱된 데이터는 각 블록 번위의 열에 있는 값의 최소값과 최대값에 해당합니다.
BRIN은 아래와 같은 연산자를 사용하여 인덱싱된 쿼리를 지원합니다.
<
<=
=
>=
>
표준 배포에 포함된 BRIN 연산자 클래스는 표 71.1에 설명되어 있습니다. 자세한 내용은 71장을 참조하십시오.
11.3 다중 열 인덱스
인덱스는 하나 또는 그 이상의 컬럼에 정의할 수 있습니다.
예를 들어 아래와 같은 테이블이 있다고 가정합니다.
CREATE TABLE test2 (
major int,
minor int,
name varchar
);_텍스트_그리고 아래와 같은 쿼리가 자주 발생한다고 가정합니다.
SELECT name FROM test2 WHERE major = constant AND minor = constant;이 경우 major와 minor 열에 인덱스를 함께 정의하는 것이 적절합니다
CREATE INDEX test2_mm_idx ON test2 (major, minor);현재로서는 B-tree, GiST, GIN, BRIN 유형만이 다중 열 인덱스를 지원합니다.
여러 개의 키 열을 가질 수 있는지 여부는 인덱스에 INCLUDE 열을 추가할 수 있는지 여부와 무관합니다.
인덱스는 INCLUDE 열을 포함하여 최대 32개의 열이 포함될 수 있습니다.
다중 열 B-tree 인덱스는 인덱스 열의 모든 하위 집합을 포함하는 쿼리 조건에 사용할 수 있지만, 인덱스는 선행 열에 제약 조건이 있을 때 가장 효율적입니다.
정확한 규칙은 선행 열에 대한 같음 제약 조건과 같음 제약 조건이 없는 첫 번째 열에 대한 부등식 제약 조건을 사용하여 스캔되는 인덱스의 일부를 제한한다는 것입니다.
이러한 오른쪽에 있는 열에 대한 제약 조건은 인덱스에서 확인되므로 테이블에 대한 적절한 방문을 절약하지만 스캔해야 하는 인덱스의 부분을 줄이지는 않습니다.
예를 들어 (a,b,c)에 대한 인덱스가 있고 a = 5이고 b >= 42이고 c < 77인 쿼리 조건이 주어진다면, 인덱스는 a = 5, b = 42인 첫 번째 항목부터 a = 5인 마지막 항목까지 스캔해야 합니다.
c >= 77인 인덱스 항목은 건너뛸 수 있지만 여전히 스캔해야 합니다.
이 인덱스는 원칙적으로 a에 대한 제약 조건이 없고 b 및/또는 c에 대한 제약 조건이 있는 쿼리에 사용할 수 있지만 전체 인덱스를 스캔해야 하므로 대부분의 경우 플래너는 인덱스를 사용하는 것보다 순차 테이블 스캔을 선호할 것입니다.
다중 열 GiST 인덱스는 인덱스 열의 모든 하위 집합을 포함하는 쿼리 조건과 함께 사용할 수 있습니다. 추가 열에 대한 조건은 인덱스가 반환하는 항목을 제한하지만, 첫 번째 열에 대한 조건은 인덱스에서 얼마나 많은 항목을 스캔해야 하는지를 결정하는 데 가장 중요한 조건입니다. 추가 열에 많은 고유 값이 있더라도 첫 번째 열에 고유 값이 몇 개만 있으면 GiST 인덱스는 상대적으로 효율적이지 않습니다.
다중 열 GIN 인덱스는 인덱스 열의 하위 집합을 포함하는 쿼리 조건과 함께 사용할 수 있습니다.
B-tree나 GiST와 달리 쿼리 조건이 어떤 인덱스 열을 사용하는지에 관계없이 인덱스 검색 효과는 동일합니다.
다중 열 BRIN 인덱스는 인덱스 열의 모든 하위 집합을 포함하는 쿼리 조건과 함께 사용할 수 있습니다. GIN과 마찬가지로 그리고 B-tree나 GiST와는 달리, 쿼리 조건이 어떤 인덱스 열을 사용하는지에 관계없이 인덱스 검색 효과는 동일합니다. 단일 테이블에 하나의 다중 컬럼 BRIN 인덱스 대신 여러 개의 BRIN 인덱스를 사용하는 유일한 이유는 다른 pages_per_range 저장소 매개변수를 사용하기 위해서입니다.
물론 각 열은 인덱스 유형에 적합한 연산자와 함께 사용해야 하며, 다른 연산자가 포함된 절은 고려되지 않습니다.
다중 컬럼 인덱스는 신중하게 사용해야 합니다. 대부분의 경우 단일 열에 대한 인덱스로 충분하며 공간과 시간을 절약할 수 있습니다. 열이 3개 이상인 인덱스는 테이블 사용이 극도로 정형화 되어 있지 않는 한 도움이 되지 않습니다.
11.4 인덱스와 ORDER BY
인덱스는 단순히 쿼리에서 반환할 행을 찾는 것 외에도 특정 정렬된 순서로 행을 전달할 수 있습니다. 이렇게 하면 별도의 정렬 단계 없이도 쿼리의 ORDER BY 사양을 준수할 수 있습니다. 현재 PostgreSQL에서 지원하는 인덱스 유형 중 B-tree만 정렬된 출력을 생성할 수 있으며, 다른 인덱스 유형은 일치하는 행을 지정되지 않고 구현에 따라 달라지는 순서로 반환합니다.
쿼리 플래너는 사양과 일치하는 사용 가능한 인덱스를 스캔하거나 테이블을 물리적 순서대로 스캔하고 명시적 정렬을 수행하여 ORDER BY 사양을 충족하는 것을 고려합니다. 테이블의 많은 부분을 스캔해야 하는 쿼리의 경우, 명시적 정렬은 순차적 엑세스 패턴을 따르기 때문에 디스크 I/O가 덜 필요하므로 인덱스를 사용하는 것보다 더 빠를 수 있습니다. 인덱스는 몇 개의 행만을 가져와야 할 때 더 유용합니다. 중요한 특수한 경우는 Limit n과 함께 ORDER BY를 사용하는 경우입니다. 명시적 정렬은 처음 n개의 행을 식별하기 위해 모든 데이터를 처리해야 하지만, ORDER BY와 일치하는 인덱스가 있으면 나머지 행을 전혀 스캔하지 않고도 처음 n개의 행을 바로 검색할 수 있습니다.
기본적으로 B-tree 인덱스는 오름차순으로 항목을 저장하며, null은 마지막에 위치합니다.
즉, x 열에 있는 인덱스를 정방향으로 스캔하면 ORDER BY x를 만족하는 출력이 생성됩니다.
인덱스를 역방향으로 스캔하여 ORDER BY x DESC를 만족하는 출력을 생성할 수도 있습니다.
예를 들어 인덱스를 생성할 때 ASC, DESC, NULLS FIRST, NULLS LAST 옵션을 포함하여 B-tree 인덱스의 순서를 조정할 수 있습니다.
CREATE INDEX test2_info_nulls_low ON test2(정보 NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);Null이 먼저 오름차순으로 저장된 인덱스는 스캔하는 방향에 따라 ORDER BY x ASC NULLS FIRST 또는 ORDER BY x DESC NULLS LAST를 만족할 수 있습니다.
두 가지 옵션과 역방향 스캔 가능성을 함께 사용한다면 ORDER By의 모든 변형을 포괄할 수 있는데 왜 굳이 네 가지 옵션을 모두 제공해야 하는지 의문이 들 수 있습니다.
단일 열 인덱스에서는 옵션이 실제로 중복되지만, 다중 열 인덱스에서는 유용할 수 있습니다.
(x, y)에 대한 두 개의 열 인덱스를 예로 들어보겠습니다.
이 인덱스는 앞으로 스캔할 경우 ORDER BY x, y를 만족시킬 수 있고, 뒤로 스캔할 경우 ORDER BY x DESC, y DESC를 만족시킬 수 있습니다.
하지만 애플리케이션에서 ORDER BY x ASC, y DESC를 자주 사용해야 할 수도 있습니다. 일반 인덱스에서는 이러한 순서를 얻을 수 있는 방법이 없지만, 인덱스가 (x ASC, y DESC) 또는 (x DESC, y ASC)로 정의되어 있으면 가능합니다.
물론 기본 정렬 순서가 아닌 인덱스는 상당히 특수한 기능이지만, 때로는 특정 쿼리에 대해 엄청난 속도 향상을 가져올 수 있습니다. 이러한 인덱스를 유지할 가치가 있는지 여부는 특별한 정렬 순서가 필요한 쿼리를 얼마나 자주 사용하는지에 따라 달라집니다.
11.5 여러 인덱스 결합
단일 인덱스 스캔은 인덱스의 열을 해당 연산자 클래스의 연산자와 함께 사용하고 AND로 조인된 쿼리 절만 사용할 수 있습니다. 예를 들어, (a, b)에 대한 인덱스가 주어졌을 때 WHERE a = 5 AND b = 6과 같은 쿼리 조건은 인덱스를 사용할 수 있지만, WHERE a = 5 OR b= 6와 같은 쿼리는 인덱스를 직접 사용할 수 없습니다.
다행히도 PostgreSQL은 단일 인덱스 스캔으로는 구현할 수 없는 경우를 처리하기 위해 여러 인덱스를 결합할 수 있는 기능을 갖추고 있습니다. 시스템은 여러 인덱스 스캔에 걸쳐 AND 및 OR 조건을 구성할 수 있습니다. 예를 들어 WHERE x = 42 OR x = 47 OR x = 53 OR x = 99와 같은 쿼리는 x에 대한 인덱스를 네 번의 개별 스캔으로 나눌 수 있으며, 각 스캔은 쿼리 절 중 하나를 사용합니다. 그런 다음 이러한 스캔의 결과를 함께 OR 하여 결과를 생성합니다. 또 다른 예로 x와 y에 대한 별도의 인덱스가 있는 경우, WHERE x = 5 AND y = 6과 같은 쿼리를 구현할 수 있는 한 가지 방법은 인덱스에 적절한 쿼리 절을 사용한 다음 인덱스 결과를 함께 AND하여 결과 행을 식별하는 것입니다.
여러 인덱스를 결합하기 위해 시스템은 필요한 각 인덱스를 스캔하고 해당 인덱스의 조건과 일치하는 것으로 보고된 테이블 행의 위치를 제공하는 비트맵을 메모리에 준비합니다.
그런 다음 쿼리에서 필요에 따라 비트맵을 AND와 OR로 결합합니다. 마지막으로, 실제 테이블 행이 방문되고 반환됩니다.
테이블 행은 비트맵이 배치되는 방식에 따라 물리적 순서대로 방문되며, 이는 원래 인덱스의 순서가 손실된다는 것을 의미하므로 쿼리에 ORDER BY 절이 있는 경우 별도의 정렬 단계가 필요합니다. 이러한 이유로 인덱스 스캔을 추가할 때마다 시간이 추가되므로 플래너는 사용할 수 있는 추가 인덱스가 있음에도 불구하고 단순 인덱스 스캔을 하용하기로 선택하는 경우가 있습니다.
가장 단순한 애플리케이션을 제외한 모든 애플리케이션에는 유용할 수 있는 다양한 인덱스 조합이 있으며, 데이터베이스 개발자는 어떤 인덱스를 제공할지 결정하기 위해 절충안을 만들어야 합니다.
때로는 다중 열 인덱스가 가장 좋지만, 때로는 별도의 인덱스를 만들고 인덱스 조합 기능을 사용하는 것이 더 낫습니다.
예를 들어, workload에 때로는 열 x만, 때로는 열 y만, 때로는 두 열이 모두 포함되는 쿼리가 혼합되어있는 경우, 두 열을 모두 사용하는 쿼리를 처리하기 위해 인덱스 조합에 의존하여 x와 y에 별도의 인덱스 두 개를 생성하거나 (x, y)에 다중 열 인덱스를 만들 수도 있습니다.
이 인덱스는 일반적으로 두 열을 모두 포함하는 쿼리에서는 인덱스 조합보다 더 효율적이지만, 11.3절에서 설명한 것처럼 y만 포함하는 쿼리에서는 거의 쓸모가 없으므로 유일한 인덱스가 되어서는 안됩니다. 다중 열 인덱스와 y에 대한 별도의 인덱스를 조합하면 상당히 잘 작동할 것입니다. x만 포함하는 쿼리의 경우, 다중 열 인덱스를 사용할 수 있지만, 이 인덱스는 더 커서 x에 대한 인덱스만 사용하는 것보다 느릴 수 있습니다.
마지막 대안은 세 가지 인덱스를 모두 생성하는 것이지만, 이는 테이블이 업데이트되는 빈도보다 훨씬 더 자주 검색되고 세 가지 유형의 쿼리가 모두 일반적인 경우에만 합리적일 수 있습니다. 쿼리 유형 중 하나가 다른 유형보다 훨씬 더 일반적인 경우, 일반적인 유형과 가장 잘 일치하는 두 개의 인덱스만 생성하는 것으로 만족해야 할 것입니다.
11.6 고유 인덱스
인덱스를 사용하여 열 값의 고유성 또는 둘 이상의 결합된 값의 고유성을 강제할 수도 있습니다.
CREATE UNIQUE INDEX name ON table (column [, ...]) [ NULLS [ NOT ] DISTINCT ];현재 B-tree 인덱스만 고유하게 선언할 수 있습니다.
인덱스가 고유하게 선언되면 인덱스 값이 동일한 여러 테이블 행이 허용되지 않습니다.
기본적으로 고유 열의 null 값은 동일것으로 간주되지 않으므로 열에 여러 개의 null이 허용됩니다.
NULLS NOT DISTINCT 옵션을 수정하여 인덱스가 null을 동등한 것으로 취급하도록 합니다.
다중 열 고유 인덱스는 인덱싱된 모든 열이 여러 행에서 동일한 경우만 거부합니다.
테이블에 대해 고유 제약 조건 또는 기본 키가 정의되면 PostgreSQL은 자동으로 고유 인덱스를 생성합니다. 이 인덱스는 기본 키 또는 고유 제약 조건을 구성하는 열을 포함하며 제약 조건을 적용하는 메커니즘입니다.
참고
고유 열에 인덱스를 수동으로 만들면 자동으로 생성된 인덱스가 복제될 뿐이므로 수동으로 만들 필요가 없습니다
11.7 인덱스의 표현식
인덱스 열은 기초 테이블의 열일 필요는 없으며 테이블의 하나 이상의 열에서 계산된 함수 또는 스칼라 표현식이 될 수 있습니다. 이 기능은 계산 결과를 기반으로 테이블에 빠르게 엑세스하는 데 유용합니다.
예를 들어 대소문자를 구분하지 않는 비교를 수행하는 일반적인 방법을 하위 함수를 사용하는 것입니다.
SELECT * FROM test1 WHERE lower(col1) = 'value';이 쿼리는 lower(col1) 함수의 결과에 인덱스가 정의되어 있는 경우 인덱스를 사용할 수 있습니다.
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));이 인덱스를 UNIQUE로 선언하면 col1 값이 대소문자만 다른 행과 실제로는 동일한 행의 생성을 방지할 수 있습니다. 따라서 표현식의 인덱스는 단순한 고유 제약 조건으로 정의할 수 없는 제약 조건을 적용하는 데 사용할 수 있습니다.
다른 예로 다음과 같은 쿼리를 자주 수행한다고 가정해보겠습니다.
SELECT * FROM people WHERE (first_name || '' || last_name) = 'John Smith';이 경우 다음과 같이 인덱스를 생성하는 것이 좋습니다.
CREATE INDEX people_names ON people((first_name || '' || last_name));두 번째 예에서와 같이 CREATE INDEX 명령의 구문은 일반적으로 인덱스 표현식 주위에 괄호를 써야 합니다. 첫 번째 예제에서처럼 표현식이 함수 호출일 때는 괄호를 생략할 ㅅ ㅜ있습니다.
인덱스 표현식은 각 행 삽입 및 비-HOT 업데이트에 대해 파생된 표현식을 계산해야 하므로 유지 관리 비용이 상대적으로 많이 듭니다. 그러나 인덱스 표현식은 이미 인덱스에 저장되어 있기 때문에 인덱스 검색 중에 다시 계산되지 않습니다. 위의 두 예제에서 시스템은 쿼리를 WHERE indexedcolumn = '상수'로 간주하므로 검색 속도는 다른 간단한 인덱스 쿼리와 동일합니다. 따라서 표현식에 대한 인덱스는 삽입 및 업데이트 속도보다 검색 속도가 더 중요한 경우에 유용합니다.
