[Database] PostgreSQL - Index

지난 시간에는 PostgreSQL 에 대한 보안 정책에 대해 알아보았습니다. 이번 시간에는 PostgreSQL의 Index 에 대해 알아보겠습니다.

---

개요

아래와 비슷한 테이블이 있다고 가정해봅시다.

CREATE TABLE test1 (
    id integer,
    content varchar
);

 

그리고 해당 테이블을 사용하는 애플리케이션 쪽에서 다음과 같은 쿼리를 수없이 요청했다고 가정해봅시다.

SELECT content FROM test1 WHERE id = constant;

 

만약 기본적인 세팅만 되어있다면, 시스템은 모든 일치하는 항목을 찾기 위해 test1 테이블 전체를 한 줄씩 스캔해야 할 것입니다. test1 에 행이 많고 그러한 쿼리에 의해 반환되는 행이 몇 개에 불과하다면, 이것은 분명히 비효율적인 방법입니다. 그러나 시스템이 id 열에 인덱스를 유지하도록 지시 받았다면, 일치하는 행을 찾기 위해 더 효율적인 방법을 사용할 수 있습니다. 예를 들어, 잘 정리된 Search Tree 의 몇 단계만 더 들어가면 쉽게 결과 값을 찾을 수 있습니다.

 

대부분의 Non-fiction 서적에서도 비슷한 접근 방식을 사용하는데, 독자들이 자주 찾는 용어와 개념은 책의 마지막에 알파벳 색인(Index) 으로 모여있습니다. 관심 있는 독자는 관심 있는 내용을 찾기 위해 책 전체를 읽을 필요 없이 색인을 비교적 빨리 스캔하여 해당 페이지로 이동할 수 있습니다. 독자가 찾을 가능성이 높은 항목을 예상하는 것이 저자의 과제인 것처럼, 어떤 색인이 유용할지 예측하는 것이 데이터베이스 프로그래머의 과제입니다.

 

다음 명령을 사용하여 id 열에 인덱스를 만들 수 있습니다.

CREATE INDEX test1_id_index ON test1 (id);

 

test1_id_index 라는 이름은 자유롭게 선택할 수 있지만, 나중에 인덱스의 용도를 기억할 수 있게 네이밍되는 것이 좋습니다.

 

인덱스를 제거하려 DROP INDEX 명령을 사용합니다. 인덱스는 언제든지 테이블에 추가하거나 테이블에서 제거할 수 있습니다.

 

인덱스가 생성되면 더 이상 추가적인 조치를 할 필요가 없습니다. 테이블이 수정되면 시스템이 인덱스를 업데이트하고 순차적인 테이블 스캔보다 더 효율적이라고 생각할 때 쿼리에 인덱스를 사용합니다. 그러나 쿼리 플래너가 제대로 교육된 결정을 내릴 수 있도록 통계를 업데이트하려면 ANALYZE 명령을 정기적으로 실행해야 할 수도 있습니다.

 

Planner/Optimizer 의 작업은 최적의 실행 계획을 만드는 것입니다. 주어진 SQL 쿼리 (혹은 쿼리 트리)는 실제로 매우 다양한 방식으로 실행될 수 있으며, 이들 각각은 동일한 결과 세트를 생성합니다. 계산 가능하면 쿼리 Optimizer 는 이러한 가능한 실행 계획 각각을 검사하여 궁극적으로 가장 빠르게 실행될 것으로 예상되는 실행 계획을 선택합니다.

일부 상황에서는 쿼리를 실행할 수 있는 가능한 각 방법을 검사하는데 과도한 시간과 메모리가 소요됩니다. 특히 이는 많은 수의 조인 작업이 포함된 쿼리를 실행할 때 발생합니다. 합리적인 시간 내에 합리적인 쿼리 계획을 결정하기 위해 PostgreSQl 은 조인 수가 임계 값을 초과하면 유전자 쿼리 최적화기를 사용합니다.

