문제 상황
테스트를 위해 12만 개의 데이터를 생성 후, 신청 내역을 30개씩 가져오는 데 약300~400ms가 소요됨.
기존 쿼리 성능 (평균 약 359.212ms)
- Query Application: 395.96ms
- Query Application: 345.71ms
- Query Application: 343.412ms
- Query Application: 322.222ms
- Query Application: 373.989ms
- Query Application: 357.407ms
- Query Application: 339.254ms
- Query Application: 348.709ms
- Query Application: 361.956ms
- Query Application: 363.505ms
테스트 환경
- 장비: M1 MacBook Air, RAM 16GB
- 기술 스택: NestJS, TypeORM, PostgreSQL (Docker 사용)
- 데이터 갯수:
- UserEntity: 3
- PolicyEntity: 4
- CompetitionEntity: 1000
- EarlybirdDiscountSnapshotEntity: 600
- CombinationDiscountSnapshotEntity: 400
- RequiredAdditionalInfoEntity: 400
- CompetitionHostMapEntity: 2400
- AdditionalInfoEntity: 60000
- ApplicationEntity: 120000
- PlayerSnapshotEntity: 120000
- ParticipationDivisionInfoEntity: 225600
- ParticipationDivisionInfoSnapshotEntity: 225600
- DivisionEntity: 362400
- PriceSnapshotEntity: 362400
Application ERD
erDiagram
player_snapshot {
varchar id PK
varchar name
varchar gender
varchar birth
varchar phoneNumber
varchar belt
varchar network
varchar team
varchar masterName
timestamptz createdAt
varchar applicationId FK
}
payment_snapshot {
varchar id PK
timestamptz createdAt
integer normalAmount
integer earlybirdDiscountAmount
integer combinationDiscountAmount
integer totalAmount
varchar applicationId FK
}
participation_division_info_snapshot {
varchar id PK
timestamptz createdAt
varchar participationDivisionInfoId FK
varchar participationDivisionId FK
}
participation_division_info {
varchar id PK
timestamptz createdAt
varchar applicationId FK
}
price_snapshot {
varchar id PK
integer price
timestamptz createdAt
varchar divisionId FK
}
division {
varchar id PK
varchar category
varchar uniform
varchar gender
varchar belt
varchar weight
varchar birthYearRangeStart
varchar birthYearRangeEnd
varchar status
timestamptz createdAt
timestamptz updatedAt
varchar competitionId FK
}
additional_info {
varchar id PK
timestamptz createdAt
timestamptz updatedAt
varchar type
varchar value
varchar applicationId FK
}
application {
varchar id PK
timestamptz createdAt
timestamptz updatedAt
timestamptz deletedAt "nullable"
varchar type
varchar status
varchar competitionId FK
varchar userId FK
}
player_snapshot }|--|| application: application
payment_snapshot }o--|| application: application
participation_division_info_snapshot }|--|| participation_division_info: participationDivisionInfo
participation_division_info_snapshot }o--|| division: division
participation_division_info }|--|| application: application
price_snapshot }|--|| division: division
additional_info }o--|| application: application
인덱스 적용 (Application indexes)
Table | Index Name | Columns | Unique | Spatial | Where |
---|---|---|---|---|---|
player_snapshot | IDX_PlayerSnapshot_applicationId | applicationId | false | false | |
participation_division_info_snapshot | IDX_ParticipationDivisionInfoSnapshot_participationDivisionInfoId | participationDivisionInfoId | false | false | |
participation_division_info | IDX_ParticipationDivisionInfo_applicationId | applicationId | false | false | |
price_snapshot | IDX_PriceSnapshot_divisionId | divisionId | false | false | |
division | IDX_Division_competitionId | competitionId | false | false | |
additional_info | IDX_AddtionalInfo_applicationId | applicationId | false | false | |
application | IDX_Application_userId_createdAt | userId, createdAt | false | false |
최적화 후 성능 (평균 약 44.199ms)
- Query Application: 56.61ms
- Query Application: 32.402ms
- Query Application: 47.185ms
- Query Application: 41.437ms
- Query Application: 45.027ms
- Query Application: 47.785ms
- Query Application: 47.505ms
- Query Application: 39.513ms
- Query Application: 35.459ms
- Query Application: 47.069ms
성능 개선 결과 (약 87.70% 개선)
- 개선 전 : 359.212ms
- 개선 후 : 44.199ms
==약 87.70% 개선되었습니다.==