PostgreSQL Lateral Join 활용

Introduction

PostgreSQL의 Lateral Join은 서브쿼리에서 외부 쿼리의 컬럼을 참조할 수 있게 해주는 강력한 기능입니다. 일반적인 JOIN에서는 불가능한 "FOR EACH ROW" 패턴의 서브쿼리를 구현할 수 있어, 복잡한 데이터 분석 쿼리를 효율적으로 작성할 수 있습니다. 이 글에서는 Lateral Join의 개념과 실제 활용 사례를 다루겠습니다.

Environment

-- PostgreSQL 버전 확인
SELECT version();

-- 테스트 테이블 생성
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10,2),
    hire_date DATE
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    project_name VARCHAR(100),
    budget NUMERIC(12,2),
    start_date DATE
);

# psql 연결
psql -U postgres -d mydb

Problem

기존 JOIN 방식의 한계:

-- 각 직원의 최근 프로젝트를 조회하려는 시도
SELECT 
    e.name,
    e.department,
    p.project_name,
    p.budget
FROM employees e
JOIN projects p ON e.id = p.employee_id
WHERE p.start_date = (
    SELECT MAX(p2.start_date)
    FROM projects p2
    WHERE p2.employee_id = e.id
);
-- 이 쿼리는 의도대로 작동하지만, 성능 문제가 있을 수 있음

-- correlated subquery의 성능 문제
EXPLAIN ANALYZE
SELECT 
    e.name,
    (SELECT COUNT(*) FROM projects p WHERE p.employee_id = e.id) as project_count
FROM employees e;
-- Seq Scan on employees  (cost=0.00..1234.56 rows=1000)
--   SubPlan 1
--     ->  Seq Scan on projects p  (cost=0.00..123.45 rows=1)
--           Filter: (employee_id = e.id)

Analysis

Lateral Join이 필요한 상황을 분석했습니다:

-- 상위 3개 프로젝트를 직원별로 조회
-- 기존 방식: 복잡한 윈도우 함수 사용
SELECT name, project_name, budget
FROM (
    SELECT 
        e.name,
        p.project_name,
        p.budget,
        ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY p.budget DESC) as rn
    FROM employees e
    JOIN projects p ON e.id = p.employee_id
) ranked
WHERE rn <= 3;

-- Lateral Join을 사용하면 더 직관적
SELECT 
    e.name,
    top_projects.*
FROM employees e,
LATERAL (
    SELECT p.project_name, p.budget
    FROM projects p
    WHERE p.employee_id = e.id
    ORDER BY p.budget DESC
    LIMIT 3
) top_projects;

Solution

1단계: 기본 Lateral Join 사용

-- 각 직원의 평균 프로젝트 예산보다 높은 프로젝트 조회
SELECT 
    e.name,
    e.department,
    high_budget_projects.*
FROM employees e,
LATERAL (
    SELECT 
        p.project_name,
        p.budget,
        p.start_date
    FROM projects p
    WHERE p.employee_id = e.id
      AND p.budget > (
          SELECT AVG(p2.budget)
          FROM projects p2
          WHERE p2.employee_id = e.id
      )
    ORDER BY p.budget DESC
    LIMIT 5
) high_budget_projects;

-- 실행 계획 확인
EXPLAIN ANALYZE
SELECT 
    e.name,
    top_projects.*
FROM employees e,
LATERAL (
    SELECT p.project_name, p.budget
    FROM projects p
    WHERE p.employee_id = e.id
    ORDER BY p.budget DESC
    LIMIT 3
) top_projects;
-- Nested Loop  (cost=0.56..4567.89 rows=3000)
--   ->  Seq Scan on employees e  (cost=0.00..12.34 rows=1000)
--   ->  Limit  (cost=0.56..4.58 rows=3)
--         ->  Index Scan using idx_projects_employee on projects p  (cost=0.56..4.58 rows=3)
--               Filter: (employee_id = e.id)

2단계: 복잡한 분석 쿼리