Planner 의 검색 절차는 실제로 Path 라고 불리는 데이터 구조와 함께 작동하는데, 이는 Planner 가 결정을 내리기만 하면 되는 만큼의 정보만 포함하는 Plan 을 간단히 잘라낸 것입니다. 가장 저렴한 Path 가 결정된 후에는 실행자에게 전달하기 위한 본격적인 Plan Tree 가 구축됩니다. 이것은 실행자가 충분히 상세하게 원하는 실행 계획을 나타냅니다.

테이블 내의 데이터 분포를 크게 변경할 때마다 ANALYZE 명령을 실행하는 것이 좋습니다. 여기에는 대량의 데이터를 테이블에 대량으로 로드하는 것이 포함됩니다. ANALYZE 명령을 실행하는 것은 쿼리 플래너가 테이블에 대한 최신 통계를 갖도록 보장합니다. 통계가 없거나 오래된 통계가 없으면 쿼리 플래너가 쿼리 계획 중에 잘못된 결정을 내릴 수 있으며, 이로 인해 부정확하거나 존재하지 않는 통계가 있는 테이블의 성능이 저하될 수 있습니다. Auto Vaccum Deamon 이 활성화된 경우, ANALYZE 명령은 자동으로 실행될 것 입니다.

 

 

인덱스는 또한 검색 조건에 따라 UPDATE 및 DELETE 명령에 도움이 될 수 있습니다. 인덱스는 또한 조인 검색에 사용될 수 있습니다. 따라서 조인 조건의 일부인 열에 정의된 인덱스는 조인을 사용한 쿼리 속도를 상당히 높일 수 있습니다.

 

일반적으로 PostgreSQL 인덱스는 하나 이상의 WHERE 절 또는 JOIN 절을 포함하는 쿼리를 최적화하는 데 사용할 수 있습니다.

 

규모가 큰 테이블에 인덱스를 생성하면 시간이 오래 걸릴 수 있습니다. 기본적으로 PostgreSQL 은 인덱스 생성과 동시에 테이블에 읽기(SELECT 문) 가 발생하도록 허용하지만 쓰기 (INSERT, UPDATE, DELETE) 는 인덱스 빌드가 완료될 때까지 차단됩니다. 프로덕션 환경에서는 이러한 차단 행위가 허용되지 않는 경우가 많습니다. 인덱스 생성과 동시에 쓰기가 발생하도록 허용할 수 있지만, 매우 조심해서 다뤄야하는 부분입니다.

 

인덱스가 생성된 후 시스템은 인덱스를 테이블과 동기화된 상태로 유지해야 합니다. 그러면 데이터 조작 작업에 오버헤드가 발생합니다. 인덱스는 힙 전용 튜플이 생성되는 것을 방지할 수도 있습니다. 따라서 쿼리에서 거의 사용되지 않거나 전혀 사용되지 않는 인덱스는 제거해야 합니다.

 

---

Index Types

PostgreSQL 은 B-tree, Hash, GiST, SP-GiST, GIN, BRIN 및 확장 블룸의 여러 인덱스 유형을 제공합니다. 각 인덱스 유형은 각각의 인덱스 구문에 가장 적합한 서로 다른 알고리즘을 사용합니다. 기본적으로 CREATE INDEX 명령은 가장 일반적인 상황에 맞는 B-tree 인덱스를 생성합니다. 다른 인덱스 유형은 키워드 USING 다음에 인덱스 유형 이름을 작성하여 선택합니다. Hash Index 의 경우를 예로 들면 다음과 같습니다.

CREATE INDEX name ON table USING HASH (column);

 

B-Tree

B-tree 는 등식 및 범위 쿼리를 처리할 수 있으며 일부 순서로 정렬할 수 있습니다. 특히 PostgreSQL 쿼리 플래너는 인덱스된 열이 다음 연산자 중 하나를 사용하여 비교할 때마다 B-tree 인덱스를 사용하는 것을 고려할 것입니다.

<   <=   =   >=   >

 

BETWEEN 및 IN 과 같은 연산자의 조합과 동일한 구성을 B-tree 인덱스 검색으로 구현할 수도 있습니다. 또한 인덱스 열에 있는 IS NULL 또는 IS NOT NULL 조건을 B-tree 인덱스와 함께 사용할 수도 있습니다.

 

