PostgreSQL 쿼리 최적화 2편: LATERAL JOIN의 함정과 해결

낭가인·2025년 12월 19일

SKALA최종프로젝트

목록 보기
9/9

이전 글 요약

1편에서 250개의 쿼리를 1개로 줄이는 최적화를 진행했습니다.

-- 250개 좌표에 대해 LATERAL JOIN으로 한 번에 조회
WITH target_coords AS (
    SELECT * FROM (VALUES ('11010', 37.5, 127.0), ...)
    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
FROM target_coords tc
CROSS JOIN LATERAL (
    SELECT target_year, score
    FROM hazard_results
    WHERE risk_type = %s
    AND target_year BETWEEN %s AND %s  -- 예: 2025 ~ 2100
    ORDER BY (거리 계산) ASC
    LIMIT 1
) hr
ORDER BY tc.region_code, hr.target_year

성능은 극적으로 개선되었지만... 새로운 문제가 발생했습니다.

문제 발견: 각 지역이 1개 연도 데이터만 반환

요구사항

  • 각 행정구역마다 2025, 2030, 2035, ..., 2095, 2100년 (16개 연도) 데이터 필요
  • 클라이언트는 연도별 시계열 그래프를 그려야 함

실제 결과

{
  "regionScores": {
    "11010": { "2025": 33.1 },  // ❌ 2025년만!
    "11020": { "2025": 30.9 },  // ❌ 2025년만!
    "11030": { "2025": 33.5 }   // ❌ 2025년만!
  }
}

기대한 결과

{
  "regionScores": {
    "11010": {
      "2025": 33.1,
      "2030": 35.2,
      "2035": 37.5,
      // ... 중략
      "2100": 55.8
    }
  }
}

원인 분석: LATERAL JOIN의 동작 방식

문제의 쿼리 구조

FROM target_coords tc              -- 250개 행
CROSS JOIN LATERAL (
    SELECT target_year, score
    FROM hazard_results
    WHERE risk_type = 'extreme_heat'
    AND target_year BETWEEN '2025' AND '2100'  -- 🔴 여기가 문제
    ORDER BY (거리 계산) ASC
    LIMIT 1                        -- 🔴 각 지역당 1개만!
) hr

LATERAL JOIN의 동작:
1. target_coords의 각 행(지역)마다 서브쿼리 실행
2. BETWEEN '2025' AND '2100' 범위 내에서
3. 거리 기준으로 정렬해서
4. LIMIT 1로 가장 가까운 데이터 1개만 선택

문제점:

  • LATERAL은 250개 지역에 대해서만 반복 실행
  • 각 지역마다 연도 구분 없이 가장 가까운 데이터 1개만 가져옴
  • 그 1개가 우연히 2025년이었던 것!

비유로 이해하기

현재 쿼리:
"각 지역마다, 2025~2100년 중에서 가장 가까운 데이터 1개만 줘"
→ 결과: 지역당 1개 (대부분 2025년)

원하는 동작:
"각 지역마다, 각 연도별로 가장 가까운 데이터를 줘"
→ 결과: 지역당 16개 (연도별로)

해결 방법 1: 연도도 CROSS JOIN으로 추가

핵심 아이디어

  • 지역만 반복하는 게 아니라
  • 지역 × 연도 모든 조합에 대해 LATERAL JOIN 실행

수정된 쿼리

WITH target_coords AS (
    -- 250개 지역 좌표
    SELECT * FROM (VALUES
        ('11010', 37.5, 127.0),
        ('11020', 37.6, 127.1),
        -- ... 250개
    ) AS t(region_code, target_lat, target_lng)
),
target_years AS (
    -- 16개 연도 (NEW!)
    SELECT * FROM (VALUES
        ('2025'), ('2030'), ('2035'), ('2040'),
        ('2045'), ('2050'), ('2055'), ('2060'),
        ('2065'), ('2070'), ('2075'), ('2080'),
        ('2085'), ('2090'), ('2095'), ('2100')
    ) AS y(year)
)
SELECT DISTINCT ON (tc.region_code, ty.year)
    tc.region_code,
    ty.year as target_year,
    hr.score
FROM target_coords tc
CROSS JOIN target_years ty          -- 🟢 연도도 CROSS JOIN!
CROSS JOIN LATERAL (
    SELECT score
    FROM hazard_results
    WHERE risk_type = %s
    AND target_year = ty.year       -- 🟢 특정 연도만 조회
    ORDER BY (
        POW(latitude - tc.target_lat::numeric, 2) +
        POW(longitude - tc.target_lng::numeric, 2)
    ) ASC
    LIMIT 1                         -- 🟢 해당 연도의 최근접 데이터 1개
) hr
ORDER BY tc.region_code, ty.year

동작 방식

Before:
┌─────────┐
│ 지역 250개 │ → LATERAL → 각 지역당 1개 데이터
└─────────┘

After:
┌─────────┐   ┌────────┐
│ 지역 250개 │ × │ 연도 16개 │ → 250 × 16 = 4,000개 조합
└─────────┘   └────────┘
      ↓
각 (지역, 연도) 조합마다 LATERAL 실행
      ↓
4,000개 데이터 (지역당 16개 연도)

코드 변경 내용

Before (1개 연도만 반환)

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 (거리 계산) ASC
        LIMIT 1
    ) hr
    ORDER BY tc.region_code, hr.target_year
