복합 인덱스

앞서 조인 순서를 바꿔 쿼리 성능이 개선되었지만, 여전히 개선의 여지가 남아있습니다.
EXPLAIN ANALYZE를 다시 보면 :

-> Nested loop inner join  (cost=307 rows=114) (actual time=0.502..5.05 rows=31 loops=1)
    -> Filter: ((journals.started_at >= TIMESTAMP'2024-06-30 15:00:00') and (journals.started_at < TIMESTAMP'2024-07-31 14:59:59.999'))  (cost=268 rows=114) (actual time=0.48..4.71 rows=55 loops=1)
        -> Index lookup on journals using FK_dcd8f26897887ea1ca19e9b910a (user_id=1)  (cost=268 rows=1025) (actual time=0.464..4.36 rows=1025 loops=1)
    -> Single-row covering index lookup on journals_dogs using PRIMARY (journal_id=journals.id, dog_id=1)  (cost=0.251 rows=1) (actual time=0.00558..0.00561 rows=0.564 loops=55)

• user_id로 인덱스 lookup을 한 뒤 started_at 조건을 확인하고 있습니다.
  이는 user_id에는 인덱스가 있지만, started_at에는 인덱스가 없어 조건에 해당하는지 확인하기 위해 물리 데이터로 왕복을 해야 함을 의미합니다.

이런 경우 user_id, started_at을 모두 포함하는 복합 인덱스를 만들면 인덱스 내에서만 조건을 처리할 수 있어 성능을 개선할 수 있습니다.
다만 인덱스를 생성함에 따르는 오버헤드도 있으므로 여러가지를 고려해서 판단해야 합니다.
현재 코드상으로 이 쿼리 말고도 user_id와 started_at을 함께 확인하는 쿼리가 많기 때문에 인덱스를 생성하는 것이 좀 더 이득이 클 것이라고 생각됩니다.
또, user_id와 started_at 인덱스에서 user_id가 왼쪽에 있으므로 user_id에 대한 단독 조건절에도 복합 인덱스를 사용 가능합니다.
복합 인덱스 추가시 user_id 단독 인덱스를 삭제할 수 있습니다.

적용하기

Raw 쿼리로는 다음과 같이 복합 인덱스를 추가할 수 있습니다 :

CREATE INDEX idx_user_started_at on JOURNALS(user_id, started_at);

TypeORM에서는 @Index 를 사용합니다:

@Entity('journals')
@Index(['userId', 'startedAt'])
export class Journals {
    @PrimaryGeneratedColumn()
    id: number;

• 복합 인덱스는 각 컬럼 위에 개별적으로 작성하지 않고 엔티티 클래스 위에 작성합니다.

• 인덱스를 적용할 컬럼의 배열을 인자로 받습니다.

개선 결과

-> Nested loop inner join  (cost=85.4 rows=55) (actual time=1.1..1.62 rows=31 loops=1)
    -> Index range scan on journals using idx_user_started_at over (user_id = 1 AND '2024-06-30 15:00:00' <= started_at < '2024-07-31 15:00:00'), with index condition: ((journals.user_id = 1) and (journals.started_at >= TIMESTAMP'2024-06-30 15:00:00') and (journals.started_at < TIMESTAMP'2024-07-31 14:59:59.999'))  (cost=25 rows=55) (actual time=0.351..0.772 rows=55 loops=1)
    -> Single-row covering index lookup on journals_dogs using PRIMARY (journal_id=journals.id, dog_id=1)  (cost=0.999 rows=1) (actual time=0.00839..0.00841 rows=0.564 loops=55)

• user_id로 인덱스 lookup한 뒤 started_at으로 필터링하는 두 단계가 한 번의 Index range scan으로 처리됨을 알 수 있습니다.
• 이를 통해 cost가 307 -> 85.4로 줄었습니다.

앞선 조인 순서 바꾸기를 통해 457 -> 307로, 복합 인덱스 적용으로 307 -> 85.4로 줄어 최종적으로 cost를 81.3% 개선하였습니다.