Optimizer 는 패턴이 상수이고 문자열의 Prefix 에 고정되어 있는 경우, 예를 들어 col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar' 과 같은 패턴 매칭 연산자를 포함하는 쿼리에 B-tree 인덱스를 사용할 수도 있습니다. 그러나 데이터베이스가 C-Locale을 사용하지 않는 경우 패턴 매칭 쿼리의 인덱싱을 지원하기 위해 특수 연잔자 클래스를 사용하여 인덱스를 생성해야 합니다. 

PostgreSQL의 C 로케일은 대조 및 문자 분류를 위해 표준 C 라이브러리의 로케일을 사용하는 것을 지정하는 로케일 설정입니다. 여기서 "대조"는 문자열이 정렬되고 비교되는 방법을 결정하는 규칙을 의미하는 반면, "문자 분류"는 문자가 문자인지, 숫자인지 또는 공백인지와 같은 문자 속성을 식별하는 것을 포함합니다.

 

B-tree 인덱스는 정렬된 순서대로 데이터를 검색하는 데에도 사용될 수 있습니다. 이는 단순한 스캔 및 정렬보다 항상 빠른 것은 아니지만, 종종 도움이 됩니다.

 

Hash

해시 인덱스는 인덱싱된 열 값에서 파생된 32비트 해시 코드를 저장합니다. 따라서 이러한 인덱스는 단순한 등식 비교만 처리할 수 있습니다. 쿼리 플래너는 인덱싱된 열이 등식 연산자를 사용한 비교와 관련될 때마다 해시 인덱스를 사용하는 것을 고려할 것 입니다.

 

GIST

GIST 인덱스는 단일 종류의 인덱스가 아니라 다양한 인덱싱 전략을 구현할 수 있는 인프라입니다. 따라서, GiST 인덱스를 사용할 수 있는 특정 연산자는 인덱싱 전략 (연산자 클래스) 에 따라 달라집니다. 예를 들어, PostgreSQL의 표준 분포는 다음 연산자를 사용하여 인덱싱된 쿼리를 지원하는 여러 2찬원 기하학적 데이터 유형에 대한 GiST 연산자 클래스를 포함합니다.

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

 

또한 GiST 인덱스는 다음과 같은 "nearest-neighbor" 검색을 최적화할 수 있습니다.

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

 

위의 예제처럼 주어진 목표 지점에 가장 가까운 지점을 찾습니다. 이를 수행할 수 있는 기능은 다시 사용 중인 특정 연산자 클래스에 따라 달라집니다.

"Neareast-Neighbor" 

주어진 쿼리 포인트에 가장 가까운 데이터 세트의 데이터 포인트를 찾는 것을 포함합니다. 이러한 유형의 검색은 공간 데이터베이스, 지리 정보 시스템(GIS) 및 다차원 데이터를 포함하는 기타 응용 프로그램에서 일반적으로 사용됩니다.

 

GiST Index 가 Neareast-Neighbor 를 검색할 때 사용하는 특성

1. 다차원 데이터 처리: GiST 는 다차원 데이터를 인덱싱할 수 있으므로 다차원 공간에서 점 간 거리를 계산해야 하는 검색에 적합합니다.
2. 거리 계산: GiST 에서 가장 가까운 이웃 검색은 거리 메트릭을 사용하여 가장 가까운 지점을 찾습니다. 공간 데이터의 경우 유클리드 거리, 맨허튼 거리 혹은 기타 관련 메트릭일 수 있습니다.

 

SP-GiST

 

SP-GiST 인덱스는 GiST 인덱스와 마찬가지로 다양한 종류의 검색을 지원하는 인프라를 제공합니다. SP-GiST 를 사용하면 쿼드트리, K-D 트리, Radix-Tree 와 같은 다양한 불균형 디스크 기반 데이터 구조를 구현할 수 있습니다.

# SP-GiST 지원 연산자들
<<   >>   ~=   <@   <<|   |>>

 

