윈도우 함수로 쿼리 최적화하기

아직 얘기 안 한 중요한 포인트가 하나 남았어. 바로 윈도우 함수가 들어간 쿼리의 성능이지. 아무리 쿨하게 짠 쿼리라도, 최적화 신경 안 쓰면 느려터진 거북이처럼 될 수 있어. 오늘은 이걸 제대로 파헤쳐볼 거야!

윈도우 함수는 진짜 유연하고 강력해. 근데 이 유연함이 축복이자, 성능에 위협이 될 수도 있어. PostgreSQL은 "마법"처럼 동작하지 않고, 데이터를 처리하려면 리소스가 필요해. 특히 윈도우 함수를 엄청 큰 테이블에 쓰면, 쿼리가 러닝머신 위에서 끝없이 달리는 느낌이 날 수도 있지.

최적화를 하면 이런 걸 할 수 있어:

• 대용량 데이터 쿼리를 더 빠르게 만들기
• 데이터베이스에 부담 줄이기
• 서버(그리고 같이 DB 쓰는 동료들)에게 더 친절한 쿼리 만들기

자, 이제 본격적으로 들어가서, 쿼리가 자동차 경주차처럼 날아다니게 만드는 방법을 알아보자.

윈도우 함수 동작의 기본

최적화하기 전에, 뭐가 쿼리를 느리게 만드는지부터 이해해야 해. PostgreSQL에서 윈도우 함수는 이렇게 동작해:

1. OVER() 안에 ORDER BY가 있으면 데이터를 정렬함.
2. 정해진 윈도우 프레임이나 그룹 안에서 각 행을 처리함.
3. 각 행마다 결과를 반환함.

이제 sales라는 테이블에 1천만 행이 있다고 상상해봐. 쿼리에 필터가 없으면, PostgreSQL은 이 모든 행을 다 처리해야 해. 이건 그냥 마라톤이 아니라, 끝없는 러닝머신이지.

윈도우 함수, 어떻게 더 빠르게 만들까?

1. 정렬을 빠르게 하려면 인덱스 쓰기

대부분의 윈도우 함수는 OVER() 안에 ORDER BY를 써서 행의 순서를 정해. 이 말은, PostgreSQL이 윈도우 함수 실행 전에 데이터를 정렬해야 한다는 뜻이야.

ORDER BY에 쓰는 컬럼(혹은 여러 컬럼)에 인덱스가 있으면, PostgreSQL이 정렬을 훨씬 빠르게 할 수 있어.

예시

CREATE INDEX idx_sales_date ON sales (sale_date);

이제 sale_date로 정렬하는 쿼리를 쓰면, 인덱스가 빛을 발하지:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

sale_date에 인덱스가 없으면, 쿼리 실행할 때마다 PostgreSQL이 느린 정렬을 하느라 허둥댈 거야.

2. WHERE로 필터링해서 데이터 줄이기

데이터 양을 줄이는 게 최적화의 핵심이야. 1천만 행을 다 처리할 필요 없고, 최근 1년치만 필요하다면 WHERE로 범위를 좁혀!

예시

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

이건 마치 더러운 물을 체로 걸러서 필요한 정보만 남기는 거랑 비슷해.

3. 적절한 윈도우 프레임 고르기

윈도우 함수로 집계할 때, 예를 들어 SUM()을 쓸 때, 프레임을 잘 정하는 게 중요해. 기본 프레임(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)을 쓰면, PostgreSQL이 현재 행까지 모든 행을 다 포함시켜. 이건 큰 테이블에선 비효율적일 수 있어.

예시: ROWS 사용하기

현재 행 기준으로 몇 개만 포함하고 싶으면, ROWS로 명확하게 지정해:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

이렇게 하면 PostgreSQL은 각 행마다 3개(이전 2개 + 현재)만 처리해. 기본값으로 수백 개씩 처리하는 것보다 훨씬 효율적이지.

4. 윈도우 함수 개수 최소화하기

각 윈도우 함수는 PostgreSQL이 따로 처리해. 여러 개 쓰면, 각각 정렬을 따로 할 수도 있어서 느려질 수 있어. 하지만 윈도우 파라미터(PARTITION BY, ORDER BY)가 같으면, PostgreSQL이 더 효율적으로 처리할 수 있어.

예시: 같은 윈도우로 최적화

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

SUM()이랑 ROW_NUMBER() 둘 다 같은 프레임을 써. PostgreSQL이 정렬을 한 번만 하면 돼서, 이게 바로 꿀팁이지.

5. 테이블 파티셔닝

테이블이 너무 크면, 물리적으로 여러 조각으로 나누는 걸 고민해봐. PostgreSQL은 파티셔닝된 테이블을 만들 수 있어서, 데이터가 여러 세그먼트에 나뉘어 저장돼. 이러면 처리 속도가 확 올라갈 수 있어.

파티셔닝 테이블 만들기 예시

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

이제 연도별로 파티션을 만들 수 있어:

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

이제 WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'를 쓰면, PostgreSQL이 자동으로 해당 파티션만 조회해.

파티셔닝에 대한 자세한 얘기는 코스 마지막쯤에 더 다룰 거야 :P

6. 불필요한 데이터 피하기 (SELECT는 필요한 것만!)

윈도우 함수랑 결과에 꼭 필요한 컬럼만 골라서 가져와. product_id, sale_date, amount만 필요하면, 고객의 바이오 데이터까지 다 끌고 올 필요 없어.

"알뜰" 쿼리 예시

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

데이터가 적을수록 PostgreSQL이 할 일이 줄어들지.

7. materialization(MATERIALIZED VIEW) 활용하기

자주 같은 윈도우 함수 계산을 한다면, 결과를 materialized view에 저장해두는 것도 방법이야. Materialized View는 데이터를 디스크에 저장해서, 복잡한 쿼리를 매번 다시 안 돌려도 돼.

materialized view 만들기 예시

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

이제 이렇게 간단히 조회할 수 있어:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. EXPLAIN과 EXPLAIN ANALYZE로 쿼리 플랜 확인하기

SQL의 다른 부분처럼, EXPLAIN이나 EXPLAIN ANALYZE를 써서 PostgreSQL이 쿼리를 어떻게 실행하는지, 어디가 병목인지 볼 수 있어.

쿼리 분석 예시

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

이 도구로 PostgreSQL이 어디서 시간을 많이 쓰는지 확인하고, 병목을 최적화할 수 있어.

윈도우 함수는 데이터 분석에 진짜 강력한 무기지만, 조심해서 써야 해. 속도 내고 싶으면? 인덱스, 필터, 파티션, materialized view 적극 활용해. PostgreSQL은 똑똑하게 써줄 때 제일 좋아해!