PostgreSQL 복합 인덱스(Composite Index) 완벽 가이드

1. 복합 인덱스(Composite Index)란?

복합 인덱스(Composite Index)는 두 개 이상의 컬럼(Column)을 결합하여 생성한 인덱스입니다. 일반적으로 검색, 정렬, 조인 등의 성능 최적화를 위해 사용됩니다.

예를 들어, users 테이블에서 (last_name, first_name)에 대한 복합 인덱스를 생성하면 다음과 같이 작동합니다.

CREATE INDEX idx_users_last_first ON users (last_name, first_name);

이제 last_name을 먼저 필터링한 후, 같은 last_name을 가진 first_name을 정렬하는 방식으로 조회 성능이 향상됩니다.

---

2. 단일 인덱스 vs 복합 인덱스

(1) 단일 인덱스

단일 인덱스는 하나의 컬럼에만 적용됩니다.

CREATE INDEX idx_users_last ON users (last_name);
CREATE INDEX idx_users_first ON users (first_name);

단일 인덱스가 두 개 존재하는 경우, 두 개의 인덱스를 조합하여 사용(Index Merge)할 수도 있지만, 경우에 따라 효율이 떨어질 수 있습니다.

(2) 복합 인덱스

CREATE INDEX idx_users_last_first ON users (last_name, first_name);

복합 인덱스는 last_name을 기준으로 먼저 정렬한 후, 동일한 last_name을 가진 first_name을 기준으로 정렬하여 검색 속도를 더욱 최적화할 수 있습니다.

---

3. 복합 인덱스의 컬럼 순서가 중요한 이유

복합 인덱스는 선언된 순서대로 사용됩니다.

인덱스	사용 가능	설명
WHERE last_name = 'Kim' AND first_name = 'Minho'	✅	last_name을 먼저 필터링한 후 first_name을 검색
WHERE last_name = 'Kim'	✅	last_name 필터링 가능
WHERE first_name = 'Minho'	❌	last_name이 먼저 인덱스에 포함되어 있어 first_name만으로는 최적화되지 않음
ORDER BY last_name, first_name	✅	정렬 시 인덱스 활용 가능
ORDER BY first_name, last_name	❌	인덱스 순서와 다르므로 정렬에 사용 불가

즉, 선언된 첫 번째 컬럼을 반드시 검색 조건에 포함해야 인덱스를 활용할 수 있습니다.

따라서, 복합 인덱스를 설계할 때는 조회 패턴을 고려하여 컬럼 순서를 결정해야 합니다.

---

4. 복합 인덱스의 활용 사례

(1) 다중 컬럼 검색 최적화

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

다음과 같은 쿼리에 인덱스가 효율적으로 사용됩니다.

SELECT * FROM orders WHERE status = 'shipped' AND order_date >= '2024-01-01';

하지만, 다음과 같은 쿼리는 부분적으로만 인덱스를 활용할 수 있습니다.

SELECT * FROM orders WHERE order_date >= '2024-01-01';

이 경우, status가 첫 번째 컬럼이므로 order_date만 단독으로 조회할 때는 인덱스를 활용하지 못할 가능성이 큽니다.

(2) GROUP BY, ORDER BY 최적화

CREATE INDEX idx_sales_region_date ON sales (region, sale_date);

다음과 같은 GROUP BY 또는 ORDER BY 쿼리는 인덱스를 효율적으로 활용할 수 있습니다.

SELECT region, SUM(amount) FROM sales GROUP BY region ORDER BY region;

---

5. 복합 인덱스의 단점 및 주의사항

(1) 인덱스 크기 증가

• 여러 개의 컬럼을 포함하면 인덱스 크기가 증가하여 성능 저하 가능성이 있음.
• 너무 많은 컬럼을 포함하는 인덱스는 쓰기(INSERT, UPDATE, DELETE) 성능을 저하시킬 수 있음.

(2) 컬럼 순서에 따른 인덱스 활용 제한

• 첫 번째 컬럼이 필터링되지 않으면 인덱스를 사용하지 못할 수도 있음.
• ORDER BY 순서가 인덱스 순서와 다르면 정렬 최적화가 어렵다.

(3) 지나치게 많은 인덱스 생성 금지

• 인덱스가 많아지면 INSERT, UPDATE, DELETE 성능이 저하됨.
• 불필요한 인덱스는 pg_stat_user_indexes 뷰를 활용하여 삭제를 고려할 것.

SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

---

6. 복합 인덱스 vs 다중 단일 인덱스

구분	장점	단점
단일 인덱스 여러 개	- 여러 개의 인덱스를 조합하여 사용할 수 있음- 개별 컬럼 검색 시 효율적	- Index Merge 시 최적화가 어렵고 경우에 따라 비용이 높음
복합 인덱스	- 특정 조회 패턴에서는 가장 빠름- ORDER BY에도 활용 가능	- 첫 번째 컬럼을 검색하지 않으면 인덱스를 사용하기 어려움

---

7. 복합 인덱스 활용을 위한 PostgreSQL 성능 분석

(1) EXPLAIN ANALYZE를 활용한 실행 계획 분석

인덱스 적용 전후로 실행 계획을 비교하여 성능을 확인합니다.

EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Kim' AND first_name = 'Minho';

인덱스가 없는 경우:

Seq Scan on users  (cost=0.00..1000.00 rows=1 width=100)

복합 인덱스를 적용한 경우:

Index Scan using idx_users_last_first on users  (cost=0.14..8.15 rows=1 width=100)

(2) pg_stat_statements로 가장 느린 쿼리 분석

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

이 정보를 기반으로 자주 실행되거나 시간이 오래 걸리는 쿼리를 복합 인덱스로 최적화할 수 있습니다.

---

8. 결론: 언제 복합 인덱스를 사용할 것인가?

✅ 사용 추천

• WHERE col1 = ? AND col2 = ? 형태의 조회가 빈번한 경우
• ORDER BY col1, col2가 자주 사용되는 경우
• GROUP BY col1, col2가 자주 사용되는 경우

❌ 사용하지 않는 것이 나은 경우

• 단일 컬럼 검색이 많은 경우 (단일 인덱스가 더 효과적)
• 컬럼 순서가 자주 변경되는 경우
• 너무 많은 컬럼을 포함하는 복합 인덱스 (쓰기 성능 저하)

---

9. 마무리

복합 인덱스는 조회 성능 최적화에 강력한 도구이지만, 무조건 좋은 것은 아닙니다.

항상 쿼리 패턴을 분석하고, 실행 계획을 확인하며, 인덱스를 전략적으로 활용하는 것이 중요합니다.