1. INDEX란?
- 인덱스는 테이블의 데이터에 대한 포인터입니다.
- 인덱스는 데이터베이스 검색 엔진이 데이터 검색 속도를 높여주도록 성능을 향상시켜 줍니다.
2. INDEX TYPES
B-Tree 인덱스 유형만 기본 인덱스 유형이기 때문에 CREATE INDEX 할때 추가 옵션이 필요하지 않지만 다른 유형의 인덱스를 만들려면 표기를 해야 합니다.
2.1. B-Tree(Balanced-Tree)
2.1.1. B-Tree란?
-
-
- PostgreSQL의 CREATE INDEX 명령에 대한 기본 인덱스 유형입니다.
- 모든 데이터 유형과 호환되며 대부분의 경우에 적합한 가장 일반적인 인덱스 유형입니다.
- 정렬된 데이터를 유지하고 데이터 검색, 삽입, 삭제 및 순차적 액세스를 허용하는 self-balancing 트리입니다.
- 쿼리 플래너는 쿼리에 아래 연산자 중 하나를 사용하여 인덱스된 칼럼을 비교할 때마다 B-Tree 인덱스를 사용합니다.
-
<, <=, =, >=, BETWEEN, IN, IS NULL, IS NOT NULL-
-
- 그리고 패턴이 변하지 않고 고정적일때
LIKE와~과 같은 패턴 매칭 연산자가 쿼리에 포함되어 있으면 쿼리 플래너는 B-Tree 인덱스를 사용할 수 있습니다.
- 그리고 패턴이 변하지 않고 고정적일때
-
name LIKE 'foo%'
name ~ '^foo'-
-
- 인덱스 생성 구문은 다음과 같습니다.
-
CREATE INDEX index_name ON table_name USING BTREE(column_name);2.2. Hash Index
2.2.1. Hash Index란?
-
-
- B-Tree 인덱스보다 빠르지만 등식 연산으로만 제한됩니다.
- 해시 인덱스는 동일 조건 = 가 쿼리에서 사용되는 경우에만 적용되는 특정 인덱스 유형입니다.
- 인덱싱된 칼럼 값에서 파생된 32비트 해시 코드를 저장하기 때문에 해시라고 불립니다.
-
CREATE INDEX index_name ON table_name USING HASH(column_name);2.3. GIN(Generalized Inverted INdex)
2.3.1. GIN 이란?
-
-
- 단일 칼럼에 여러 값을 포함하는 데이터 유형이 있을 때 가장 유용하게 사용됩니다.
- GIN은 여러 값을 한 행에 매핑하는 데에 적합합니다.
- GIN 인덱스를 적용하는 가장 일반적인 사례는 배열, 범위 타입, JSON, 전체 택스트 검색과 같은 데이터 유형을 사용하는 작업들입니다.
- INSERT 및 UPDATE 작업의 경우 GIN 인덱스가 느려집니다.
-
2.4. Gist(Generalized Search Tree)
2.4.1. GiST란?
-
-
- 데이터가 기하학적 형식일때 유용하게 사용할 수 있습니다.
- GiST 인덱스를 사용하면 트리 구조를 사용하여 새로운 데이터 유형을 인덱싱할 수 있습니다.
- 예 : 기하학적 데이터 타입이나 네트워크 주소 데이터
- GiST는 B-Tree가 인덱싱할 수 없는 쿼리가 있는 경우에도 유용합니다.
-
2.5. SP-GiST(Space-Partitioned GiST)
2.5.1. SP-GiST란?
-
-
- SP-GiST 인덱스는 GiST인덱스와 유사하지만 분할된 검색 트리를 사용하여 불균형 데이터 구조를 인덱싱하므로 SEARCH 및 INSERT작업을 더욱 단순화 시킵니다.
- 이러한 구조의 공통점은 검색 공간을 동일한 크기일 필요가 없는 파티션으로 반복적으로 분할한다는 것입니다.
- 데이터가 클러스터링 요소이거나 클러스터링 된 형식일때 유용하고, 가장 좋은 예는 전화번호 입니다.
-
2.6. BRIN(Block Range INdex)
2.6.1. BRIN이란?
-
-
- BRIN은 특정 칼럼이 테이블 내의 물리적위치와 자연스러운 상관관계를 갖는 큰 테이블에 적용됩니다.
- 블록의 범위는 테이블에서 물리적으로 인접한 페이지 그룹입니다.
- BRIN 인덱스는 각 블록 범위에 대한 페이지 번호와 최소 및 최대 값을 저장합니다.
-
3. CREATE/DROP INDEX
3.1. CREATE INDEX
-
- Primary key나 unique constraint를 정의할 때 unique 인덱스가 자동으로 생성됩니다.
- 인덱스 종류 생략 시 기본적으로 B-Tree로 생성됩니다.
- CREATE INDEX의 시놉시스는 다음과 같습니다.
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]-
-
- CONCURRENTLY : 이 옵션을 사용할 경우 테이블에서 동시 INSERT, UPDATE, DELETE를 방지하는 lock을 사용하지 않고 인덱스를 빌드합니다. 반면 표준 인덱스 빌드는 완료될 때까지 테이블의 WRITE를 lock을 합니다.
- METHOD : 사용할 인덱스 메서드의 이름입니다.
B-Tree,hash,GiST,SP-GiST,GIN,BRIN중에 하나의 값을 가질 수 있습니다.(defualt B-Tree) - [ASC/DESC] : 해당 인덱스에 저장할 칼럼을 나열하고 정렬할 순서를 지정합니다.(default : ASC)
- [NULLS { FIRST | LAST } ] : 이 옵션을 사용하여 NULLS를 NULL이 아닌 값 앞에 배치할 것인지 나중에 배치할 것인지 정의합니다.
-
3.2. DROP INDEX
-
- 인덱스를 제거하려면 인덱스의 소유자여야 합니다.
- DROP INDEX의 시놉시스는 다음과 같습니다.
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]-
-
- CONCURRENTLY : 인덱스가 있는 테이블에 SELECT, INSERT, UPDATE 그리고 DELETE 에 대한 locking 없이 인덱스를 삭제합니다. 일반 DROP INDEX 는 테이블에 대한 lock을 얻은 후, 그 DROP INDEX 명령이 완료될 때까지 다른 액세스를 차단합니다. 그리고 만약 CONCURRENTLY 옵션을 사용하면 충돌한 트랜잭션이 완료될때까지 커맨드는 기다립니다.
- CASCADE : 자동적으로 이 인덱스에 종속되어있는 오브젝트들을 삭제합니다.
- RESTRICT : 인덱스에 종속된 오브젝트가 있는 경우 인덱스를 삭제하지 않습니다.
-
4. Single-column Indexes
4.1. Single-column Indexes란?
-
- 하나의 테이블 칼럼만을 기반으로 생성되는 인덱스 입니다.
- 쿼리의 WHERE 절에서 필터 조건으로 자주 사용하는 칼럼을 고려하여 생성합니다.
- 다음은 생성 구문입니다.
CREATE INDEX index_name ON table_name (column_name);5. Multi-column Indexes
5.1. Multi-column Indexes란?
-
- 둘 이상의 테이블 컬럼에 인덱스를 만들 수 있습니다.
- 이 경우 32개의 컬럼으로 제한됩니다.
- 제한은 pg_config_manual.h 에서 변경할 수 있습니다.
- B-Tree, GIN, GiST 및 BRIN 타입만 멀티 칼럼 인덱스를 지원합니다.
- 다음은 생성 구문입니다.
CREATE INDEX index_name ON table_name (column1_name, column2_name);5.2. Multi-column Indexes 예시
-
- 테스트 데이터 테이블 생성 후 10000개의 데이터 입력
CREATE TABLE people(
id INT GENERATED BY DEFAULT AS IDENTITY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);-
- last_name이 Adams인 사람을 SELECT 하는 쿼리 실행
last_name에 정의된 인덱스가 없었기 때문에 순차 스캔을 진행하였습니다.
test=# EXPLAIN ANALYZE
test-# SELECT
test-# *
test-# FROM
test-# people
test-# WHERE
test-# last_name = 'Adams';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on people (cost=0.00..186.00 rows=18 width=17) (actual time=0.034..0.849 rows=13 loops=1)
Filter: ((last_name)::text = 'Adams'::text)
Rows Removed by Filter: 9987
Planning Time: 0.140 ms
Execution Time: 0.888 ms
(5 rows)-
- last_name과 first_name 컬럼에 인덱스 정의
first_name으로 검색하는 것 보다 last_name을 기준으로 사람을 검색하는 빈도가 더 높다고 가정하고 인덱스를 정의합니다.
CREATE INDEX idx_people_names ON people (last_name, first_name);-
- last_name이 Adams인 사람을 SELECT 하는 쿼리 재실행
test=# EXPLAIN ANALYZE
SELECT
*
FROM
people
WHERE
last_name = 'Adams';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on people (cost=4.42..44.07 rows=18 width=17) (actual time=0.032..0.055 rows=13 loops=1)
Recheck Cond: ((last_name)::text = 'Adams'::text)
Heap Blocks: exact=12
-> Bitmap Index Scan on idx_people_names (cost=0.00..4.42 rows=18 width=0) (actual time=0.024..0.024 rows=13 loops=1)
Index Cond: ((last_name)::text = 'Adams'::text)
Planning Time: 0.084 ms
Execution Time: 0.086 ms
(7 rows)-
- first_name이 Lou 이고, last_name이 Adams인 사람을 SELECT 하는 쿼리 실행
WHERE 절의 두 컬럼이 모두 인덱스에 있기 때문에 인덱스를 사용하여 스캔하였습니다.
test=# EXPLAIN ANALYZE
test-# SELECT
test-# *
test-# FROM
test-# people
test-# WHERE
test-# last_name = 'Adams'
test-# AND first_name = 'Lou';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_people_names on people (cost=0.29..8.30 rows=1 width=17) (actual time=0.025..0.027 rows=1 loops=1)
Index Cond: (((last_name)::text = 'Adams'::text) AND ((first_name)::text = 'Lou'::text))
Planning Time: 0.099 ms
Execution Time: 0.048 ms
(4 rows)-
- first_name이 Lou인 사람을 SELECT 하는 쿼리 실행
first_name이 인덱스에 포함되어 있는 컬럼이지만 순차 스캔을 사용하였습니다.
인덱스를 정의할 때 조회에 자주 사용되는 컬럼을 찾고 컬럼 목록의 시작 부분에 배치를 해야 인덱스를 활용해 스캔할 수 있습니다.
test=# EXPLAIN ANALYZE
test-# SELECT
test-# *
test-# FROM
test-# people
test-# WHERE
test-# first_name = 'Lou';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on people (cost=0.00..186.00 rows=32 width=17) (actual time=0.028..0.786 rows=32 loops=1)
Filter: ((first_name)::text = 'Lou'::text)
Rows Removed by Filter: 9968
Planning Time: 0.082 ms
Execution Time: 0.825 ms
(5 rows)6. Unique Index
6.1. Unique Index란?
-
- Unique Index를 사용하면 테이블에서 값이 동일한 행이 하나이상 가질 수 없습니다.
- 이 인덱스 타입은 데이터 무결성과 고성능을 유지할 때 매우 유용합니다.
- Unique Index 생성구문은 다음과 같습니다.
CREATE UNIQUE INDEX index_name on table_name (column_name);
CREATE UNIQUE INDEX index_name
on table_name(column_name1, column_name2);6.2. Unique Index 예시
6.2.1. 테스트 데이터 테이블 생성
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
);-
-
- 인덱스 조회
-
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'employees';
tablename | indexname | indexdef
-----------+---------------------+----------------------------------------------------------------------------------
employees | employees_pkey | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (employee_id)
employees | employees_email_key | CREATE UNIQUE INDEX employees_email_key ON public.employees USING btree (email)
(2 rows)6.2.2. Unique Index – Single-column
-
-
- mobile-phone 컬럼 추가
-
ALTER TABLE employees ADD mobile_phone VARCHAR(20);-
-
- mobile-phone 컬럼에 unique 인덱스 정의
-
CREATE UNIQUE INDEX idx_employees_mobile_phone ON employees(mobile_phone);-
-
- 데이터 추가 후 같은 전화번호로 다른 데이터 입력 시도
-
test=# INSERT INTO employees(first_name, last_name, email, mobile_phone)
test-# VALUES ('John','Doe','john.doe@postgresqltutorial.com', '(408)-555-1234');
INSERT 0 1
test=# INSERT INTO employees(first_name, last_name, email, mobile_phone)
test-# VALUES ('Mary','Jane','mary.jane@postgresqltutorial.com', '(408)-555-1234');
ERROR: duplicate key value violates unique constraint "idx_employees_mobile_phone"
DETAIL: Key (mobile_phone)=((408)-555-1234) already exists.6.2.3. Unique Index – multi-column
-
-
- work_phone, extension 컬럼 추가
-
ALTER TABLE employees
ADD work_phone VARCHAR(20),
ADD extension VARCHAR(5);-
-
- work_phone, extension 컬럼에 Unique 인덱스 정의
-
여러 직원이 같은 직장 전화번호를 사용할 수 있지만, 내선번호는 각각 달라야 합니다.
CREATE UNIQUE INDEX idx_employees_workphone ON employees(work_phone, extension);-
-
- 테스트 데이터 추가
-
test=# INSERT INTO employees(first_name, last_name, work_phone, extension)
test-# VALUES('Lily', 'Bush', '(408)-333-1234','1212');
INSERT 0 1
test=# INSERT INTO employees(first_name, last_name, work_phone, extension)
test-# VALUES('Joan', 'Doe', '(408)-333-1234','1211');
INSERT 0 1
test=# INSERT INTO employees(first_name, last_name, work_phone, extension)
test-# VALUES('Tommy', 'Stark', '(408)-333-1234','1211');
ERROR: duplicate key value violates unique constraint "idx_employees_workphone"
DETAIL: Key (work_phone, extension)=((408)-333-1234, 1211) already exists.7. Partial Index
7.1. Partial Index 란?
-
- Partial 인덱스는 WHERE 절이 있는 인덱스로, 테이블에 있는 테이터의 특정 부분 집합을 포함합니다.
- 크기가 작고, 동작 속도가 빠르며, 더 복잡한 쿼리에서는 다른 인덱스들과 함께 사용할 수 있습니다.
- 다음은 Partial Index의 생성 구문입니다.
CREATE INDEX index_name on table_name (conditional_expression);7.2. Partial Index 예시
-
- 테스트 할 데이터 테이블
dvdrental=# d customer
Table "public.customer"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
customer_id | integer | | not null | nextval('customer_customer_id_seq'::regclass)
store_id | smallint | | not null |
first_name | character varying(45) | | not null |
last_name | character varying(45) | | not null |
email | character varying(50) | | |
address_id | smallint | | not null |
activebool | boolean | | not null | true
create_date | date | | not null | ('now'::text)::date
last_update | timestamp without time zone | | | now()
active | integer | | |-
- 모든 unactive 고객을 SELECT 하는 쿼리
dvdrental=# select customer_id,first_name,last_name from customer where active =0;
customer_id | first_name | last_name
-------------+------------+-----------
16 | Sandra | Martin
64 | Judith | Cox
124 | Sheila | Wells
169 | Erica | Matthews
241 | Heidi | Larson
271 | Penny | Neal
315 | Kenneth | Gooden
368 | Harry | Arce
406 | Nathan | Runyon
446 | Theodore | Culp
482 | Maurice | Crawley
510 | Ben | Easter
534 | Christian | Jung
558 | Jimmie | Eggleston
592 | Terrance | Roush
(15 rows)
dvdrental=# explain analyze select customer_id,first_name,last_name from customer where active =0;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=17) (actual time=0.014..0.089 rows=15 loops=1)
Filter: (active = 0)
Rows Removed by Filter: 584
Planning Time: 0.105 ms
Execution Time: 0.131 ms
(5 rows)-
- active 컬럼에 인덱스 생성
CREATE INDEX idx_customer_inactive ON customer(active)
WHERE active = 0;-
- 모든 unactive 고객을 SELECT 하는 쿼리 재실행
dvdrental=# explain analyze select customer_id,first_name,last_name from customer where active =0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_customer_inactive on customer (cost=0.14..16.12 rows=15 width=17) (actual time=0.016..0.037 rows=15 loops=1)
Planning Time: 0.092 ms
Execution Time: 0.062 ms
(3 rows)8. Expression Indexes
8.1. Expression Indexes 란?
-
- Expression 인덱스는 함수 또는 데이터 수정과 일치하는 쿼리를 의미합니다.
- 함수의 결과를 인덱싱하고 로우 데이터 값에 의한 검색 만큼 효율적으로 검색할 수 있습니다.
- Expression 인덱스의 생성구문은 다음과 같습니다.
CREATE INDEX index_name ON table_name (expression);8.2. Expression Indexes 예시
Partial Index와 같은 테스트 데이터 테이블로 진행합니다.
-
- last_name이 Purdy 인 고객을 SELECT 하는 쿼리 진행
dvdrental=# EXPLAIN ANALYZE
SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
last_name = 'Purdy';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_last_name on customer (cost=0.28..8.29 rows=1 width=17) (actual time=0.040..0.043 rows=1 loops=1)
Index Cond: ((last_name)::text = 'Purdy'::text)
Planning Time: 0.087 ms
Execution Time: 0.065 ms
(4 rows)-
- lower 함수를 이용해 last_name이 purdy 인 고객을 SELECT 하는 쿼리를 수행
바로 위 쿼리를 진행할 때 사용한 인덱스를 사용할 수 없습니다.
dvdrental=# EXPLAIN ANALYZE
SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
LOWER(last_name) = 'purdy';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on customer (cost=0.00..17.98 rows=3 width=17) (actual time=0.143..0.229 rows=1 loops=1)
Filter: (lower((last_name)::text) = 'purdy'::text)
Rows Removed by Filter: 598
Planning Time: 0.098 ms
Execution Time: 0.251 ms
(5 rows)-
- LOWER 함수 표현식을 이용한 인덱스 생성
CREATE INDEX idx_ic_last_name ON customer(LOWER(last_name));-
- lower 함수를 이용해 last_name이 purdy 인 고객을 SELECT 하는 쿼리 재 수행
실행계획이 bitmap index 스캔을 이용하는 것으로 바뀌고 실제 수행시간이 줄어든 것을 확인합니다.
dvdrental=# EXPLAIN ANALYZE
SELECT
customer_id,
first_name,
last_name
FROM
customer
WHERE
LOWER(last_name) = 'purdy';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customer (cost=4.30..11.15 rows=3 width=17) (actual time=0.028..0.031 rows=1 loops=1)
Recheck Cond: (lower((last_name)::text) = 'purdy'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_ic_last_name (cost=0.00..4.30 rows=3 width=0) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: (lower((last_name)::text) = 'purdy'::text)
Planning Time: 0.093 ms
Execution Time: 0.057 ms
(7 rows)9. 기타
-
- 크기가 작은 테이블이거나 UPDATE와 INSERT 작업이 크고 자주 일어나는 테이블에는 인덱스를 사용하지 말아야합니다.
- NULL값이 많거나, 자주 편집되는 칼럼에는 인덱스를 사용하지 않아야 합니다.
