[PostgreSQL] 쿼리 성능 최적화 과정

PostgreSQL에서 특정 쿼리의 성능을 개선하기 위해 인덱스 추가, 서브쿼리 최적화, JOIN 최적화 등의 작업을 수행한 과정을 공유합니다.

---

1. 성능 개선 전 쿼리

다음은 성능 개선 전의 PostgreSQL 쿼리입니다.

WITH data_main AS (
    SELECT model_type, event_time, object_id, performance_score, param_details,
           similarity_metric, similarity_value, prediction_name, prediction_value,
           category_name, category_percentage
    FROM sample_table
    WHERE event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
      AND object_id = 'OBJ123'
      AND model_revision = (SELECT MAX(model_revision) FROM sample_table)
),
data_ml AS (
    SELECT event_time, object_id, performance_score
    FROM data_main
    WHERE model_type = 'ML'
      AND performance_score > 0.8
    ORDER BY performance_score DESC
),
data_rca AS (
    SELECT event_time, object_id, param_details, similarity_metric, similarity_value,
           prediction_name, prediction_value, category_name, category_percentage
    FROM data_main
    WHERE model_type = 'RCA'
)
SELECT ml.event_time, ml.object_id, ml.performance_score,
       rca.param_details, rca.similarity_metric, rca.similarity_value,
       rca.prediction_name, rca.prediction_value, rca.category_name, rca.category_percentage
FROM data_ml ml
JOIN data_rca rca
ON ml.event_time = rca.event_time AND ml.object_id = rca.object_id
ORDER BY ml.performance_score DESC
LIMIT 1;

실행 계획 분석 결과

• Seq Scan(Sequential Scan) 발생
• ORDER BY 시 추가 정렬 비용 발생
• 인덱스를 일부 사용하지만 model_revision과 performance_score가 인덱스에 포함되지 않음

---

2. 성능 개선을 위한 접근 전략

2.1 복합 인덱스 추가

쿼리의 WHERE 및 ORDER BY 절에 자주 등장하는 컬럼을 포함한 복합 인덱스를 생성합니다.

CREATE INDEX idx_sample_table_optimized
ON sample_table (object_id, event_time, model_revision, model_type, performance_score);

2.2 서브쿼리 최적화

WITH 서브쿼리를 줄이고 IN 조건을 활용하여 불필요한 데이터 로드를 최소화합니다.

SELECT event_time, object_id, performance_score, param_details, similarity_metric, similarity_value,
       prediction_name, prediction_value, category_name, category_percentage
FROM sample_table
WHERE event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
  AND object_id = 'OBJ123'
  AND model_revision = (SELECT MAX(model_revision) FROM sample_table)
  AND model_type IN ('ML', 'RCA')
ORDER BY performance_score DESC
LIMIT 1;

2.3 인덱스 정렬 최적화

ORDER BY performance_score DESC가 자주 사용되므로 내림차순 인덱스를 생성합니다.

CREATE INDEX idx_sample_table_score_desc
ON sample_table (performance_score DESC);

---

3. 성능 최적화 결과

EXPLAIN ANALYZE로 실행 계획을 비교해 본 결과, 쿼리 실행 시간이 40% 이상 단축되었습니다.

비교 전후 실행 계획 예시

• Before: actual time=500.123..1500.456ms
• After: actual time=200.567..850.678ms

Time 값은 낮을수록 좋다

• PostgreSQL의 EXPLAIN ANALYZE에서 actual time은 연산 시작 시각과 종료 시각을 나타내며, Time 값이 낮을수록 성능이 개선된 것을 의미합니다.

---

4. 성능 개선 팁

• 인덱스를 추가할 때는 자주 사용하는 컬럼과 정렬 조건을 고려하여 복합 인덱스를 생성
• 불필요한 서브쿼리와 WITH 절 제거로 쿼리 최적화
• EXPLAIN ANALYZE를 통해 인덱스 적용 전후 성능 차이를 반드시 확인