[DB] PostgreSQL Partial Index로 쿼리 성능 개선하기

1. Partial Index

PostgreSQL에는 Partial Index라는 기능이 있습니다.
속히 부분 인덱스라고 불리는데, 말 그대로 특정 칼럼에 대해 조건을 걸어 해당 조건에 부합하는 행에 대해서만 인덱스를 생성할 수 있습니다.

전체 테이블에 대해 생성하지 않고 부분적으로만 인덱스를 생성하기 때문에, 인덱스의 크기가 보다 감소하게 되는데요, 이를 통해 공간 효율성과 성능 향상을 기대해볼 수 있습니다.

인덱스는 전체 행에 대비해 크기가 작은 행을 찾을 때 사용하면 더욱 효과적입니다. partial index를 기반으로 조건을 통해 더 작은 크기의 인덱스를 생성한다면, 이 인덱스를 보다 더욱 효과적으로 사용할 수 있습니다.

2. 조회 쿼리 성능 개선하기

partial index를 이용해 기존에 사용하던 조회 쿼리의 성능을 개선해보겠습니다. 예제에 등장하는 테이블의 로우 개수는 약 8000개입니다.

2-1. 기존 쿼리 분석

기존 쿼리는 다음과 같습니다.

SELECT usp1_0.step_progress_id, usp1_0.completed_at, 
	usp1_0.created_at,usp1_0. duration_time, usp1_0.step_type, 
	usp1_0.updated_at, usp1_0.user_id 
FROM user_step_progress usp1_0 
WHERE usp1_0.user_id=? 
	AND usp1_0.completed_at IS NOT NULL

WHERE절에서 user_id필드에 대해 등호 연산자를 사용한 뒤, completed_at필드가 NULL이 아닌 것에 대한 필터를 수행하고 있습니다.

해당 쿼리에 대해 EXPLAIN ANALYZE 결과를 보면, WHERE절 조건을 확인하기 위해 Seq Scan을 하고 있는 것을 확인할 수 있습니다.

Seq Scan on user_step_progress usp1_0  (cost=0.00..173.11 rows=8 width=56) (actual time=0.028..1.332 rows=2 loops=1)
  Filter: ((completed_at IS NOT NULL) AND (user_id = 3))
  Rows Removed by Filter: 8007
Planning Time: 0.573 ms
Execution Time: 1.366 ms

2-2. 인덱스 추가

user_id = ? 에 대한 조건을 위해 인덱스를 추가하기 위해서는, JPA의 @Index 기능을 이용해 entity 파일에 쉽게 인덱스를 생성할 수 있습니다.

그러나, 이 포스팅의 주요 요점은 partial index입니다. JPA의 @Index는 partial index 생성을 지원하지 않기 때문에 다른 방법을 고안해야합니다.

1. db 콘솔에서 직접 SQL문을 작성해 인덱스 추가
2. SQL문을 따로 파일로 관리
   2-1. flyway 이용해 마이그레이션 버전 관리
   2-2. 매번 init

1번 방법의 경우 SQL문 관리가 힘들기 때문에 저는 2번 방법을 택했습니다.

flyway는 db 마이그레이션 버전 관리를 돕는 오픈소스 툴입니다. 해당 툴을 이용해 SQL문을 관리하고자 했으나, postgresql 17.5버전을 아직 지원하지 않아 사용할 수 없었습니다. 추후 기회가 된다면, 해당 방법을 적용해볼 예정입니다.

결과적으로, SQL문을 파일로 관리하되, 프로그램 실행 시 SQL문을 init하는 방법을 택했습니다. 운영보다는 실습이 중심인 프로젝트이기 때문에, 우선은 버전 관리를 크게 고려하지 않았습니다.

application.yml에 sql.init.mode 옵션을 alway로 변경합니다.

spring:
  sql:
    init:
      platform: postgresql
      mode: always

resources 폴더에 schema.sql 이라는 파일을 두고 SQL문을 작성했습니다. 애플리케이션을 실행하면 해당 파일에 작성된 SQL문이 실행되어 DB에 적용됩니다.

user_id 필드에 대한 인덱스와 더불어, completed_at 필드가 null이 아닌 조건을 추가로 걸어 partial index를 생성했습니다.

/**
  Courses
 */
CREATE INDEX IF NOT EXISTS idx_user_step_progress_user_completed_partial
    ON user_step_progress(user_id)
    WHERE completed_at IS NOT NULL;

2-3.쿼리 재분석

인덱스를 추가한 이후, 쿼리의 실행 계획을 다시 살펴보았습니다.

기존에 조건절을 살펴보기 위해 테이블을 순차적으로 스캔했던 것에 비해, 생성한 인덱스를 이용해 Index Scan을 하는 것을 확인할 수 있습니다.

Index Scan using idx_user_completed_partial on user_step_progress usp1_0  (cost=0.13..12.27 rows=8 width=56) (actual time=0.047..0.048 rows=2 loops=1)
  Index Cond: (user_id = 3)
Planning Time: 0.287 ms
Execution Time: 0.073 ms

실행 시간도 기존 실행 시간에 비해 18배나 감소했습니다!

2-4. 부분 인덱스가 아니라면?

2-3의 결과는 where절 조건에 딱 부합하는 partial index를 이용해 나온 최적의 결과입니다. partial index가 아닌 user_id, completed_at 칼럼에 대한 일반적인 인덱스를 활용하면 성능이 얼마나 개선될지 확인해봅시다.

다음과 같은 인덱스를 생성해주고 실행 계획을 살펴보았습니다.

CREATE INDEX user_step_progress_user_id_completed_at_index
	ON user_step_progress(user_id, completed_at);

마찬가지로 생성한 인덱스를 사용해 Index Scan을 하고 있기는 하나, condition 부분이 조금 다른 것을 확인할 수 있습니다.

Index Scan using user_step_progress_user_id_completed_at_index on user_step_progress usp1_0  (cost=0.28..20.69 rows=8 width=56) (actual time=0.553..0.557 rows=2 loops=1)
  Index Cond: ((user_id = 3) AND (completed_at IS NOT NULL))
Planning Time: 1.645 ms
Execution Time: 0.588 ms

또한 기존 실행 시간보다 약 2.3배 성능이 개선되었으나, partial index를 이용한 것의 결과에 비하면 약 8배 정도 성능이 더딘 것을 알 수 있습니다.

확연히 일반 인덱스에 비해 partial index를 잘 이용하면 성능을 엄청나게 향상시킬 수 있습니다. 다만, 과도하고 적절하지 않은 인덱스 이용은 오히려 성능 저하를 불러올 수도 있으니, 상황에 따라 지표를 확인해가며 적절한 인덱스 전략을 이용하는 것이 좋습니다.