[PostgreSQL] ASC 인덱스가 있는데 DESC 인덱스를 추가해도 될까?

PostgreSQL에서 특정 컬럼에 이미 ASC(오름차순) 인덱스가 존재하는 경우, DESC(내림차순) 인덱스를 추가로 생성해도 되는지 궁금했습니다. 운영중인 서비스에서 쿼리 성능 및 API 성능이 떨어지는 이슈가 발생해서 개선점을 찾는 중이었습니다. 최신 데이터를 빈번하게 조회하는 해당 서비스 및 테이블 특성 상 고려할 사항을 정리했습니다. 이번 포스트에서는 ASC 인덱스가 이미 존재할 때 DESC 인덱스를 추가로 생성해야 할지와 실행 계획을 통해 성능을 분석하는 방법을 알아보겠습니다.

1. PostgreSQL에서 ASC와 DESC 인덱스의 관계

PostgreSQL의 B-Tree 인덱스는 기본적으로 오름차순(ASC)과 내림차순(DESC)을 모두 지원합니다. 하지만 ORDER BY event_time DESC와 같은 내림차순 정렬을 자주 사용한다면, DESC 인덱스를 별도로 생성하면 성능이 개선될 수 있습니다.

언제 DESC 인덱스가 유용할까?

1. 내림차순 정렬이 빈번할 때
   • ORDER BY event_time DESC가 자주 사용되는 경우, DESC 전용 인덱스를 생성하면 성능이 개선될 수 있습니다.
2. 최근 데이터 조회가 잦을 때
   • 예: 최신 이벤트를 가져오는 쿼리 
   • SELECT * FROM events ORDER BY event_time DESC LIMIT 10;
   • ASC 인덱스를 뒤집어 사용하는 것보다 DESC 인덱스를 통해 바로 가져오는 것이 효율적일 수 있습니다.
3. 다중 컬럼 인덱스에서 정렬 방향이 중요한 경우
   • 예: (event_type, event_time) 인덱스가 있는 경우, event_time을 내림차순으로 자주 조회하면 (event_type, event_time DESC) 인덱스를 고려할 수 있습니다.

2. DESC 인덱스 생성 방법

event_time 컬럼에 DESC 인덱스를 생성하려면 다음 명령어를 실행하면 됩니다.

CREATE INDEX idx_event_time_desc ON events (event_time DESC);

이제 PostgreSQL은 ORDER BY event_time DESC를 효율적으로 처리할 수 있습니다.

3. 성능 확인: 실행 계획(EXPLAIN) 분석

인덱스가 쿼리 성능에 미치는 영향을 확인하려면 EXPLAIN과 EXPLAIN ANALYZE 명령어를 사용합니다.

1) EXPLAIN: 실행 계획만 확인

EXPLAIN SELECT * FROM events ORDER BY event_time DESC LIMIT 10;

결과 예시

Limit  (cost=0.42..2.57 rows=10 width=52)
  ->  Index Scan Backward using idx_event_time_asc on events  (cost=0.42..25.21 rows=100 width=52)

• Index Scan Backward는 ASC 인덱스를 뒤집어 내림차순으로 정렬한다는 의미입니다.

2) EXPLAIN ANALYZE: 실제 실행 시간 확인

EXPLAIN ANALYZE SELECT * FROM events ORDER BY event_time DESC LIMIT 10;

결과 예시 (DESC 인덱스가 없는 경우)

Limit  (cost=0.42..2.57 rows=10 width=52) (actual time=0.023..0.052 rows=10 loops=1)
  ->  Index Scan Backward using idx_event_time_asc on events  (cost=0.42..25.21 rows=100 width=52) (actual time=0.021..0.048 rows=10 loops=1)

3) EXPLAIN ANALYZE 결과 비교 (DESC 인덱스를 추가한 경우)

DESC 인덱스를 생성하고 동일한 쿼리를 다시 실행하면 다음과 같은 결과를 기대할 수 있습니다.

Limit  (cost=0.29..1.52 rows=10 width=52) (actual time=0.012..0.030 rows=10 loops=1)
  ->  Index Scan using idx_event_time_desc on events  (cost=0.29..14.25 rows=100 width=52) (actual time=0.010..0.025 rows=10 loops=1)

실행 계획 비교 포인트

• 인덱스 종류: Index Scan Backward → Index Scan using idx_event_time_desc
• 실행 시간: 0.052ms → 0.030ms로 개선됨

이처럼 내림차순 인덱스를 생성하면 정렬 비용이 줄어들고 실행 시간이 개선되는 경우가 많습니다.

4. DESC 인덱스 생성 시 주의사항

1. 쓰기 성능 저하
   • INSERT, UPDATE, DELETE 시 인덱스도 함께 갱신되므로, 불필요한 인덱스는 오히려 성능을 저하시킬 수 있습니다.
2. 디스크 공간 증가
   • 인덱스는 별도의 저장 공간을 차지합니다.
3. 인덱스 관리 필요
   • 필요 없는 인덱스는 주기적으로 삭제하는 것이 좋습니다.

     DROP INDEX IF EXISTS idx_event_time_desc;

5. 결론: DESC 인덱스를 생성할지 고민된다면?

1. 쿼리 실행 계획을 먼저 확인(EXPLAIN ANALYZE) 하세요.
2. ORDER BY event_time DESC나 최신 데이터 조회가 잦다면 DESC 인덱스를 추가하면 성능이 개선될 수 있습니다.
3. 인덱스를 생성한 후에는 성능 개선 효과를 반드시 모니터링하고, 불필요하면 제거하세요.

실제 결과

생각보다 극적인 변화는 없었습니다. 테이블 내에 데이터 양 자체가 많고, INSERT 작업이 분당 50~100 건 가량 일어나기 때문에 오히려 전반적인 쿼리 속도가 느려졌습니다. 인덱스 생성을 하여 느려졌다면 고민하지 않고 제거하는 것을 추천합니다.