[Postgresql] 복합인덱스에 관해서

🗂️ 복합인덱스

복합인덱스 말 그대로 DB의 여러 개의 컬럼을 조합하여 인덱스로 생성하는 것

최근 회사에서 실시간으로 여러 기지국에서 5분단위로 들어오는 데이터를 조회하는 쿼리를 작성하게 되었다.

기지국의 데이터 양은 엄청나게 많기 때문에(5분 단위로 insert 되는 row가 약 40만건이 된다.)

인덱스를 필수로 작성해야되었고 조회되는 컬럼이 다양하고 카디널리티가 낮다 보니 단일인덱스가 아닌 복합인덱스 작업이 필수였다.

예시
CREATE TABLE base_station_data
(
  id          INT AUTO_INCREMENT PRIMARY KEY,
  create_date DATE         NOT NULL, --5분단위 데이터 날짜 예시 : 2025-05-18
  create_time TIME         NOT NULL, --5분단위 데이터 시간 예시 : 15:25:00
  region_name VARCHAR(100) NOT NULL, --조회 장소 이름
  region_code VARCHAR(10)  NOT NULL, --조회 장소 코드
  ...                                --그 외
);

회사의 DBA분께서는 (create_date, create_time, region_code) 로 된 복합인덱스를 만들어주셨다고 했다.

하지만 내 생각에 아무리 생각해봐도 region_code를 사용할 일은 없다고 생각되어서

조회쿼리가 최종적으로는 create_date, create_time, region_name 을 where 절로 조회하게 되는 조건문을 만들었다.

---

🧠 성능?

데이터가 5분단위로 약 40만건이 들어오는 db에서 장소명(장소 개수는 120이다.)으로 데이터를 거르게 된다면 대략 한 장소당 3300개의 데이터가 들어오게 된다.

내 생각에는 3300개의 데이터 정도는 조회가 어렵지 않으니 단순하게 만든 복합인덱스에서 (create_date, create_time) 까지만 조회해도 상관없을 것이라고 생각했고

결과적으로 한 요청당 10초 이상 대략 20초 가까이의 절망적인 성능을 보여주게 되었다.

---

🧐 성능의 원인

EXPLAIN으로 실행 계획을 보지 않아도 분명했다.

region_name을 조건에 넣은 쿼리는 인덱스 (create_date, create_time, region_code) 순서와 맞지 않아 인덱스를 타지 못하고 전체 테이블을 Full Scan하게 되었다.

PostgreSQL에서는 복합 인덱스를 사용할 경우, 인덱스 컬럼의 순서에 따라 조건이 인식된다.

WHERE create_date = ‘2025-05-18’ AND create_time = ‘15:00:00’ AND region_name = ‘서울’처럼 조건을 준다면 region_name이 인덱스에 없거나 순서가 맞지 않으면 인덱스를 사용할 수 없게 되었다.

현재의 복합인덱스

create_date	create_time	region_code
2025-05-18	15:00:00	0000001
2025-05-18	15:00:00	0000002
…	…	…

결국, 내가 쿼리에서 사용한 region_name은 복합 인덱스의 순서에 없었고, 매 요청마다 테이블내의 40만건을 전부 Full Scan 하는 상황이 발생했다.

결과적으로 DBA 분에게 말씀을 드리고 기존 복합인덱스 구조(create_date, create_time, region_code)에서 (create_date, create_time, region_name)으로 인덱스를 수정했다.(인덱스를 수정하는데만 1시간이 넘게 걸렸다..)

---

🧪 결과

위 인덱스를 적용하고 나서 실제 서비스에 반영된 쿼리 응답 속도는 다음과 같이 개선되었다.

조건	속도	데이터양
create_date + create_time + region_code	15~20 s	약 400000건
create_date + create_time + region_name	1s 이내	약 400000건