쿼리를 짤 때 대부분의 경우는 WHERE절에 a='value'와 같이 단일 조건을 걸어 조회를 하거나 복수개의 파라미터가 필요한 경우 a IN ('value1', 'value2')의 형태로 IN절을 사용한다. 그러나 IN절에 들어갈 파라미터가 한두개가 아니라 수백개 이상의 문자열이 들어간다면... 다음과 같은 문제들이 발생할 수 있다.:
- SQL 문장 길이가 비대해져서 파싱/플랜 캐싱 효율이 떨어짐
- 실행계획 최적화가 어렵고, Index 활용률이 낮아짐
- JDBC/Maven 코드에서 문자열 합치기가 번거롭고, SQL injection 위험 증가
그러나 그럼에도 불구하고 릴레이션이 모호하거나 정규화 컬럼으로 취합되지 않는 경우 수백개의 파라미터를 한꺼번에 넣어 쿼리를 작성해야만 할 때가 있다.
그래서 성능 최적화된 구조를 위해 여러가지 방법들을 고려해봤다.:
1. 임시 테이블 (Temporary Table) / UNLOGGED 테이블 활용
다량의 파라미터 자체를 테이블로 취급되도록 만들어 기존쿼리와 조인하는 방식이다.
temp_table처럼 임시 테이블로 insert 해놓고, 메인 테이블과 JOIN 또는 EXISTS로 조회한다.
예시:
CREATE TEMP TABLE temp_ids (id text);
INSERT INTO temp_ids VALUES ('111'), ('222'), ('333');
SELECT t.*
FROM target_table t
JOIN temp_ids ti ON t.id = ti.id;장점: 인덱스 활용 최적, 쿼리 짧음, 성능 안정적
단점: 매번 insert하는 오버헤드 → JDBC batch insert로 최소화 가능
2. 배열(Array) 파라미터 전달 (ANY / = ANY())
Java 코드에서 List을 PostgreSQL 배열로 바인딩 후 = ANY(:arrayParam)으로 검색하는 방식이다.
예시:
SELECT *
FROM target_table
WHERE id = ANY(:id_array);Java (JDBC) 예시:
> PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM target_table WHERE id = ANY(?)");
Array array = conn.createArrayOf("text", strList.toArray());
ps.setArray(1, array);장점: SQL 문장 짧고, 파라미터 안전하게 전달 가능
단점: 대량 배열이면 Query planner가 Hash Semi Join으로 풀 때 성능 저하 가능 → 인덱스 조건 비교 불리할 수 있음
3. JSON/CSV 파라미터 파싱
문자열을 하나의 JSON/CSV로 묶어 파라미터 1개만 전달 후, DB 내에서 unnest
예시:
SELECT *
FROM target_table
WHERE id IN (
SELECT jsonb_array_elements_text(:json_param)
);장점: JDBC 전달이 간단함 (문자열 1개만 세팅)
단점: JSON parsing 비용 있음 → 중소규모 데이터에 적합
4. UNNEST 함수 활용
텍스트 배열을 파라미터로 바인딩하여 unnest 사용으로
SELECT * FROM main_table
WHERE id = ANY(SELECT unnest(?::text[]))장점: SQL 단일 실행 (temp table 필요 없음)
단점: SQL이 길어질 수 있음 (수천 건 넘어가면 비효율)
검토 및 최적화
여러가지 풀어가는 방식이 있지만 들어오느 파라미터의 대략적인 데이터 개수에 따라 효율적인 방법은 달라진다.
해당 쿼리의 경우 Java Maven 환경에서 PostgreSQL DB를 사용하고, 한번의 요청에 100건 정도의 파라미터를 받아 실시간 API를 조회하는 용도였지만 100개 이상의 문자열을 IN 절에 그대로 넣는 건 매 요청마다 SQL 파싱/플랜 최적화 오버헤드가 커져서 성능 저하가 발생할 수 있기 때문에 사전에 응답 속도(지연 최소화) + 안정성 확보에 대한 검토와 최적화가 필요했다.
결국 아래와 같은 많은 이점으로 ANY+UNNEST 함수 활용을 하는 방법을 사용했다.
- PostgreSQL 옵티마이저가 UNNEST를 특별히 최적화 함.
- 배열을 임시 테이블처럼 빠르게 펼침
- 인덱스 스캔과 효율적으로 결합
- 파라미터를 배열로 만들면 연속된 메모리 공간에 저장하므로 메모리 효율적
- IN절의 개별 파라미터보다 메모리 사용량 적음
- 대량 데이터(100개 이상) 처리 시 차이 극명
실제로 같은 ANY함수를 사용해도 UNNEST를 사용하는것과 사용하지 않는것에서 성능에 큰 차이가 있었다.
단, 이점에도 정리되어 있듯 UNNEST 대한 성능 최적화는 PostgreSQL의 옵티마이저에만 유효하고 다른 데이터베이스에서는 적용되지 않으므로 다른 DB의 경우 다른 방식의 적용 방법이 효율적일 수 있다.
