DB프로그래밍

[PostgreSQL] 통계 쿼리 최적화 방법

똘켓

가장 빠르고 안정적인 방식 TOP 3

1) JOIN + GROUP BY + SUM() (가장 일반적이고 빠름)

tb1 중심으로 tb2.ad_cnt를 합쳐서 한 줄로 가져올 때 가장 흔히 사용하는 방식.

SELECT 
    t1.id,
    COALESCE(SUM(t2.ad_cnt), 0) AS ad_cnt_sum
FROM tb1 t1
LEFT JOIN tb2 t2 
       ON t2.tb1_id = t1.id
GROUP BY t1.id;

✔ 성능 최적화 필수 인덱스

CREATE INDEX idx_tb2_tb1id ON tb2(tb1_id);

왜 빠름?

  • tb2 → tb1 조인 시 인덱스가 있으면 Nested Loop가 매우 빠르게 수행됨.
  • SUM은 Aggregation 중 가장 가벼운 연산이라 병목이 거의 없음.

2) LATERAL 서브쿼리 (tb2가 매우 큰 경우 속도가 더 잘 나올 수 있음)

tb2에 건수가 수백만 건 이상일 때, LATERAL 방식이 더 빠를 수 있어.

SELECT
    t1.id,
    COALESCE(t2.sum_cnt, 0) AS ad_cnt_sum
FROM tb1 t1
LEFT JOIN LATERAL (
    SELECT SUM(ad_cnt) AS sum_cnt
    FROM tb2
    WHERE tb2.tb1_id = t1.id
) t2 ON TRUE;

✔ 언제 더 빠르나?

  • tb1은 작고 tb2는 매우 클 때
  • tb2.tb1_id 인덱스가 매우 효율적으로 작동함
  • 불필요한 전체 GROUP BY 스캔을 피함

3) ARRAY_AGG 또는 JSON 집계 (모든 tb2 값을 한 줄로 묶어야 하는 경우)

ad_cnt 여러 값을 한 필드에 모아야 하는 경우:

배열로 한 줄

SELECT 
    t1.id,
    ARRAY_AGG(t2.ad_cnt) AS ad_cnt_list
FROM tb1 t1
LEFT JOIN tb2 t2 ON t2.tb1_id = t1.id
GROUP BY t1.id;

JSON으로 한 줄

SELECT 
    t1.id,
    JSON_AGG(t2.ad_cnt) AS ad_cnt_json
FROM tb1 t1
LEFT JOIN tb2 t2 ON t2.tb1_id = t1.id
GROUP BY t1.id;

✔ 필요한 인덱스

CREATE INDEX idx_tb2_tb1id ON tb2(tb1_id);


성능을 더 끌어올리는 팁

1) tb2(tb1_id) 인덱스는 필수

이거 없으면 조인 병목 확정.

2) ad_cnt 합계가 자주 필요하면 Materialized View 캐싱 추천

데이터가 많이 변경되지 않는 경우에 매우 효과적.

CREATE MATERIALIZED VIEW mv_tb1_adcnt AS
SELECT tb1_id, SUM(ad_cnt) AS sum_cnt
FROM tb2
GROUP BY tb1_id;

3) tb2의 많은 컬럼이 필요 없다면 필요한 컬럼만 선택

PostgreSQL은 튜플 넓이가 넓어지면 캐시 미스가 늘어 속도 저하됨.

Hi, I’m 똘켓

Related Posts