PostgreSQL 복잡한 JOIN 쿼리 성능 최적화: 행 증폭 제거·선집계·부분 인덱스
노선, 정류장 시간, 예약, 좌석, 즐겨찾기처럼 1:N 관계가 여러 개 겹친 조회는 SQL 한 문장으로 만들 수 있다. 하지만 한 번에 조인할 수 있다는 것과 효율적으로 조인된다는 것은 다른 문제다. 특히 1:N × 1:N 조인 뒤 GROUP BY, COUNT(DISTINCT ...), SUM(...)으로 결과를 수습하는 구조는 중간 행 수를 크게 늘리고 집계 오류까지 만들 수 있다.
이 글은 실제 PostgreSQL 조회를 튜닝하면서 확인한 내용을 공개용 테이블명으로 바꿔 정리한 기록이다. 핵심은 다음 세 가지다.
- 큰 테이블을 모두 조인한 뒤 집계하지 말고, 각 1:N 테이블을 업무 키 단위로 먼저 집계한다.
- 서로 겹치지 않는 상태 조건은
OR대신UNION ALL로 분리해 조건과 인덱스를 단순하게 만든다. - 인덱스는 개수보다 실제 WHERE·JOIN 조건과 선두 컬럼 순서가 중요하며, 적용 전후는 반드시 실행계획과 운영 통계로 검증한다.
아래 테이블·컬럼 이름은 공개를 위해 일반화했다. 실제 서비스명, 서버 주소, 사용자 번호, 원본 SQL 식별자는 포함하지 않았다.
1. 조회 조건
예시 업무 조건은 다음과 같다.
- 특정 기관과 운행일의 노선을 조회한다.
- 운행일은
:base_date라는YYYYMMDD문자열로 전달된다. - 예약 상태가
S이면 그대로 포함한다. - 예약 상태가
W이면 현재 SQL 문 시작 시각을 기준으로 최근 10분 이내 데이터만 포함한다. - 노선별 시작·종료 시각, 좌석 정보, 예약 수와 사용자 즐겨찾기 여부를 함께 반환한다.
예약 조건만 떼어 보면 다음과 같다.
reservation_date = to_date(:base_date, 'YYYYMMDD')
AND (
state = 'S'
OR (
state = 'W'
AND reserved_at >=
(statement_timestamp() AT TIME ZONE 'Asia/Seoul')
- interval '10 minutes'
)
)
statement_timestamp()는 현재 트랜잭션 시작 시각이 아니라 현재 SQL 문이 시작된 시각을 반환한다. 하나의 SQL 안에서는 기준 시각이 일관되므로 “최근 10분” 조건에 쓰기 좋다.
단, 위 예시는 reserved_at이 한국 로컬 시각을 담는 timestamp without time zone일 때 맞는 형태다. 컬럼이 timestamptz라면 시간대를 제거하지 말고 다음처럼 같은 타입끼리 비교해야 한다.
reserved_at >= statement_timestamp() - interval '10 minutes'
날짜·시간 타입이 섞인 상태에서 암묵적 변환에 기대면 결과와 인덱스 사용 여부가 달라질 수 있다. 먼저 컬럼 타입부터 확인한다.
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'reservation'
AND column_name IN ('reservation_date', 'reserved_at');
2. 첫 번째 병목: 1:N × 1:N 조인의 행 증폭
한 노선에 정류장 시간이 20개, 예약이 30개 있다고 가정해 보자. 두 테이블을 노선에 동시에 조인하면 집계 전 단계에서 최대 20 × 30 = 600개 행이 만들어질 수 있다.
SELECT
r.route_id,
MIN(t.arrival_time) AS start_time,
MAX(t.arrival_time) AS end_time,
COUNT(DISTINCT v.reservation_id) AS reservation_count
FROM route_master r
LEFT JOIN route_time t
ON t.org_id = r.org_id
AND t.route_id = r.route_id
LEFT JOIN reservation v
ON v.org_id = r.org_id
AND v.route_id = r.route_id
WHERE r.org_id = :org_id
GROUP BY r.route_id;
최종 결과가 노선당 한 행이어도 PostgreSQL은 그 전에 늘어난 행을 조인하고 정렬하거나 해시로 묶어야 한다. COUNT(DISTINCT ...)는 중복 결과를 가려줄 수 있지만 중간 처리 비용까지 없애지는 않는다.
더 위험한 것은 SUM이다. 예약 금액이나 좌석 수를 같은 구조에서 합산하면 정류장 수만큼 반복되어 실제보다 크게 계산될 수 있다. 따라서 성능 문제이기 전에 집계 정확성 문제로 봐야 한다.
3. 해결 원칙: 각 테이블을 노선당 한 행으로 줄인 뒤 조인
정류장 시간은 노선별 MIN/MAX, 예약은 노선별 COUNT, 즐겨찾기는 사용자·노선별 한 행으로 먼저 만든다. 이후 노선 기준으로 1:1 조인하면 중간 행 수가 통제된다.
공개용으로 정리한 최적화 SQL
WITH params AS (
SELECT
to_date(:base_date, 'YYYYMMDD') AS service_date,
extract(
isodow FROM to_date(:base_date, 'YYYYMMDD')
)::integer AS iso_day_of_week,
:org_id::bigint AS org_id,
:user_id::bigint AS user_id,
:route_type::text AS route_type,
(statement_timestamp() AT TIME ZONE 'Asia/Seoul')
- interval '10 minutes' AS wait_from
),
route_base AS (
SELECT
r.org_id,
r.route_id,
r.vehicle_id,
r.route_name,
r.seat_type
FROM route_master r
CROSS JOIN params p
WHERE r.org_id = p.org_id
AND r.day_of_week = p.iso_day_of_week
AND r.route_type = p.route_type
AND r.use_yn = 'Y'
),
route_key AS (
SELECT DISTINCT org_id, route_id
FROM route_base
),
favorite_route AS (
SELECT DISTINCT f.org_id, f.route_id
FROM user_favorite_route f
JOIN route_key k
ON k.org_id = f.org_id
AND k.route_id = f.route_id
CROSS JOIN params p
WHERE f.user_id = p.user_id
AND f.use_yn = 'Y'
),
route_time_agg AS (
SELECT
t.org_id,
t.route_id,
MIN(t.arrival_time) AS start_time,
MAX(t.arrival_time) AS end_time
FROM route_time t
JOIN route_key k
ON k.org_id = t.org_id
AND k.route_id = t.route_id
GROUP BY t.org_id, t.route_id
),
reservation_candidates AS (
SELECT
v.org_id,
v.route_id,
v.reservation_id
FROM reservation v
JOIN route_key k
ON k.org_id = v.org_id
AND k.route_id = v.route_id
CROSS JOIN params p
WHERE v.reservation_date = p.service_date
AND v.state = 'S'
UNION ALL
SELECT
v.org_id,
v.route_id,
v.reservation_id
FROM reservation v
JOIN route_key k
ON k.org_id = v.org_id
AND k.route_id = v.route_id
CROSS JOIN params p
WHERE v.reservation_date = p.service_date
AND v.state = 'W'
AND v.reserved_at >= p.wait_from
),
reservation_count AS (
SELECT
org_id,
route_id,
COUNT(DISTINCT reservation_id) AS reservation_count
FROM reservation_candidates
GROUP BY org_id, route_id
)
SELECT
CASE WHEN f.route_id IS NULL THEN 'N' ELSE 'Y' END AS favorite_yn,
r.route_id,
r.vehicle_id,
r.route_name,
t.start_time,
t.end_time,
r.seat_type,
COALESCE(c.reservation_count, 0) AS reservation_count
FROM route_base r
LEFT JOIN favorite_route f
ON f.org_id = r.org_id
AND f.route_id = r.route_id
LEFT JOIN route_time_agg t
ON t.org_id = r.org_id
AND t.route_id = r.route_id
LEFT JOIN reservation_count c
ON c.org_id = r.org_id
AND c.route_id = r.route_id
ORDER BY favorite_yn DESC, t.start_time;
이 구조의 핵심은 CTE 개수가 아니다. 다음 순서를 강제한다는 점이다.
route_base에서 대상 노선을 먼저 줄인다.route_key를 통해 하위 테이블이 읽어야 할 노선 키 범위를 제한한다.- 정류장 시간과 예약을 각각 노선당 한 행으로 집계한다.
- 마지막 SELECT는 대부분 1:1 관계만 조인한다.
route_key에 DISTINCT가 필요한지는 route_base의 유일성에 따라 결정한다. 이미 (org_id, route_id)가 유일하다면 불필요한 DISTINCT는 제거하는 편이 낫다.
4. OR를 UNION ALL로 나눈 이유
두 예약 상태 조건은 서로 겹치지 않는다.
- 첫 번째 분기:
state = 'S' - 두 번째 분기:
state = 'W'이면서 최근 10분
따라서 중복 제거가 필요한 UNION이 아니라 UNION ALL을 사용할 수 있다. 이 구조는 각 분기가 자기 조건에 맞는 부분 인덱스를 사용하기 쉬워지고, 실행계획도 읽기 쉬워진다.
다만 OR를 항상 UNION ALL로 바꾸면 빨라지는 것은 아니다. 데이터 분포와 PostgreSQL 버전에 따라 BitmapOr가 더 효율적일 수도 있다. 변경 전후를 같은 바인드 값과 비슷한 캐시 상태에서 측정해야 한다.
5. COUNT(DISTINCT)를 계속 써야 할까?
위 예제에는 안전하게 COUNT(DISTINCT reservation_id)를 남겨두었다. 하지만 reservation_candidates에서 예약 한 건이 정확히 한 행임이 스키마와 조인 조건으로 보장된다면 다음처럼 줄일 수 있다.
COUNT(*) AS reservation_count
COUNT(DISTINCT)는 중복 가능성을 해결하는 도구이지, 불완전한 조인 조건을 가리는 보험으로 쓰면 안 된다. 다음 순서로 확인한다.
SELECT reservation_id, COUNT(*)
FROM reservation_candidates
GROUP BY reservation_id
HAVING COUNT(*) > 1;
결과가 없고 유일성이 제약조건으로도 보장된다면 COUNT(*) 전환을 검토할 수 있다.
6. 부분 인덱스 설계
원래 조건은 S와 W의 검색 방식이 다르다. 하나의 넓은 인덱스보다 상태별 부분 인덱스가 더 작고 명확할 수 있다.
CREATE INDEX CONCURRENTLY idx_reservation_s_route_date
ON reservation (org_id, route_id, reservation_date)
INCLUDE (reservation_id)
WHERE state = 'S';
CREATE INDEX CONCURRENTLY idx_reservation_w_route_date_time
ON reservation (org_id, route_id, reservation_date, reserved_at)
INCLUDE (reservation_id)
WHERE state = 'W';
org_id, route_id, reservation_date는 동등 조건이고, reserved_at은 범위 조건이다. B-tree 다중 컬럼 인덱스는 일반적으로 선두 동등 조건 뒤에 첫 범위 조건을 두는 구성이 효율적이다.
INCLUDE (reservation_id)는 검색 키가 아니라 결과 반환에 필요한 값을 인덱스 리프에 포함한다. 조건과 테이블 가시성 상태가 맞으면 index-only scan 가능성을 높일 수 있지만, 인덱스가 커지고 쓰기 비용도 늘어난다. 무조건 추가하지 말고 실행계획과 인덱스 크기를 함께 본다.
정류장 시간 집계에는 다음 후보를 검토할 수 있다.
CREATE INDEX CONCURRENTLY idx_route_time_route_arrival
ON route_time (org_id, route_id, arrival_time);
운영 서버에서 CREATE INDEX CONCURRENTLY는 일반 인덱스 생성보다 오래 걸리고 추가 작업이 필요하지만 쓰기를 장시간 막는 위험을 줄여준다. 그래도 CPU·I/O 부하는 발생하므로 저부하 시간에 진행하고 완료 후 유효성을 확인한다.
SELECT
indexrelid::regclass AS index_name,
indisvalid,
indisready
FROM pg_index
WHERE indexrelid IN (
'idx_reservation_s_route_date'::regclass,
'idx_reservation_w_route_date_time'::regclass,
'idx_route_time_route_arrival'::regclass
);
부분 인덱스는 쿼리의 WHERE 조건이 인덱스의 조건을 논리적으로 포함한다고 플래너가 판단할 수 있어야 사용된다. 상태 값을 함수로 감싸거나 표현을 지나치게 바꾸면 인덱스를 인식하지 못할 수 있다.
상태까지 바인드 변수로 일반화한 prepared statement는 계획 시점에 state = 'S' 또는 state = 'W'임을 증명하지 못해 해당 부분 인덱스를 사용하지 못할 수 있다. 이 예시처럼 분기마다 상태 리터럴이 고정되어 있을 때 적용 가능성이 더 명확하다. 또한 변하는 현재 시각은 인덱스 predicate로 만들 수 없으므로, W 상태만 부분 조건으로 두고 reserved_at을 인덱스 키로 둔다.
7. 인덱스를 막는 흔한 표현
문자 컬럼의 공백을 없애려고 다음처럼 조건 컬럼을 함수로 감싸는 경우가 있다.
WHERE trim(route_type) = :route_type
일반 (route_type) 인덱스는 이 표현에 직접 맞지 않는다. 데이터에 실제 패딩이 없다면 불필요한 trim()을 제거한다. 패딩이 업무 규칙이라 제거할 수 없다면 데이터 정규화 또는 표현식 인덱스를 별도로 검토한다.
CREATE INDEX CONCURRENTLY idx_route_master_trimmed_type
ON route_master (org_id, trim(route_type));
표현식 인덱스도 쓰기 비용과 저장 공간을 사용한다. 먼저 데이터 품질을 확인하는 것이 우선이다.
SELECT COUNT(*) AS padded_rows
FROM route_master
WHERE route_type <> trim(route_type);
8. 실행계획은 이렇게 비교한다
인덱스를 만들었다는 사실만으로 성공이라고 판단하지 않는다. 같은 파라미터로 변경 전·후 실행계획을 비교한다.
ANALYZE route_master;
ANALYZE route_time;
ANALYZE reservation;
EXPLAIN (
ANALYZE,
BUFFERS,
VERBOSE,
SETTINGS
)
SELECT ...;
EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. SELECT라도 운영 부하가 큰 쿼리는 스테이징 또는 저부하 시간에 측정해야 한다.
확인할 항목은 다음과 같다.
- 각 노드의
actual rows와 예상rows차이 loops가 비정상적으로 큰 노드- 조인 직후 행 수가 급증하는 지점
Rows Removed by Filtershared hit와shared read- 임시 파일을 의미하는 temp read/write
- Sort 또는 Hash의 메모리 사용과 디스크 spill 여부
- 새 부분 인덱스의 실제 사용 여부
실제 시간과 행 수는 loops당 평균으로 표시되는 항목이 있으므로 전체 작업량을 볼 때는 반복 횟수를 함께 본다. 상위 노드의 버퍼 수치에는 하위 노드 사용량이 포함되므로 각 노드 값을 단순 합산해서도 안 된다.
당시 운영 통계에서는 평균 실행 시간이 약 0.3초 수준이고 shared_blks_hit가 높으며 실제 읽기 블록은 거의 없는 모습도 확인했다. 이것은 캐시가 잘 활용된다는 뜻이지, 쿼리 구조가 최적이라는 뜻은 아니다. 호출 수가 많다면 0.3초도 누적 부하가 된다.
9. pg_stat_statements로 누적 비용 확인
단일 실행계획은 한 번의 측정이고, pg_stat_statements는 운영 중 누적 통계를 보여준다.
SELECT
queryid,
calls,
rows,
total_exec_time,
mean_exec_time,
shared_blks_hit,
shared_blks_read,
temp_blks_read,
temp_blks_written
FROM pg_stat_statements
WHERE query ILIKE '%route_master%'
ORDER BY total_exec_time DESC
LIMIT 20;
평균 시간만 보면 호출 빈도가 높은 쿼리를 놓칠 수 있다. calls, total_exec_time, mean_exec_time, 블록 읽기와 임시 파일을 같이 봐야 한다. 다른 사용자의 SQL 원문과 queryid를 보려면 권한이 필요할 수 있다.
누적 블록 수는 calls로 나눠 실행당 사용량도 함께 확인한다. shared_blks_read는 PostgreSQL이 공유 블록을 읽은 횟수이며 운영체제 캐시가 개입할 수 있으므로 물리 디스크 I/O 횟수와 완전히 같다고 단정하지 않는다. rows 역시 중간 조인 행이 아니라 최종 반환 또는 변경 행의 누계다.
적용 직후에는 짧은 구간만 보고 결론 내리지 않는다. 대표 트래픽이 누적된 뒤 같은 기준으로 비교한다. 통계 초기화는 전체 관측 기준을 바꾸므로 공동 운영 환경에서 임의로 실행하지 않는다.
10. 뷰와 머티리얼라이즈드 뷰를 먼저 만들 필요는 없다
일반 VIEW는 SQL을 저장해 읽기 쉽게 만들지만 결과를 미리 계산해 보관하지 않는다. 따라서 뷰로 감싼다는 이유만으로 행 증폭이나 집계 비용이 사라지지는 않는다.
MATERIALIZED VIEW는 결과를 저장하므로 조회는 빨라질 수 있지만 다음 비용이 생긴다.
- 새 데이터가 즉시 반영되지 않는다.
- 갱신 주기와
REFRESH비용을 관리해야 한다. - 실시간 “최근 10분” 조건과 잘 맞지 않을 수 있다.
먼저 원본 쿼리의 조인 범위와 집계 순서를 고치고 인덱스를 검증한다. 그래도 변하지 않는 대규모 집계가 반복될 때 머티리얼라이즈드 뷰를 검토하는 순서가 안전하다.
11. 최종 체크리스트
- [ ] 여러 1:N 테이블을 동시에 조인해 행이 곱해지지 않는가?
- [ ]
SUM값이 조인 때문에 반복 계산되지 않는가? - [ ] 각 하위 테이블을 최종 업무 키 단위로 먼저 집계했는가?
- [ ]
LEFT JOIN한 테이블의 조건을 WHERE에 두어 사실상 INNER JOIN으로 바꾸지 않았는가? - [ ] 서로 배타적인 상태 조건은
UNION ALL분리가 더 명확한가? - [ ]
COUNT(DISTINCT)가 실제로 필요한가? - [ ] 날짜와 timestamp/timestamptz 타입을 맞춰 비교하는가?
- [ ] 인덱스 선두 컬럼이 실제 동등 조건과 조인 키를 반영하는가?
- [ ] 부분 인덱스의 predicate가 쿼리 조건과 맞는가?
- [ ]
TRIM,CAST, 함수 적용이 일반 인덱스 사용을 막지 않는가? - [ ]
EXPLAIN (ANALYZE, BUFFERS)에서 actual rows·loops·buffer를 비교했는가? - [ ]
pg_stat_statements로 호출 수와 누적 실행 시간을 확인했는가?
마무리
복잡한 조회를 튜닝할 때 가장 먼저 볼 것은 “쿼리를 몇 번 실행하느냐”가 아니라 집계 전에 몇 행을 만들고 있느냐다. 데이터베이스 왕복을 줄이려고 모든 테이블을 한 번에 조인했더라도, 그 결과로 수백·수천 배의 중간 행이 생기면 오히려 더 느리고 집계까지 틀릴 수 있다.
이번 사례의 결론은 단순하다.
대상 노선을 먼저 줄이고, 정류장 시간과 예약을 노선 단위로 선집계한 뒤, 상태별 부분 인덱스를 붙이고 실행계획과 누적 통계로 검증한다.
인덱스는 마지막 처방이 아니라 쿼리 구조와 함께 설계해야 한다. 그리고 가장 좋은 SQL은 예쁘게 보이는 SQL이 아니라, 실제 데이터 분포에서 행 수와 I/O를 줄였다는 것을 실행계획으로 증명한 SQL이다.
