| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 11. 인덱스 | Fast Forward | Next |
부분 인덱스란, 테이블의 부분 집합에 구축되는 인덱스입니다. 부분 집합은 (부분 인덱스의술어로 불림) 조건식에서 정의됩니다. 부분 인덱스에는 그 술어를 채우는 테이블행에만 대하는 항목이 포함됩니다. 부분 인덱스는 특별한 기능입니다. 그러나 이것들이 유용이 되는 상황이 다수 있습니다.
부분 인덱스를 이용하는 주된 목적은 빈출치에 대해서 인덱스를 작성하지 않게 하는 것입니다. (테이블 전체의 행 가운데, 몇 퍼센트 이상을 차지함) 빈출치를 검색하는 쿼리는 어떠한 경우에서도 인덱스를 사용하지 않기 때문에, 그것들에 대해서 인덱스를 작성해도 전혀 의미가 없습니다. 이 때문에, 인덱스의 크기를 작게 할 수 있어 그 결과, 인덱스를 사용하는 쿼리가 빨라집니다. 또한, 인덱스를 업데이트할 필요 없는 경우도 있기 때문에, 테이블을 업데이트하는 연산도 빨라집니다. Example 11-1 에, 이 개념에 근거한 용례를 나타냅니다.
Example 11-1. 빈출치를 제외하기 위한 부분 인덱스의 작성
웹 서버의 액세스 로그를 데이터베이스에 저장하고 있다고 합니다. 많은 액세스는 사내의 IP주소의 범위내로부터 발신되고 있습니다. 그러나 범위외의 주소(예를 들면, 사원이 다이얼 업 접속하고 있는 장소)로부터의 발신도 있습니다. 주로 범위외로부터의 액세스를 IP주소로 검색하는 경우, 사내의 하위네트워크에 해당하는 IP주소의 범위에 인덱스를 작성할 필요는 없을 것입니다.
이하와 같은 테이블을 가정합니다.
CREATE TABLE access_log (
url varchar,
client_ip inet,
...
);
이 예에 적절한 부분 인덱스를 작성하려면 , 이하와 같은 커맨드를 사용합니다.
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');
이 인덱스를 사용할 수 있는 쿼리의 전형적인 예는 이하와 같은 것입니다.
SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
또한, 이하와 같은 쿼리의 경우, 이 인덱스는 사용할 수 없습니다.
SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';
이러한 부분 인덱스를 사용하려면, 사전에 빈출치가 무엇으로 있을까를 알고 있을 필요가 있습니다. 값의 분포가(어플리케이션의 성질상) 고유해, 한편 정적(시간이 지나도 변화하지 않는다)인 경우, 이러한 부분 인덱스의 작성은 어려운 일이 아닙니다. 그러나 빈출치가, 단지 로드된 데이터에 우연히 일치한 것일 경우, 시간 경과에 수반해 인덱스 정의를 변경하는 보수 작업이 매우 복잡해집니다.
부분 인덱스를 사용하는 다른 가능한 방법은 일반적인 쿼리에 필요 없는 값을 인덱스로부터 없애는 것입니다 (Example 11-2를 참조해 주십시요). 이 방법의 이점은 위에 나타난 것과 같습니다. 다만, 이 방법을 사용하면, 인덱스 스캔이 적합한 경우에서도, "필요 없는 "값에 인덱스를 개입시킨 액세스가 방지되어 버립니다. 분명히, 이러한 케이스로 부분 인덱스를 작성할 때는 세심의 주위를 기울여, 충분한 검증을 실시할 필요가 있습니다.
Example 11-2. 필요 없는 값을 제외하기 위한 부분 인덱스의 작성
지불이 끝난 주문서 및 미지불 주문서를 포함하는 1 개의 테이블이 있다고 합니다. 그리고 미지불의 주문서가 테이블 전체에 대한 비율이 작은 한편, 그 부분에 액세스 수가 가장 많았다고 합니다. 이러한 경우, 미지불 행에만 인덱스를 작성하는 함으로서 성능을 향상시킬 수 있습니다. 인덱스의 작성에는 이하와 같은 커맨드를 사용합니다.
CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;
이 인덱스를 사용하는 쿼리의 예로서는 다음의 것이 생각됩니다.
SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;
그러나 이 인덱스는 order_nr를 전혀 사용하지 않는 쿼리에서도 사용할 수 있습니다. 이하는 그 예입니다.
SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;
이 쿼리에서는 시스템이 인덱스 전체를 검색할 필요가 있기 때문에, amount열에 부분 인덱스를 작성했을 경우만큼 효율은 좋지 않습니다. 그러나 미지불 주문서 데이터가 적을 경우, 부분 인덱스를 미지불 주문서를 발견하기 위해서만 사용하는 것이 효율이 좋을 것 있습니다.
이하의 쿼리에서는 이 인덱스를 사용할 수 없는 것에 주의해 주십시요.
SELECT * FROM orders WHERE order_nr = 3501;
주문 번호 3501은 지불이 끝난 상태일지도 모르고, 미지불일지도 모르기 때문입니다.
Example 11-2은 또한 인덱스된 열과 술어에서 사용된 열은 일치할 필요가 없다는 것을 설명하고 있습니다. PostgreSQL에서는 인덱스된 테이블의 열만이 포함되어 있다면, 임의의 술어로 부분 인덱스를 사용할 수 있습니다. 그러나 이 술어는 인덱스를 사용하고자 하는 쿼리의 조건과 일치시켜야 한다는 것에 유의하십시요. 정확히 말하면, 부분 인덱스를 쿼리로 사용하기 위해서는 인덱스의 술어가 쿼리의WHERE조건에 수학적으로 일치한다고 시스템이 판단할 수 있는 경우만입니다. PostgreSQL에는 다른 형식의 표현식이 수학적으로 동등하다고 판단하는 것과 같은 세련된 증명 기능은 없습니다 (그러한 범용적인 증명 기능의 작성은 매우 곤란하고, 실제 이용은 너무 실행 속도가 너무 늦을 것입니다). 시스템에서는 예를 들면"x < 1"은"x < 2"를 의미하는 단순한 비교 연산자의 의미는 인식 가능합니다. 그러나 그 이외의 경우는 술어 조건은 쿼리의WHERE조건과 완전하게 일치하고 있을 필요가 있습니다. 일치하고 있지 않는 경우는 인덱스는 사용 가능하다고 인식되지 않습니다. 일치할지 어떨지는 실행시가 아닌, 쿼리 계획 작성시에 판정됩니다. 따라서, 파라미터 첨부의 쿼리에서는 부분 인덱스는 동작하지 않습니다. 예를 들어, "x < ? "과 지정된 파라미터를 가지는 준비된 쿼리에서는 어떠한 파라미터 값으로 "x < 2"를 나타내지 않습니다.
부분 인덱스를 위해 사용되는 3번째 가능성은 쿼리로 인덱스를 전혀 사용하지 않습니다. 이 생각은 테이블의 부분 집합에 유일한 인덱스를 작성한다고 하는 것입니다 (Example 11-3를 참조해 주십시요). 이것에 의해, 인덱스 술어를 만족하지 않는 행을 제약하는 일 없이, 그 술어를 만족하하는 행에서의 일관성을 강요합니다.
Example 11-3. 유일한 부분 인덱스의 작성
테스트의 결과가 저장되고 있는 테이블이 있다고 합시다. 주어진 subject 및 대상(target)의 조합에 대해서, "성공"의 항목은 확실히 1개 밖에 없게 합니다. "실패"의 엔트리는 다수 있어도 상관하지 않습니다. 이하에, 이것을 실행하는 일례를 나타냅니다.
CREATE TABLE tests (
subject text,
target text,
success boolean,
...
);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
이는 성공하는 테스트가 거의 없고, 실패하는 테스트가 많은 경우에 특히 유효한 방법입니다.
마지막으로, 부분 인덱스는 시스템의 쿼리 계획의 선택을 변경하기 위해서도 사용할 수 있습니다. 특수한 데이터 분포를 가지는 데이터 집합에서는 시스템이 실제로 사용해서는 안되는 인덱스를 사용해 버리는 일이 있습니다. 이러한 경우, 특정한 쿼리에서는 사용할 수 없는 인덱스를 설정할 수 있습니다. 보통, PostgreSQL에서는 인덱스의 사용에 대해 적절한 선택을 실시합니다(예를 들면, 빈출치의 검색에는 인덱스를 사용하지 않으므로, 이전의 예는 인덱스의 크기를 실제로 작게 할 뿐, 인덱스의 사용을 제한할 필요는 없습니다). 그리고 완전히 부적절한 계획을 선택하는 것 같으면, 버그로 보고해 주십시요.
부분 인덱스를 작성하려면 , 적어도 쿼리 실행 planner와 동등의 지식을 가지고 있는 것, 특히, 인덱스가 도움이 되는 상황을 이해하고 있을 필요가 있습니다. 이러한 지식을 얻기 위해서는 PostgreSQL로 인덱스가 어떻게 기능하는지를 이해해, 경험을 쌓는 것이 필요합니다. 대부분의 경우에서는 보통의 인덱스와 비교해, 부분 인덱스를 사용하는 이점은 많지 않습니다.
부분 인덱스의 상세한 정보는 부분적 인덱스 경우 , POSTGRES의 부분적 인덱싱 : 탐구 프로젝트 , 및 Generalized Partial Indexes (캐쉬된 버전) 을 참조해 주십시요.