PostgreSQL 쿼리 튜닝: INSERT INTO → CTAS로 5배 성능 향상

※ Converting INSERT INTO to CTAS for PostgreSQL Query Optimization

 

안녕하세요. 듀스트림입니다.

 

오늘은 쿼리 튜닝 사례를 하나 가져왔습니다.

기존 INSERT INTO 문으로 만들어져 있는 쿼리를 CTAS(Create Table As Select) + INSERT INTO로 병렬 처리 가능하게 최적화하여 성능 개선한 사례입니다.

 

주요 포인트는 다음과 같습니다.

1. PostgreSQL은 병렬 INSERT를 지원하지 않습니다.

2. CTAS는 병렬 처리를 지원합니다. (SELECT ... INTO도 병렬 지원)

3. max_parallel_workers, max_parallel_workers_per_gather 파라미터가 적절하게 설정되어 있어야 합니다.

4. 상황에 따라 parallel_setup_cost, parallel_tuple_cost 파라미터 수정이 필요할 수 있습니다.

# 파라미터 설명

max_parallel_workers: 클러스터 전체 워커 수
max_parallel_workers_per_gather: 단일 Gather에 대한 워커 수 제한
parallel_setup_cost: 병렬 작업 시작 비용 (작을수록 더 자주 병렬 Gather 실행)
parallel_tuple_cost: 병렬로 전송되는 튜플의 비용

 

어떻게 최적화했는지 예시로 살펴보겠습니다.

---

AS-IS

-- INSERT INTO
EXPLAIN(BUFFERS, ANALYZE)
INSERT INTO target_table (value_category, category_count)
SELECT
    CASE 
        WHEN value >= 100 THEN 'high'
        WHEN value BETWEEN 50 AND 99 THEN 'medium'
        ELSE 'low'
    END AS value_category,
    COUNT(*) AS category_count
FROM partitioned_source_table
WHERE id BETWEEN 1 AND 100000000
GROUP BY value_category;

--- QUERY PLAN ---
Insert on target_table  (cost=4082261.00..4082267.00 rows=0 width=0) (actual time=27831.061..27831.067 rows=0 loops=1)
  ->  Subquery Scan on "*SELECT*"  (cost=4082261.00..4082267.00 rows=200 width=46) (actual time=27830.914..27830.924 rows=3 loops=1)
                          ...
Planning Time: 0.460 ms
Execution Time: 27831.127 ms

• 단일 워커가 Subquery Scan을 통해 전체 작업을 수행합니다.

 

TO-BE

-- Step1: CTAS
EXPLAIN(BUFFERS, ANALYZE)
CREATE TEMP TABLE temp_source_data AS
SELECT
    CASE 
        WHEN value >= 100 THEN 'high'
        WHEN value BETWEEN 50 AND 99 THEN 'medium'
        ELSE 'low'
    END AS value_category,
    COUNT(*) AS category_count
FROM partitioned_source_table
WHERE id BETWEEN 1 AND 100000000
GROUP BY value_category;

--- QUERY PLAN ---
Finalize GroupAggregate  (cost=2187438.89..2187491.06 rows=200 width=40) (actual time=9960.556..9961.896 rows=3 loops=1)
  Group Key: (CASE WHEN (partitioned_source_table.value >= 100) THEN 'high'::text WHEN ((partitioned_source_table.value >= 50) AND (partitioned_source_table.value <= 99)) THEN 'medium'::text ELSE 'low'::text END)
  Buffers: shared hit=26019 read=806258
  ->  Gather Merge  (cost=2187438.89..2187485.56 rows=400 width=40) (actual time=9960.549..9961.884 rows=9 loops=1)
        Workers Planned: 2
        Workers Launched: 2
                    ...
Planning:
  Buffers: shared hit=650
Planning Time: 1.857 ms
Execution Time: 9963.891 ms

• 병렬 적용 확인: Gather Merge가 적용되었고, Workers Planned: 2, Workers Launched: 2가 표시되었습니다.

+ Gather Merge는 ORDER BY 또는 정렬 작업이 필요할 때 사용됩니다. (이 경우 Sort Key가 명시되어 있습니다.)
++ 일반적인 병렬 Gather의 경우, 단순히 Gather로 표시됩니다.

 

• 27초에서 9.9초로 성능이 개선되었습니다.

 

-- Step2: ANALYZE
ANALYZE temp_source_data; --INSERT 작업의 성능을 높이기 위해

 

-- Step3: TARGET_TABLE INSERT
EXPLAIN(BUFFERS, ANALYZE)
INSERT INTO target_table (value_category, category_count)
SELECT value_category, category_count
FROM temp_source_data;

--- QUERY PLAN ---
Insert on target_table  (cost=0.00..25.00 rows=0 width=0) (actual time=0.175..0.176 rows=0 loops=1)
  Buffers: shared hit=2 dirtied=1 written=1, local hit=1
  ->  Seq Scan on temp_source_data  (cost=0.00..25.00 rows=1200 width=46) (actual time=0.008..0.009 rows=3 loops=1)
        Buffers: local hit=1
Planning:
  Buffers: shared hit=6
Planning Time: 0.105 ms
Execution Time: 0.198 ms

---

예시에서는 약 3배 정도 향상되었지만,
운영 환경에서는 병렬도를 10으로 설정하여, 쿼리 튜닝 후 약 5배 이상의 성능 개선 효과를 확인한 사례입니다.

 

앞으로도 유익한 쿼리 튜닝 정보를 지속적으로 포스팅하겠습니다.