PostgreSQL과 Index

조회

PostgreSQL은 스키마의 인덱스를 pg_indexes 테이블로 관리한다.

Column	Type	Collation	Nullable	Default	Storage	Description
schemaname	name				plain
tablename	name				plain
indexname	name				plain
tablespace	name				plain
indexdef	text				extended

SELECT * FROM pg_indexes;

생성

CREATE INDEX users_name_age ON Users(name, age);

Users 테이블의 name, age 컬럼에 대한 users_name_age라는 인덱스를 생성한다.

별도로 인덱스 타입을 지정하지 않으면, 기본적으로 B-Tree기반 인덱스를 생성한다.

hash, GIN, GiST, SP-GiST등의 Index가 존재함 (하단에서 이어서 설명)

삭제

DROP INDEX SCHEMA_NAME.INDEX_NAME;

업데이트

데이터가 삭제, 추가되면 인덱스의 정확도가 점점 떨어질 것이다.

변경된 데이터 상황에 맞게 인덱스를 업데이트 해줘야 한다.

// Postgresql 방식
REINDEX INDEX index_name;
// 인덱스의 statistic 정보만 업데이트 -> 인덱스 구조가 변경되는게 아님
ANALYZE table_name(index_name);

참고

데이터 개수가 늘어날 수록 인덱스 생성, 삭제, 업데이트는 비용이 많이 소모된다는 점을 항상 염두해야 한다.

Index 종류

성능 비교

PostgreSQL에서는 \timing과 EXPLAIN ANALYZE를 통해 간단히 실행 시간을 확인할 수 있다.

INDEX 적용 이전

\timing
SELECT * FROM Users WHERE name =

INDEX 적용 후

Index가 사용이 안 돼요

문제없이 인덱스를 만들었는데도 Index를 타지 않는다.

Query Optimizer가 Index를 타는게 오히려 성능이 안 좋다고 판단했기 때문이다.

그럼에도 인덱스를 사용하고 싶으면 PostgreSQL의 경우, Query Optimizer가 Index를 타도록 다음과 같이 힌트를 제공할 수 있다.

// 
SELECT /*+ INDEX(user_name_age_idx) */ * FROM Users WHERE name = 'hi' AND age = 25;

SELECT 절에 /*+ INDEX(인덱스_이름) */형식으로 힌트를 줄 수 있다.

그런데 힌트를 제공한다고 해서 무조건 인덱스를 사용하는건 아니다.

내가 아무리 인덱스 사용하라고 해도, 쿼리 옵티마이저가 성능상 이건 아닌데...하면 인덱스를 사용하지 않는다.