[PostgreSQL] work_mem와 정렬 spill — 큰 ORDER BY가 디스크로 새는 순간 잡아내기

설치·접속: PostgreSQL 설치와 접속

부제: ORDER BY·GROUP BY·DISTINCT가 유독 느린데, EXPLAIN에 "external merge Disk"가 찍혀 있을 때

-- 병렬 제거해 정렬 방식만 또렷이 본다
SET max_parallel_workers_per_gather = 0;

SET work_mem = '4MB';                 -- 기본값
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM m5_orders ORDER BY amount;

SET work_mem = '256MB';               -- 넉넉히
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM m5_orders ORDER BY amount;

-- work_mem=4MB : 디스크로 샌다
 Sort  (actual time=743..956  rows=2000000)
   Sort Method: external merge  Disk: 84592kB           ← 82MB를 디스크에 씀
   Buffers: ... temp read=21142 written=21182           ← temp = 임시파일 I/O
   Execution Time: 1011.516 ms

-- work_mem=256MB : 메모리 안에서 끝
 Sort  (actual time=633..875  rows=2000000)
   Sort Method: quicksort  Memory: 160862kB             ← 통째로 메모리 정렬
   Execution Time: 927.463 ms

work_mem은 정렬·해시 한 건이 쓸 수 있는 메모리 한도다. 정렬 대상이 이 한도를 넘으면 PostgreSQL은 데이터를 조각내 디스크 임시파일에 쓰고 나중에 병합한다 — 이게 Sort Method: external merge Disk이고, temp read/written 블록이 그 증거다. 82MB짜리 정렬을 4MB 한도로 돌리니 디스크를 21,000블록이나 왕복한다. work_mem을 키우면 quicksort Memory로 바뀌며 임시파일이 사라진다. 주의: work_mem은 커넥션·연산 단위라 100커넥션 × 정렬 2개 × 256MB = 50GB가 될 수 있다. 그래서 서버 전역으로 크게 잡지 말고, 무거운 배치 쿼리에서만 세션 단위로 올린다(SET work_mem). 진단 순서는 명확하다 — 느린 정렬을 만나면 EXPLAIN ANALYZE에서 Disk:와 temp를 먼저 확인한다.

이렇게도 쓴다

정렬 컬럼에 인덱스를 주면 정렬 자체가 사라진다(Index Scan이 순서대로 반환). (조합: 정렬 회피)

CREATE INDEX idx_m5_amount ON m5_orders(amount);
-- ORDER BY amount 가 Sort 없이 Index Scan

 

해시 집계(GROUP BY)도 같은 한도를 쓴다 — Batches가 늘면 해시가 샌 것. (조합: HashAggregate)

EXPLAIN (ANALYZE) SELECT user_id, count(*) FROM m5_orders GROUP BY user_id;

 

임시파일이 실제로 만들어지는지 로그로 잡는다. (조합: log_temp_files)

SET log_temp_files = 0;   -- 0 이상 크기의 temp 파일을 모두 로그

 

상위 N건만 필요하면 LIMIT으로 top-N heapsort를 유도해 메모리를 아낀다. (조합: LIMIT)

EXPLAIN (ANALYZE) SELECT * FROM m5_orders ORDER BY amount LIMIT 100;

 

세션에서만 올리고 쿼리 끝나면 되돌린다 — 전역 확대는 피한다. (원칙: 세션 단위)

SET work_mem='256MB';  -- 무거운 배치 직전
RESET work_mem;        -- 끝나면 원복

 

정렬 방식과 사용 메모리를 한 줄로 확인한다. (조합: EXPLAIN ANALYZE)

EXPLAIN (ANALYZE) SELECT DISTINCT amount FROM m5_orders;