"""

After (모든 연도 반환)

# 고정된 연도 범위 생성
fixed_years = list(range(2025, 2101, 5))  # [2025, 2030, ..., 2100]

query_region_batch = f"""
    WITH target_coords AS (
        SELECT * FROM (VALUES {coords_clause})
        AS t(region_code, target_lat, target_lng)
    ),
    target_years AS (
        SELECT * FROM (VALUES {','.join("('" + str(y) + "')" for y in fixed_years)})
        AS y(year)
    )
    SELECT DISTINCT ON (tc.region_code, ty.year)
        tc.region_code,
        ty.year as target_year,
        hr.{score_col} as score
    FROM target_coords tc
    CROSS JOIN target_years ty
    CROSS JOIN LATERAL (
        SELECT {score_col}
        FROM hazard_results
        WHERE risk_type = %s
        AND target_year = ty.year
        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, ty.year
"""

추가로 발견한 문제: 타입 불일치

에러 발생

psycopg2.errors.UndefinedFunction: operator does not exist: character varying = integer
LINE 9: AND target_year IN (2025,2030,2035,...)
        ^
HINT: No operator matches the given name and argument types.
You might need to add explicit type casts.

원인

  • DB의 target_year 컬럼: VARCHAR (문자열)
  • 쿼리의 값: 2025 (정수)
  • PostgreSQL은 자동 타입 변환을 하지 않음

해결

# Before (정수)
AND target_year IN ({','.join(str(y) for y in fixed_years)})
# 결과: AND target_year IN (2025,2030,2035,...)

# After (문자열로 감싸기)
AND target_year IN ({','.join("'" + str(y) + "'" for y in fixed_years)})
# 결과: AND target_year IN ('2025','2030','2035',...)

성능 비교

방식쿼리 수결과 데이터실행 시간완성도
원본 (Python Loop)250개250 × 16 = 4,000개~30초❌ 타임아웃
1차 최적화 (LATERAL)1개250개 (❌)~1초❌ 데이터 부족
2차 최적화 (연도 CROSS JOIN)1개4,000개 (✅)~2초✅ 완벽

LATERAL JOIN 사용 시 주의사항

1. 반복 단위를 명확히 하기

-- ❌ 잘못된 생각: "LATERAL이 알아서 연도별로 반복하겠지"
CROSS JOIN LATERAL (
    WHERE target_year BETWEEN '2025' AND '2100'
    LIMIT 1
)

-- ✅ 올바른 방법: "반복할 것을 명시적으로 CROSS JOIN"
CROSS JOIN target_years ty
CROSS JOIN LATERAL (
    WHERE target_year = ty.year
    LIMIT 1
)

2. LIMIT의 의미 이해하기

-- LIMIT 1의 의미:
-- "각 LATERAL 실행마다 1개만 반환"
-- ≠ "전체 결과 중 1개만 반환"

-- 지역 250개 × LATERAL LIMIT 1 = 250개 결과
-- 지역 250개 × 연도 16개 × LATERAL LIMIT 1 = 4,000개 결과

3. DISTINCT ON 활용

SELECT DISTINCT ON (tc.region_code, ty.year)
    -- (지역, 연도) 조합마다 첫 번째 행만 선택
    -- 이미 LATERAL에서 LIMIT 1을 했으므로 중복 방지용

최종 결과

API 응답 예시

{
  "regionScores": {
    "11010": {
      "2025": 33.1,
      "2030": 35.4,
      "2035": 38.2,
      "2040": 41.5,
      "2045": 44.8,
      "2050": 48.1,
      "2055": 51.6,
      "2060": 54.9,
      "2065": 58.3,
      "2070": 61.8,
      "2075": 65.2,
      "2080": 68.7,
      "2085": 72.1,
      "2090": 75.6,
      "2095": 79.0,
      "2100": 82.5
    },
    "11020": { /* 16개 연도 */ },
    // ... 248개 지역 더
  },
  "siteAALs": {
    "uuid1": { /* 16개 연도 */ },
    "uuid2": { /* 16개 연도 */ }
  }
}

학습 포인트

1. LATERAL JOIN은 만능이 아니다

  • 왼쪽 테이블의 각 행마다 서브쿼리 실행
  • 다차원 반복이 필요하면 명시적으로 CROSS JOIN

2. SQL은 명시적이어야 한다

  • "DB가 알아서 해주겠지" ❌
  • "내가 원하는 걸 정확히 표현" ✅

3. 쿼리 결과를 항상 검증

# 단순히 쿼리가 성공했다고 끝이 아니라
region_rows = db.execute_query(...)

# 결과 개수를 확인
expected_count = len(REGION_COORD_MAP) * len(fixed_years)
actual_count = len(region_rows)
assert actual_count == expected_count, \
    f"Expected {expected_count}, got {actual_count}"

결론

LATERAL JOIN을 사용할 때는:

  1. 반복 단위를 명확히: 무엇을 기준으로 반복할 것인가?
  2. CROSS JOIN으로 명시: 다차원 반복은 명시적으로 표현
  3. LIMIT의 범위 이해: 각 LATERAL 실행마다의 제한
  4. 결과 검증: 기대한 데이터 개수가 맞는지 확인

250개 쿼리를 1개로 줄이는 것도 중요하지만,
올바른 결과를 반환하는 것이 더 중요합니다.

참고 자료

profile
낭가인
안녕하세요
이전 포스트

APScheduler 배치 작업 Instance Hang 문제 해결

0개의 댓글