PostgreSQL: 인덱스 - 연산자 클래스

※ Operator Class for Indexes in PostgreSQL.

 

안녕하세요. 듀스트림입니다.

 

운영 환경에서는 복잡한 비즈니스 로직이 들어간 SQL을 즉시 최적화해서 반영하기 어려울 때가 많습니다.

그다음으로 효과적인 방법은 인덱스를 통한 데이터 액세스 최적화입니다.

 

그래서 우리는 인덱스에 대해 잘 알아야합니다.

 

오늘 포스팅은 PostgreSQL의 Index Operator Class에 대한 내용입니다.

 

여담으로 MySQL과 비교해 보자면, MySQL에는 PostgreSQL의 연산자 클래스와 같은 개념이 없습니다.
대신 인덱스 생성 시 데이터 타입 + 인덱스 종류 + Collation 조합으로 동작 방식을 결정하며, 별도의 연산자 집합을 따로 정의하는 구조가 아닙니다.

 

+ 메커니즘 비교표

구분	PostgreSQL	MySQL
개념	• Operator Class / Operator Family인덱스가 어떤 연산자(=, <, >, LIKE 등)와 어떤 비교 함수를 지원할지 정의	• 별도의 operator class 개념 없음데이터 타입 + 인덱스 종류(BTREE/HASH/…)+ Collation 조합으로 동작 방식 결정
세부 제어	• 같은 데이터 타입이라도 다른 opclass 선택 가능예: btree에서 text_ops, text_pattern_ops • 특정 연산자 패턴 최적화 가능	• 데이터 타입과 Collation에 따라 정렬/비교 규칙 자동 결정 • 인덱스 생성 시 부분 인덱스(prefix), Fulltext, Spatial 등 선택 가능
연산자 집합	• 인덱스 접근 메소드(BTREE, GiST, GIN 등)마다 지원 연산자 집합 정의	• 연산자 집합을 따로 정의하지 않음데이터 타입에 맞는 기본 연산자(=, <, LIKE 등)만 사용
사용자 확장	• 사용자가 새로운 opclass/opfamily 생성 가능 → 커스텀 인덱스 연산 정의	• 사용자 정의 불가 • MySQL 엔진 내부에서 지원하는 인덱스/Collation에만 의존
예시	CREATE INDEX idx_txt ON tbl (col text_pattern_ops);	CREATE INDEX idx_txt ON tbl (col(20)) COLLATE utf8mb4_bin;

---

1. Operator Class?

Operator Class(연산자 클래스)란, PostgreSQL에서 인덱스가 데이터를 어떻게 비교하고 정렬할지 정의하는 규칙입니다.

• 인덱스 = 검색을 빠르게 하기 위한 데이터 구조
• 데이터를 정렬하거나 비교해야 할 때 어떤 연산자를 사용할지를 Operator Class가 정의
• 인덱스 종류(B-Tree, GIN, GiST)에 따라 Operator Class가 달라짐

인덱스를 사전이라고 생각하면 Operator Class는 사전을 어떤 기준으로 정렬할지 결정하는 규칙과 같습니다. (가나다 순? 알파벳 순? 접두어 기준? 등)

---

2. Operator Class의 구조

PostgreSQL에서 Operator Class는 크게 3가지 요소로 구성됩니다.

Operator Class = 자료형 + 비교 연산자 + 인덱스 활용 규칙

요소	설명
자료형	인덱스를 적용할 컬럼의 데이터 타입
인덱스 연산자	데이터를 비교할 때 사용하는 연산자 집합 (<, <=, =, >=, >)
지원 인덱스 메서드	B-Tree, GIN, GiST 등 어떤 인덱스에서 사용할지 정의

---

3. Operator Class 기본값

자료형	인덱스 타입	기본 Operator Class
text	B-Tree	text_ops
varchar	B-Tree	varchar_ops
integer	B-Tree	int4_ops
bigint	B-Tree	int8_ops
date	B-Tree	date_ops

---

4. pg_opclass 테이블

PostgreSQL에서 Operator Class 메타데이터는 pg_opclass 테이블에 저장됩니다.

SELECT opcname, opcintype::regtype, opcmethod
FROM pg_opclass
--WHERE opcintype = 'text'::regtype
;

결과 값이 아래로 더 많습니다.

+ opcmethod 컬럼 숫자의 의미
opcmethod 컬럼은 Operator Class가 사용할 인덱스 메서드(Index Access Method, AM)의 OID입니다.

SELECT oid, amname FROM pg_am;

---

5. Operator Class와 활용 예시

▸ B-Tree

-- 기본값: text_ops
CREATE INDEX idx_text ON users(email);

-- LIKE 검색 최적화
CREATE INDEX idx_text_like ON users(email text_pattern_ops);

 

▸ GIN

-- gin_tsvector_ops
CREATE INDEX idx_tsvector ON documents USING GIN(to_tsvector('english', content));

 

▸ GIST

CREATE INDEX idx_geom ON locations USING GiST(geom gist_geometry_ops);

 

▸ Bloom

-- text_bloom_ops
CREATE INDEX idx_email_bloom ON users USING bloom(email);

---

6. Operator Class 활용 팁

• LIKE 검색이 많은 컬럼 → text_pattern_ops
• 숫자/날짜 범위 검색 → 기본 B-Tree
• Full-text 검색 → GIN + gin_tsvector_ops
• 공간/범위 데이터 → GiST + 맞는 Operator Class
• Bloom 인덱스 → 멀티컬럼 문자열 비교 시 유리

---

7. 정리

적절한 Operator Class 선택만으로도 쿼리 성능과 디스크 공간 효율을 크게 개선할 수 있습니다.

• Operator Class = 자료형 + 비교 연산자 + 인덱스 메서드 규칙
• B-Tree, GIN, GiST, Bloom 등 인덱스별로 적합한 Operator Class 존재
• pg_opclass와 pg_am을 통해 메타데이터 확인 가능
• opcmethod 숫자는 인덱스 메서드 OID를 나타냄
• 자료형과 쿼리 패턴에 맞는 Operator Class를 선택하는 것이 성능 최적화의 핵심

---

오늘은 여기까지~