-- 월별 매출 트렌드와 전월 대비 변화율 계산
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) as month,
        SUM(amount) as total_sales
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT 
    ms.month,
    ms.total_sales,
    prev_month.total_sales as prev_month_sales,
    CASE 
        WHEN prev_month.total_sales > 0 
        THEN ((ms.total_sales - prev_month.total_sales) / prev_month.total_sales * 100)
        ELSE NULL 
    END as growth_rate
FROM monthly_sales ms,
LATERAL (
    SELECT total_sales
    FROM monthly_sales ms2
    WHERE ms2.month = ms.month - INTERVAL '1 month'
) prev_month
ORDER BY ms.month;

-- Lateral Join을 사용한 세션 분석
SELECT 
    u.user_id,
    s.session_start,
    s.page_views,
    s.duration
FROM users u,
LATERAL (
    SELECT 
        session_start,
        COUNT(*) as page_views,
        MAX(timestamp) - MIN(timestamp) as duration
    FROM page_views pv
    WHERE pv.user_id = u.id
      AND pv.timestamp >= u.last_login
    GROUP BY session_start
    ORDER BY session_start DESC
    LIMIT 5
) s;

3단계: 성능 최적화

-- 인덱스 최적화
CREATE INDEX idx_projects_employee_budget 
ON projects(employee_id, budget DESC);

CREATE INDEX idx_page_views_user_timestamp 
ON page_views(user_id, timestamp);

-- Lateral Join 성능 비교
-- 기존 방식
EXPLAIN ANALYZE
SELECT 
    e.name,
    p.project_name,
    p.budget
FROM employees e
JOIN LATERAL (
    SELECT project_name, budget
    FROM projects
    WHERE employee_id = e.id
    ORDER BY budget DESC
    LIMIT 3
) p ON true;
-- Nested Loop  (cost=0.56..2345.67 rows=3000)
--   ->  Seq Scan on employees e  (cost=0.00..12.34 rows=1000)
--   ->  Limit  (cost=0.56..2.35 rows=3)
--         ->  Index Scan using idx_projects_employee_budget on projects  (cost=0.56..2.35 rows=3)
--               Filter: (employee_id = e.id)

-- 실행 시간: 125ms

-- 서브쿼리 방식과 비교
EXPLAIN ANALYZE
SELECT 
    e.name,
    p.project_name,
    p.budget
FROM employees e
JOIN (
    SELECT 
        employee_id,
        project_name,
        budget,
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY budget DESC) as rn
    FROM projects
) p ON e.id = p.employee_id AND p.rn <= 3;
-- Hash Join  (cost=345.67..5678.90 rows=3000)
--   ->  Seq Scan on employees e  (cost=0.00..12.34 rows=1000)
--   ->  Subquery Scan on p  (cost=345.67..565.67 rows=10000)
--         Filter: (p.rn <= 3)
--         ->  WindowAgg  (cost=345.67..456.78 rows=10000)
--               ->  Sort  (cost=345.67..370.67 rows=10000)
--                     Sort Key: p.employee_id, p.budget DESC

-- 실행 시간: 234ms (Lateral Join이 2배 빠름)

Lessons Learned

Lateral Join의 장점: 외부 쿼리의 컬럼을 서브쿼리에서 직접 참조할 수 있어 더 직관적인 쿼리 작성 가능
성능 비교: 윈도우 함수 대신 Lateral Join을 사용하면 성능이 향상될 수 있습니다
인덱스 활용: Lateral Join의 서브쿼리에서 사용되는 인덱스가 성능에 중요한 영향
LIMIT 활용: Lateral Join과 함께 LIMIT를 사용하면 불필요한 데이터 처리를 줄일 수 있습니다
EXPLAIN ANALYZE: 항상 실행 계획을 확인하여 Lateral Join이 올바르게 최적화되는지 검증해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

PostgreSQL Lateral Join 활용: 고급 쿼리 최적화 기법