GIN

GIN 인덱스는 배열과 같이 여러 개의 성분 값을 포함하는 데이터 값에 적합한 "역방향 인덱스" 입니다. 역방향 인덱스는 각 성분 값에 대한 별도의 항목을 포함하며 특정 성분 값의 존재를 테스트하는 쿼리를 효율적으로 처리할 수 있습니다.

 

GiST 및 SP-GiST 와 마찬가지로 GIN은 다양한 사용자 정의 인덱싱 전략을 지원할 수 있으며, GIN 인덱스를 사용할 수 있는 특정 연산자는 인덱싱 전략에 따라 다릅니다. 예를 들어, PostgreSQL 의 표준 배포한에는 배열 전용 GIN 연산자 클래스가 포함되어 있으며, 이들 연산자를 사용하여 인덱싱된 쿼리를 지원합니다.

<@   @>   =   &&

 

BRIN (Block Range Index)

BRIN 인덱스는 테이블의 연속적인 물리적 블록 범위에 저장된 값에 대한 요약을 저장합니다. 따라서 값이 테이블 행의 물리적 순서와 잘 상관되어 있는 열에 가장 효과적입니다. GiST, SP-GiST 및 GIN 과 같이 BRIN 은 다양한 인덱싱 전략을 지원할 수 있으며, BRIN 인덱스를 사용할 수 있는 특정 연산자는 인덱싱 전략에 따라 다릅니다. 선형 정렬 순서를 갖는 데이터 유형의 경우, 인덱싱된 데이터는 각 블록 범위에 대한 열의 값의 최소값 및 최대값에 해당합니다. 이는 다음 연산자들을 사용하여 인덱싱된 쿼리를 지원합니다.

<   <=   =   >=   >

 

---

Indexed and ORDER BY

인덱스는 쿼리에서 반환할행을 단순히 찾을 뿐만 아니라 특정하게 정렬된 순서로 전달할 수도 있습니다. 이를 통해 별도의 정렬 단게 없이 쿼리의 OREDER BY 사양을 준수할 수 있습니다. 현재 PostgreSQL 에서 지원하는 인덱스 유형 중 B-tree 만 정렬된 출력을 생성할 수 있습니다.

 

 쿼리 플래너는 규격과 일치하는 사용 가능한 인덱스를 스캔하거나 테이블을 물리적 순서로 스캔하여 명시적 정렬을 수행하여 ORDER BY 사양을 만족시키는 것을 고려합니다. 테이블의 많은 부분을스캔해야 하는 쿼리의 경우, 명시적 정렬은 순차적 액세스 패턴을 따르기 때문에 디스크 I/O 가 덜 필요하기 떄문에 인덱스를 사용하는 것보다 빠를 수 있습니다. 인덱스는 몇 개의 행만 가져온다면 더 유용합니다. 고려할 점이 있는 특수한 케이스는 LIMIT N 과 결합된 ORDER BY 구문입니다. 명시적 정렬은 처음 N 개의 행을 식별하기 위해 모든 데이터를 처리해야 하지만 ORDER BY 와 일치하는 인덱스가 있는 경우 나머지 행을 스캔하지 않고 처음 N 개의 행만 바로 가져올 수 있습니다. 

 

기본적으로 B-tree 인덱스는 Null 을 마지막으로 오름차순으로 항목을 저장합니다. 이는 특정 열(X column) 에 대한 인덱스의 순방향 스캔이 ORDER BY X 를 만족하는 출력을 생성한다는 것을 의미합니다. 인덱스는 또한 역방향 스캔이 가능하여 ORDER BY X DESC 를 만족하는 출력을 생성할 수 있습니다.

 

인덱스를 생성할 때 ASC, DESC, NULLS FIRST 혹은 NULLS LAST 옵션을 포함하여 B-tree 인덱스의 순서를 조정할 수 있습니다.

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

 

---

여기까지 PostgreSQL의 Index에 대해서 알아보았습니다. 다음 시간에는 PostgreSQL의 트랜잭션 처리에 대해 알아보겠습니다.