PostgreSQL 쿼리 최적화: 250개 쿼리를 1개로 줄이기
문제 상황
FastAPI 기후 시뮬레이션 API에서 30초 타임아웃 문제가 발생했습니다.
- 250개 행정구역 × 80년 × 4개 시나리오 × 9개 리스크 데이터 조회
- API 응답 시간: 30초 이상
- 결과: 타임아웃으로 실패
원인 분석
# 기존 코드 (문제)
for code, coord in REGION_COORD_MAP.items(): # 250번 반복
target_lat = coord["lat"]
target_lng = coord["lng"]
query = """
SELECT DISTINCT ON (target_year)
target_year, score, latitude, longitude
FROM hazard_results
WHERE risk_type = %s
AND target_year BETWEEN %s AND %s
ORDER BY target_year, (
POW(latitude - %s, 2) + POW(longitude - %s, 2)
) ASC
"""
# 매번 DB에 쿼리 실행
region_rows = db.execute_query(query, (risk_type, start_year, end_year, lat, lng))문제점:
- Python 루프에서 250번 DB 쿼리 실행
- 250번의 네트워크 왕복 (Round-trip)
- 각 쿼리마다 연결, 파싱, 실행 오버헤드
해결 방법: LATERAL JOIN을 활용한 배치 쿼리
핵심 아이디어
- 250개 좌표를 SQL VALUES로 임시 테이블 생성
- LATERAL JOIN으로 각 좌표별 최근접 데이터 한 번에 조회
- 1번의 쿼리로 모든 데이터 처리
개선된 코드
# 1. 250개 좌표를 VALUES 절로 변환
coords_values = []
for code, coord in REGION_COORD_MAP.items():
coords_values.append(f"('{code}', {coord['lat']}, {coord['lng']})")
coords_clause = ', '.join(coords_values)
# 결과: "('11010', 37.5, 127.0), ('11020', 37.6, 127.1), ..."
# 2. 단일 쿼리로 모든 지역의 모든 연도 데이터 조회
query_region_batch = f"""
WITH target_coords AS (
SELECT * FROM (VALUES {coords_clause})
AS t(region_code, target_lat, target_lng)
)
SELECT DISTINCT ON (tc.region_code, hr.target_year)
tc.region_code,
hr.target_year,
hr.{score_col} as score
FROM target_coords tc
CROSS JOIN LATERAL (
SELECT target_year, {score_col}
FROM hazard_results
WHERE risk_type = %s
AND target_year BETWEEN %s AND %s
ORDER BY (
POW(latitude - tc.target_lat::numeric, 2) +
POW(longitude - tc.target_lng::numeric, 2)
) ASC
LIMIT 1
) hr
ORDER BY tc.region_code, hr.target_year
"""
# 한 번만 실행
region_rows = db.execute_query(
query_region_batch,
(request.hazard_type, str(request.start_year), str(request.end_year))
)핵심 기술 설명
1. CTE (Common Table Expression)
WITH target_coords AS (
SELECT * FROM (VALUES
('11010', 37.5, 127.0),
('11020', 37.6, 127.1),
-- ... 250개
) AS t(region_code, target_lat, target_lng)
)- 250개 좌표를 메모리상의 임시 테이블로 생성
- 쿼리 내에서 여러 번 참조 가능
2. LATERAL JOIN
FROM target_coords tc
CROSS JOIN LATERAL (
SELECT ...
FROM hazard_results
WHERE ...
ORDER BY 거리계산
LIMIT 1
) hr일반 JOIN vs LATERAL JOIN:
| 구분 | 일반 JOIN | LATERAL JOIN |
|---|---|---|
| 동작 | 고정된 테이블끼리 조인 | 왼쪽 행마다 서브쿼리 실행 |
| 참조 | 서브쿼리에서 외부 테이블 참조 불가 | 서브쿼리에서 tc 참조 가능 |
| 용도 | 정적 조인 | 동적 계산, 최근접 검색 |
LATERAL의 장점:
- 250개 좌표 각각에 대해 최근접 hazard 데이터를 찾음
- DB 엔진이 병렬 처리 최적화
- Python 루프보다 훨씬 효율적
3. DISTINCT ON
SELECT DISTINCT ON (tc.region_code, hr.target_year)
tc.region_code,
hr.target_year,
hr.score
FROM ...
ORDER BY tc.region_code, hr.target_year- (지역코드, 연도) 조합마다 첫 번째 행만 선택
- 거리 기준 정렬 후 가장 가까운 데이터만 추출
비유로 이해하기
기존 방식 (250번 왕복)
개발자: "서울 종로구 근처 데이터 찾아줘"
DB: "찾았어요"
개발자: "서울 중구 근처 데이터 찾아줘"
DB: "찾았어요"
... 248번 더 반복개선된 방식 (1번 왕복)
개발자: "이 250개 좌표 근처 데이터를 한번에 찾아줘"
[서울 종로구, 서울 중구, ... 250개]
DB: "250개 전부 찾아서 한 번에 줄게"성능 개선 결과
| 항목 | Before | After | 개선율 |
|---|---|---|---|
| 쿼리 횟수 | 250회 | 1회 | 99.6% 감소 |
| 네트워크 왕복 | 250번 | 1번 | 99.6% 감소 |
| 응답 시간 | 30초+ | 1~3초 | 약 10~30배 향상 |
| 타임아웃 | 실패 | 성공 | 문제 해결 |
추가 최적화: 인덱스 생성
-- 복합 인덱스로 검색 속도 향상
CREATE INDEX idx_hazard_results_risk_year_coords
ON hazard_results (risk_type, target_year, latitude, longitude);
-- 좌표 기반 검색 최적화
CREATE INDEX idx_hazard_results_coords_btree
ON hazard_results (latitude, longitude);적용 가능한 상황
이 패턴은 다음과 같은 경우에 유용합니다:
-
다중 좌표 최근접 검색
- 여러 위치의 가장 가까운 매장/시설 찾기
- 지역별 날씨/환경 데이터 조회
-
배치 데이터 조회
- N개 ID에 대한 관련 데이터 조회
- 각 항목마다 조건부 서브쿼리 필요한 경우
-
Python 루프 → SQL 변환
- 반복문에서 매번 DB 쿼리하는 경우
- N+1 쿼리 문제 해결
주의사항
1. 좌표 개수 제한
# 너무 많은 좌표는 쿼리 크기 초과 가능
if len(REGION_COORD_MAP) > 1000:
# 배치를 나눠서 처리
batch_size = 500
for i in range(0, len(coords), batch_size):
batch = coords[i:i+batch_size]
# 배치별 쿼리 실행2. SQL Injection 방지
# BAD: f-string으로 사용자 입력 직접 삽입
coords_clause = f"('{user_input}', ...)" # 위험!
# GOOD: 고정된 데이터만 VALUES에 사용
# 동적 파라미터는 %s 사용3. 인덱스 활용
ORDER BY POW(...)계산은 인덱스 사용 불가- 데이터가 많으면 좌표 범위로 필터링 후 거리 계산
WHERE latitude BETWEEN %s - 0.1 AND %s + 0.1
AND longitude BETWEEN %s - 0.1 AND %s + 0.1전체 코드 비교
Before (250개 쿼리)
region_scores_map = {}
for code, coord in REGION_COORD_MAP.items():
target_lat = coord["lat"]
target_lng = coord["lng"]
query_region = f"""
SELECT DISTINCT ON (target_year)
target_year, {score_col} as score
FROM hazard_results
WHERE risk_type = %s
AND target_year BETWEEN %s AND %s
ORDER BY target_year, (
POW(latitude - %s, 2) + POW(longitude - %s, 2)
) ASC
"""
region_rows = db.execute_query(
query_region,
(hazard_type, start_year, end_year, target_lat, target_lng)
)
if code not in region_scores_map:
region_scores_map[code] = {}
for row in region_rows:
year = str(row['target_year'])
score = float(row['score'] or 0.0)
region_scores_map[code][year] = scoreAfter (1개 쿼리)
region_scores_map = {}
if REGION_COORD_MAP:
# 1. 좌표 값들을 VALUES 절로 변환
coords_values = []
for code, coord in REGION_COORD_MAP.items():
coords_values.append(f"('{code}', {coord['lat']}, {coord['lng']})")
coords_clause = ', '.join(coords_values)
# 2. 단일 쿼리로 모든 지역의 모든 연도 데이터 조회
query_region_batch = f"""
WITH target_coords AS (
SELECT * FROM (VALUES {coords_clause})
AS t(region_code, target_lat, target_lng)
)
SELECT DISTINCT ON (tc.region_code, hr.target_year)
tc.region_code,
hr.target_year,
hr.{score_col} as score
FROM target_coords tc
CROSS JOIN LATERAL (
SELECT target_year, {score_col}
FROM hazard_results
WHERE risk_type = %s
AND target_year BETWEEN %s AND %s
ORDER BY (
POW(latitude - tc.target_lat::numeric, 2) +
POW(longitude - tc.target_lng::numeric, 2)
) ASC
LIMIT 1
) hr
ORDER BY tc.region_code, hr.target_year
"""
region_rows = db.execute_query(
query_region_batch,
(hazard_type, start_year, end_year)
)
# 결과를 region_scores_map에 저장
for row in region_rows:
code = row['region_code']
year = str(row['target_year'])
score = float(row['score'] or 0.0)
if code not in region_scores_map:
region_scores_map[code] = {}
region_scores_map[code][year] = score결론
Python 루프에서 반복적으로 DB 쿼리를 실행하는 대신, SQL의 강력한 기능(CTE, LATERAL JOIN)을 활용하면:
- 네트워크 왕복 최소화
- DB 엔진의 최적화 활용
- 극적인 성능 향상
핵심 원칙: "데이터를 애플리케이션으로 가져와서 처리하지 말고, DB에서 처리해서 결과만 가져오자"
참고 자료
안녕하세요