PostgreSQL 쿼리 튜닝 | EXPLAIN ANALYZE, Buffers, Nested Loop·Hash·Merge

소프트웨어 개발/데이터·통계

PostgreSQL 쿼리 튜닝 | EXPLAIN ANALYZE, Buffers, Nested Loop·Hash·Merge

boradora 2026. 8. 4. 09:04

작업일2026. 07

EXPLAIN ANALYZE 실행계획 Seq Scan 복합 인덱스 Hash Join Materialized View 안티패턴 pg_stat_statements

인덱스를 걸었는데도 쿼리가 그대로 느릴 때가 있습니다. 인덱스가 없어서가 아니라 만들어 둔 인덱스를 쿼리가 안 타서 생기는 일입니다. 탔는지 안 탔는지는 추측할 필요 없이 실행계획에 그대로 적혀 있습니다.

이 쿼리가 왜 느린지 어떻게 알 수 있을까요?
실행계획에서 버려진 행의 개수를 먼저 확인합니다. 5만 행을 읽어 4만 행을 버리고 있다면 대부분의 비용이 불필요한 행을 읽는 데 사용되고 있는 것입니다.

느린 쿼리를 고치는 순서는 정해져 있습니다. 어떤 쿼리가 느린지 찾고, 실행계획으로 왜 느린지 확인하고, 원인에 맞는 방법으로 고칩니다. 실행 계획을 확인하지 않고 인덱스를 추가하면 쓰기 비용만 증가하고 실행 계획은 그대로인 경우가 있습니다.

실행계획

실행계획은 옵티마이저가 쿼리를 실행하기 전에 정한 실행 방법입니다. 어떤 순서로 테이블을 읽을지, 어떤 인덱스를 쓸지, 어떤 조인 방식을 택할지가 여기 적혀 있습니다.

명령	하는 일	주의
`EXPLAIN`	추정 계획만 보여줍니다. 쿼리를 실행하지 않습니다	추정값이라 실제와 다를 수 있습니다
`EXPLAIN ANALYZE`	실제로 실행하고 걸린 시간과 행 수를 측정합니다	DML이면 실제로 반영됩니다
`EXPLAIN (ANALYZE, BUFFERS)`	메모리에서 읽었는지 디스크에서 읽었는지까지 보여줍니다	캐시 히트율을 볼 때 씁니다

EXPLAIN ANALYZE는 쿼리를 진짜로 실행합니다. UPDATE나 DELETE에 붙이면 데이터가 실제로 바뀝니다. DML을 측정할 때는 트랜잭션으로 감싸고 마지막에 롤백합니다.

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'DONE' WHERE id = 1;
ROLLBACK;                -- 측정만 하고 되돌립니다

플랜을 읽는 방향

플랜은 트리입니다. -> 로 들여쓴 줄이 자식이고, 자식이 먼저 실행됩니다. 그래서 가장 깊이 들여쓴 줄부터 위로 올라가며 읽습니다.

실행 순서는 아래에서 위로, 결과는 위로 전달됩니다

④ Limit  100개만 반환

③ Sort  salary 내림차순

② Hash Join  departments · jobs 를 붙임

① Seq Scan on employees  여기가 문제입니다

한 줄에 들어 있는 것

모든 노드는 같은 형식을 씁니다. 괄호가 두 개인데 앞은 예측, 뒤는 실측입니다.

Limit  (cost=2386.77..2387.02 rows=100 width=30)
       (actual time=28.278..28.296 rows=100 loops=1)

값	뜻	어떻게 보나
`cost=A..B`	A는 첫 행이 나올 때까지, B는 전부 끝날 때까지의 추정 비용	단위가 시간이 아니라 상대적인 값입니다. 플랜끼리 비교할 때만 씁니다
`rows` (앞)	몇 행이 나올지 추정	뒤의 실제 `rows`와 비교합니다
`width`	행 하나의 평균 바이트	`SELECT *`로 바꾸면 이 값이 커집니다
`actual time=A..B`	실제로 걸린 밀리초	여기가 실측값입니다. 어느 노드에서 시간이 뛰는지 봅니다
`rows` (뒤)	실제로 나온 행 수	추정과 몇 배 이상 벌어지면 `ANALYZE`가 필요합니다
`loops`	이 노드가 반복 실행된 횟수	1이 아니면 시간을 곱해야 합니다. Nested Loop 안쪽에서 커집니다

loops를 놓치면 시간을 잘못 읽습니다. actual time은 1회 실행분입니다. actual time=0.5..0.8 loops=3000 이라면 실제로 쓴 시간은 0.8ms가 아니라 2,400ms입니다.

실제 사례로 읽어보기

입사 1년 이내의 재직자를 연봉순 상위 100명 뽑는 쿼리입니다. 직원 테이블에 5만 행이 있고, 부서와 직무 테이블을 붙여서 조회합니다.

SELECT e.employee_id, e.salary, d.department_name, j.job_title
  FROM employees e
  JOIN departments d ON d.department_id = e.department_id
  JOIN jobs        j ON j.job_id = e.job_id
 WHERE e.status = 'ACTIVE'
   AND e.hire_date >= CURRENT_DATE - INTERVAL '365 days'
 ORDER BY e.salary DESC
 LIMIT 100;

튜닝 전

[여기에 스크린샷 : q4_v1_before.png]

맨 아래 줄부터 올라가며 읽습니다. 가장 깊은 곳에 이 줄이 있습니다.

->  Seq Scan on employees e  (cost=0.00..1960.00 rows=9551 width=24)
                             (actual time=0.018..21.028 rows=9567 loops=1)
      Filter: ((status = 'ACTIVE'::text)
               AND (hire_date >= (CURRENT_DATE - '365 days'::interval)))
      Rows Removed by Filter: 40433
      Buffers: shared hit=960

여기서 세 가지가 보입니다.

Seq Scan입니다. 인덱스를 안 타고 employees 테이블을 처음부터 끝까지 읽었습니다.
Rows Removed by Filter: 40433. 조건에 안 맞아서 버린 행이 4만 433개입니다. 통과한 것은 9,567개입니다. 5만 행을 읽어 5분의 1만 남았습니다.
이 노드에서 21.028ms를 썼습니다. 전체가 28.353ms이니 전체 실행 시간의 대부분이 이 노드에서 소비됩니다.

Rows Removed by Filter가 크면 조건 컬럼에 인덱스를 적용할 수 있는지 확인합니다. 이 값은 읽었지만 조건을 만족하지 않아 필터 단계에서 제거된 행 수입니다. 통과한 행보다 버린 행이 훨씬 많다면, 애초에 그 행들을 읽지 않을 방법이 있다는 뜻입니다.

나머지 줄도 확인합니다. 위로 올라가면 Hash Join이 두 겹 있습니다.

->  Hash Join  (cost=8.40..2021.74 rows=9551 width=30)
               (actual time=0.223..26.226 rows=9567 loops=1)
      Hash Cond: (e.job_id = j.job_id)
...
->  Hash  (cost=4.00..4.00 rows=200 width=12)
      Buckets: 1024  Batches: 1  Memory Usage: 17kB
      ->  Seq Scan on departments d  (rows=200)

departments가 200행, jobs가 40행입니다. 이 정도로 작은 테이블은 Seq Scan이 비용이 가장 낮게 계산되는 경우가 많습니다. 전부 메모리에 올려 해시 테이블을 만드는 편이 인덱스를 타는 것보다 빠릅니다. Batches: 1이니 메모리에 다 들어갔고, 디스크로 넘치지도 않았습니다.

Seq Scan이 있다고 무조건 나쁜 것은 아닙니다. 작은 테이블이거나 전체의 상당 부분을 읽어야 하는 쿼리라면 순차 읽기가 더 빠릅니다. 문제가 되는 것은 읽은 행 대부분이Filter 단계에서 제거되는경우입니다.

무엇을 걸어야 하나

WHERE 조건이 status와 hire_date 두 개이고 AND로 묶여 있습니다. AND 조건이니 복합 인덱스가 맞습니다. 여기에 ORDER BY e.salary까지 있으니 salary도 인덱스에 포함시킵니다.

CREATE INDEX idx_emp_hire_status_salary
    ON employees (hire_date, status, salary);

ANALYZE employees;   -- 통계를 갱신해야 플래너가 새 인덱스를 고려합니다

복합 인덱스는 등호 조건을 앞에, 범위 조건을 뒤에 둡니다. status = 'ACTIVE'가 등호, hire_date >=가 범위이니 원칙대로면 (status, hire_date) 순입니다. 다만 status는 값 종류가 적어 선택도가 낮고, 이 데이터에서는 날짜로 좁히는 폭이 더 컸습니다. 순서 원칙은 출발점이고, 최종 판단은 두 순서를 다 만들어 실행계획을 비교하는 것입니다.

튜닝 후

[여기에 스크린샷 : q4_v1_after.png]

같은 쿼리를 한 글자도 고치지 않았는데 플랜이 달라졌습니다. 가장 깊은 곳이 이렇게 바뀌었습니다.

->  Bitmap Heap Scan on employees e  (cost=262.55..1412.09 rows=9477 width=24)
      Recheck Cond: ((hire_date >= (CURRENT_DATE - '365 days'::interval))
                     AND (status = 'ACTIVE'))
      Heap Blocks: exact=907
      Buffers: shared hit=948
      ->  Bitmap Index Scan on idx_emp_hire_status_salary
            (cost=0.00..260.19 rows=9477)
            Index Cond: ((hire_date >= ...) AND (status = 'ACTIVE'))
            Buffers: shared hit=41

바뀐 점을 하나씩 짚어보겠습니다.

Seq Scan이 Bitmap Heap Scan으로 바뀌었습니다. 인덱스로 읽을 행의 위치를 먼저 찾고, 그 위치만 테이블에서 읽습니다.
Rows Removed by Filter가 사라졌습니다. 조건에 맞는 행만 인덱스로 찾아 접근하므로 불필요한 행을 읽지 않습니다.
Filter가 Recheck Cond로 바뀌었습니다. 비트맵이 페이지 단위로 뭉개질 수 있어서 읽은 뒤 조건을 한 번 더 확인합니다. Heap Blocks: exact=907은 뭉개진 부분 없이 정확히 기록됐다는 뜻입니다.
인덱스를 읽는 데 41 페이지를 읽어 접근할 힙 블록을 결정했습니다. 이것으로 테이블에서 읽을 907 블록을 정했습니다.

항목	튜닝 전	튜닝 후
employees 접근 방식	`Seq Scan`	`Bitmap Heap Scan`
버린 행	40,433	0
해당 노드 소요	21.028 ms	인덱스 스캔 포함해 크게 감소
Buffers (전체)	shared hit=963	shared hit=951
Execution Time	28.353 ms	17.633 ms

28.353ms에서 17.633ms로 약 38% 줄었습니다.

남아 있는 것

여기서 짚고 갈 것이 있습니다. Sort 노드는 그대로 남아 있습니다.

->  Sort  (cost=1835.61..1859.30 rows=9477 width=30)
          (actual time=17.545..17.560 rows=100 loops=1)
      Sort Key: e.salary DESC
      Sort Method: top-N heapsort  Memory: 36kB

인덱스에 salary를 넣었는데도 정렬이 사라지지 않았습니다. 이유는 선두 컬럼에 범위 조건이 사용되면 인덱스 순서만으로 전체 정렬 순서를 보장할 수 없기때문입니다.

(hire_date, status, salary) 순서에서 hire_date에 걸린 조건이 >= 입니다. 등호가 아니라 범위이므로 여러 날짜 구간이 결과에 포함되고, 각 구간 안에서만 salary가 정렬돼 있습니다. 구간을 넘나들면 순서가 이어지지 않으니 인덱스가 전체 정렬 순서를 보장하지 못합니다. 그래서 데이터베이스는 별도의 Sort를 붙입니다.

복합 인덱스에서 범위 조건 뒤의 컬럼은 정렬에 쓸 수 없습니다. 앞에서 "등호를 앞에, 범위를 뒤에" 두라고 한 규칙이 여기서 한 번 더 나옵니다. ORDER BY까지 인덱스로 해결하려면 정렬 컬럼을 선두로 올려야 합니다.

실제로 인덱스를 이렇게 바꾸면 Sort 노드가 사라집니다.

-- 정렬 컬럼을 선두로, 값 종류가 적은 status 는 부분 인덱스 조건으로
CREATE INDEX idx_emp_salary_hire
    ON employees (salary DESC, hire_date)
 WHERE status = 'ACTIVE';

status는 값이 두 종류뿐이라 인덱스 컬럼으로 두면 좁혀지는 폭이 작습니다. WHERE 절로 옮겨 부분 인덱스로 만들면 인덱스 크기도 줄고, 선두 자리를 정렬 컬럼에 쓸 수 있습니다. 이 구성에서는 Sort가 완전히 없어지고 약 30배까지 빨라졌습니다.

앞의 Sort Method가 top-N heapsort였던 덕에 그나마 부담이 작았습니다. LIMIT 100이 있어 9,477개를 전부 정렬하지 않고 상위 100개만 유지했고, 메모리도 36kB만 썼습니다. LIMIT이 없었다면 훨씬 무거워집니다.

`Sort Method`	뜻	판단
`top-N heapsort`	`LIMIT`이 있어 상위 N개만 유지	좋습니다. 전체 정렬을 피했습니다
`quicksort`	메모리 안에서 전체 정렬	괜찮습니다
`external merge Disk: ...`	메모리가 부족해 디스크에서 정렬	느립니다. `work_mem`을 올리거나 정렬을 없애야 합니다

Buffers 읽기

BUFFERS 옵션을 붙이면 각 노드가 몇 페이지를 어디서 읽었는지 나옵니다. 페이지 하나는 기본 8kB입니다.

표기	뜻	판단
`shared hit`	메모리 캐시에서 읽음	빠릅니다. 이 비율이 높아야 합니다
`shared read`	디스크에서 읽음	느립니다. 반복 실행해도 크면 캐시가 부족한 것입니다
`temp read / written`	임시 파일 사용	정렬이나 해시가 메모리에 안 들어갔다는 뜻입니다

위 사례는 튜닝 전후 모두 hit만 있고 read가 없습니다. 데이터가 이미 캐시에 다 올라와 있었다는 뜻입니다. 측정할 때는 두 번 이상 돌려 캐시가 채워진 뒤의 값을 보는 편이 실제 운영 상황에 가깝습니다.

노드 타입

플랜에 나오는 노드 이름만 알아도 어디가 문제인지 좁혀집니다.

노드	의미	판단	조치
`Index Only Scan`	인덱스만으로 처리. 테이블 접근 없음	가장 좋음	커버링 인덱스가 걸린 상태
`Index Scan`	인덱스로 찾고 테이블에서 나머지를 읽음	좋음	인덱스 순서가 유지됩니다
`Bitmap Heap Scan`	비트맵을 만든 뒤 블록 순서로 접근	양호	대상 행이 많을 때 유리. 정렬은 유지되지 않습니다
`Seq Scan`	테이블 전체를 순차로 읽음	경우에 따라	작은 테이블이면 정상. `Rows Removed by Filter`가 크면 인덱스 검토
`Hash Join`	해시 테이블을 만들어 조인	대용량에 적합	`Batches`가 1보다 크면 디스크로 넘친 것
`Nested Loop`	바깥 행마다 안쪽을 반복 탐색	소규모에 적합	`loops`가 커지면 급격히 느려집니다
`Merge Join`	양쪽을 조인 키로 정렬한 뒤 맞물려 병합	정렬돼 있을 때만	아래에 `Sort`가 붙으면 Hash Join보다 불리합니다
`Sort`	결과를 정렬	확인 필요	`Index Scan`으로 순서를 얻으면 없앨 수 있습니다

조인 방식 세 가지

옵티마이저는 조인 방식도 비용으로 고릅니다. SQL을 한 글자도 안 바꿔도 양쪽 입력의 행 수, 조인 키 인덱스 유무, work_mem 크기, 통계 정확도에 따라 Nested Loop · Hash Join · Merge Join 중 다른 것이 선택됩니다. 플랜에 찍힌 조인 노드가 왜 그것인지 알아야 Batches나 loops 같은 숫자를 읽을 수 있습니다.

구분	Nested Loop	Hash Join	Merge Join
동작	바깥(outer) 행 하나마다 안쪽(inner)을 반복 탐색	작은 쪽으로 해시 테이블을 만들고(build) 큰 쪽을 흘리며 대조(probe)	양쪽을 조인 키로 정렬한 뒤 지퍼처럼 맞물려 병합
비용	O(N × M). inner 조인 키에 인덱스가 있으면 N × log M	O(N + M). 해시 테이블 생성 비용이 한 번	정렬돼 있으면 O(N + M), 아니면 정렬 비용 추가
유리할 때	outer 결과가 아주 적고 inner 조인 키에 인덱스가 있을 때	양쪽이 다 크고 등가(=) 조인이며 해시가 메모리에 들어갈 때	양쪽이 크고 조인 키로 이미 정렬(인덱스 순서)돼 있을 때
불리할 때	outer 행 수가 늘면 반복 횟수가 그대로 곱해집니다	`work_mem`이 부족하면 배치가 쪼개져 디스크로 넘칩니다	정렬 결과가 메모리를 넘기면 디스크 정렬로 떨어집니다
가능한 조건	등가 · 비등가 · 범위 조인 전부	등가(=) 조인만	등가 및 범위 비교 (정렬 순서 필요)
플랜 표기	`Nested Loop` 아래 `loops=N`인 Index Scan	`Hash Join` + `Hash (Buckets/Batches/Memory Usage)`	`Merge Join` + 양쪽 `Sort` 또는 Index Scan
튜닝 지점	outer를 줄이는 필터, inner 조인 키 인덱스	`work_mem`을 올려 `Batches: 1` 유지	조인 키 인덱스로 `Sort` 노드 자체를 제거

세 방식을 강제로 바꿔가며 같은 쿼리를 측정해보면 숫자가 이렇게 갈립니다. 주문 9,590건과 주문상품 28,815건을 조인해 매출을 합산하는 쿼리이고, enable_hashjoin·enable_mergejoin·enable_nestloop를 꺼서 방식을 지정했습니다.

SET enable_hashjoin = off;
SET enable_mergejoin = off;     -- 둘 다 끄면 Nested Loop 만 남습니다
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_hashjoin; RESET enable_mergejoin;

조인 방식	Execution Time	Buffers (hit)
Hash Join (옵티마이저 기본 선택)	6.1 ms	291
Nested Loop 강제	3.7 ms	3,758
Merge Join 강제	5.6 ms	348

Nested Loop가 시간은 제일 짧은데 Buffers는 12.9배입니다. 이 데이터는 전부 shared_buffers에 올라와 있어서 페이지를 3,758번 읽어도 전부 메모리 적중이라 빨라 보입니다. 디스크를 읽는 환경이었다면 그 반복 액세스가 그대로 랜덤 I/O 비용이 됩니다. 실행 시간만으로 조인 방식을 비교하면
메모리 캐시의 영향을 구분할 수 없습니다.가 이 표에 그대로 나옵니다.

옵티마이저가 Hash Join을 고른 것은 실행시간이 아니라 읽을 페이지 수를 기준으로 계산했기 때문입니다. 플랜에서 조인 노드를 볼 때 확인할 것은 방식 이름이 아니라 그 아래 붙은 숫자입니다. Nested Loop면 loops, Hash Join이면 Batches와 Memory Usage, Merge Join이면 Sort 노드의 유무입니다.

인덱스를 무력화하는 패턴

위 사례는 인덱스가 없어서 안 탄 경우였습니다. 그런데 인덱스가 있는데도 안 타는 경우가 더 자주 생깁니다. 인덱스는 컬럼의 값이 그대로 있을 때만 쓸 수 있습니다.

컬럼에 함수를 씌우는 경우

-- 인덱스를 못 씁니다 : 컬럼에 함수를 적용했습니다
WHERE YEAR(created_at) = 2026

-- 인덱스를 씁니다 : 컬럼은 그대로 두고 범위로 바꿉니다
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'

대소문자를 무시한 비교처럼 함수를 꼭 써야 하는 경우도 있습니다. 그때는 쿼리를 고치는 대신 함수를 적용한 결과에 인덱스를 겁니다.

CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';   -- 인덱스 사용

타입이 다른 값을 비교하는 경우

user_id가 INT인데 문자열로 비교하면, 데이터베이스가 컬럼 쪽을 변환하면서 인덱스를 못 씁니다. 따옴표 하나 때문에 전체 스캔이 일어납니다.

WHERE user_id = '12345'    -- 타입 변환이 일어나 인덱스를 못 씁니다
WHERE user_id = 12345      -- 인덱스를 씁니다

LIKE 앞에 와일드카드가 붙는 경우

B-Tree 인덱스는 앞에서부터 정렬돼 있어서 앞부분이 정해져야 범위를 좁힐 수 있습니다. 앞에 %가 붙으면 시작점을 정할 수 없어 전부 읽습니다.

WHERE name LIKE '%길동%'    -- B-Tree 인덱스 사용 불가
WHERE name LIKE '홍%'       -- 앞이 고정이라 인덱스 사용 가능

중간 검색이 꼭 필요하면 pg_trgm 확장에 GIN 인덱스를 씁니다. 문자열을 세 글자 단위로 쪼개 색인하기 때문에 가운데 검색에도 인덱스가 걸립니다.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);

SELECT * FROM users WHERE name LIKE '%길동%';   -- 이제 인덱스를 씁니다

앞이 고정된 LIKE인데도 인덱스를 안 타는 경우가 있습니다. text 컬럼의 기본 B-Tree 인덱스는 데이터베이스 로케일이 C가 아니면 LIKE에 쓰이지 않습니다. 로케일에 따라 문자 정렬 순서가 달라져서 인덱스 순서와 LIKE가 기대하는 순서가 어긋나기 때문입니다. en_US.UTF-8 환경에서 실제로 겪었고, text_pattern_ops를 지정해 해결했습니다.

-- 로케일과 무관하게 LIKE 접두사 검색에 쓰이는 인덱스
CREATE INDEX idx_emp_email_pattern
    ON employees (lower(email) text_pattern_ops);

SELECT * FROM employees WHERE lower(email) LIKE 'user123%';

인덱스를 만들었는데 플랜이 그대로라면 opclass와 로케일을 확인해봐야 합니다. SHOW lc_collate; 로 현재 로케일을 볼 수 있습니다.

OR 조건에 복합 인덱스를 거는 경우

위 사례는 AND였기 때문에 복합 인덱스가 맞았습니다. OR은 반대입니다.

WHERE department_id = 10
   OR job_id IN (3, 4, 5)

(department_id, job_id) 복합 인덱스는 앞 컬럼으로 좁힌 뒤 그 안에서 뒤 컬럼을 보는 구조입니다. OR은 둘 중 하나만 맞아도 되니 앞 컬럼으로 좁히는 것 자체가 성립하지 않습니다. 각 컬럼에 단일 인덱스를 따로 만들면 플래너가 두 인덱스를 각각 탐색한 뒤 BitmapOr로 합칩니다.

CREATE INDEX idx_emp_department_id ON employees (department_id);
CREATE INDEX idx_emp_job_id        ON employees (job_id);
ANALYZE employees;

-- 플랜에 이렇게 나옵니다
-- Bitmap Heap Scan on employees
--   ->  BitmapOr
--         ->  Bitmap Index Scan on idx_emp_department_id
--         ->  Bitmap Index Scan on idx_emp_job_id

정리하면 AND는 복합 인덱스 하나, OR는 단일 인덱스 여러 개입니다.

OR를 UNION ALL로 바꾸는 재작성은 결과를 틀리게 만들 수 있습니다. 두 조건을 각각 조회해 이어 붙이면 양쪽 조건에 모두 걸리는 행이 두 번 세어집니다. 같은 데이터에서 실제로 19건이 중복 집계됐습니다. 성능 문제가 아니라 정확성 문제입니다. UNION으로 바꿔 중복을 제거할 수는 있지만, 인덱스만 갖추면 원본 OR가 알아서 BitmapOr로 처리되므로 재작성 자체가 필요하지 않습니다.

쿼리 구조에서 생기는 문제

SELECT *

필요 없는 컬럼까지 전부 가져오면 네트워크로 오가는 양이 늘어납니다. 그리고 커버링 인덱스를 쓸 수 없게 됩니다. 인덱스에 없는 컬럼을 하나라도 요구하면 테이블에 접근해야 하기 때문입니다.

SELECT * FROM users WHERE email = 'a@b.com';        -- 테이블 접근 발생
SELECT id, name FROM users WHERE email = 'a@b.com'; -- Index Only Scan 가능

NOT IN 과 NULL

NOT IN의 목록에 NULL이 하나라도 있으면 결과가 항상 비어 있습니다. NULL과의 비교 결과가 참도 거짓도 아닌 UNKNOWN이라, 조건 전체가 참이 되지 못하기 때문입니다. 에러가 안 나고 조용히 빈 결과만 나오기 때문에 원인을 찾기 어렵습니다.

-- customer_id 에 NULL 이 하나라도 있으면 결과가 항상 0건
SELECT * FROM users
 WHERE id NOT IN (SELECT customer_id FROM orders);

-- NOT EXISTS 는 NULL 이 있어도 정상 동작하고 대체로 더 빠릅니다
SELECT * FROM users u
 WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id);

DISTINCT 남용

결과에 중복이 보인다고 DISTINCT를 붙이면 중복이 왜 생겼는지를 덮어버립니다. 대부분 JOIN 때문에 행이 불어난 것이고, 존재 여부만 확인하면 되는 경우라면 EXISTS가 맞습니다.

-- 중복 원인을 덮는 방식
SELECT DISTINCT s.name
  FROM students s JOIN enrollments e ON e.student_id = s.id;

-- 존재 여부만 확인 (행이 불어나지 않습니다)
SELECT name FROM students s
 WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

큰 OFFSET

페이지네이션을 OFFSET으로 구현하면 뒤로 갈수록 느려집니다. 건너뛸 행을 읽고 나서 버리는 방식이라, 10,000페이지를 보려면 100,000행을 읽은 뒤 버립니다.

-- 페이지가 뒤로 갈수록 선형으로 느려집니다
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- 마지막으로 본 id 부터 이어 읽습니다 (Keyset / Cursor 방식)
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;

Keyset 방식은 특정 페이지로 바로 건너뛸 수 없다는 제약이 있습니다. 무한 스크롤에는 잘 맞고, 페이지 번호를 눌러 이동하는 화면에는 안 맞습니다.

N+1

목록 100건을 조회한 뒤 각 건마다 상세를 한 번씩 더 조회하면 쿼리가 101번 나갑니다. 개별 쿼리는 전부 빠른데 합치면 느린 형태라, 실행계획만 봐서는 찾기 어렵습니다. 쿼리 개수를 세어 봐야 보입니다. JOIN이나 IN 한 번으로 묶습니다.

실제로 12개를 튜닝해보고 알게 된 것

e커머스 스키마(주문 9,590건 · 주문상품 28,815건 · 고객 3,000건)에 분석 쿼리 12개를 짜고 문항마다 튜닝 전 실행계획 → 병목 지목 → 개선 → 튜닝 후 실행계획 순서로 측정했습니다. 가장 크게 배운 것은 인덱스로 해결되는 쿼리가 절반도 안 된다는 것이었습니다.

먼저 판단 기준부터 정리해야 했습니다. 이 규모는 테이블 전체가 shared_buffers에 올라가서 Seq Scan도 밀리초 안에 끝나고, 같은 쿼리를 두 번 돌려도 실행시간이 ±20% 흔들립니다. 그래서 실행시간은 참고로만 두고 아래 순서로 봤습니다.

순위	볼 것	의미
1	`Buffers: shared hit`	실제로 읽은 페이지 수. 튜닝 효과가 가장 정직하게 찍힙니다
2	스캔 노드 변화	`Seq Scan`이 `Index Only Scan`으로 바뀌었는가
3	`Heap Fetches`	`Index Only Scan`에서 0이면 힙 접근이 완전히 사라진 것
4	`Sort Method` · `Memory`	정렬이 없어졌는가, 정렬 메모리가 줄었는가
5	`Rows Removed by Filter`	읽고 나서 버린 행. 클수록 인덱스가 필요하다는 신호
6	`Execution Time`	참고용. 이것만 보고 결론 내지 않습니다

인덱스가 통한 쿼리

조건에 맞는 행이 전체의 일부일 때는 인덱스가 그대로 먹혔습니다. 부분 인덱스로 인덱스에 담을 행을 미리 걸러 두고, INCLUDE로 함께 읽는 컬럼을 얹어 커버링 인덱스를 만드는 조합입니다.

CREATE INDEX idx_orders_sales_ts
    ON ecom.orders (order_ts)
    INCLUDE (order_id, customer_id, coupon_code)   -- 힙 접근을 없애려고 얹습니다
 WHERE order_status IN ('paid','shipped','delivered');   -- 9,590행 → 7,241행만 색인

ANALYZE ecom.orders;   -- 통계를 갱신해야 옵티마이저가 새 인덱스를 고릅니다

여기서 놓치기 쉬운 것이 하나 있습니다. 쿼리의 WHERE 절을 부분 인덱스 정의와 같은 모양으로 써야 그 인덱스가 선택됩니다. 인덱스에 order_status IN ('paid','shipped','delivered')라고 적어 놓고 쿼리에서는 order_status != 'cancelled'처럼 다르게 쓰면 결과가 같아도 인덱스를 안 탑니다. 옵티마이저가 두 조건이 같다는 것을 증명하지 못하기 때문입니다.

인덱스로 안 되는 쿼리

월별 매출 집계처럼 조건이 order_status 하나뿐이고 대상이 전체의 75%인 쿼리가 있었습니다. 부분 인덱스를 만들고 enable_seqscan = off로 강제해봤더니 Index Scan 쪽이 오히려 느렸습니다. 읽을 양을 못 줄이는데 인덱스를 한 번 더 거치는 비용만 붙기 때문입니다. 선택도가 높으면 Seq Scan이 정답이고, 이때는 쿼리를 고쳐야 합니다.

실제 병목은 count(DISTINCT o.order_id)였습니다. 주문 1건이 주문상품 개수만큼 늘어나니 중복을 제거해야 하는데, DISTINCT가 붙으면 HashAggregate를 못 쓰고 Sort + GroupAggregate로 떨어집니다. 21,768행을 1.6MB 메모리로 정렬하고 있었습니다.

-- 튜닝 전 : 조인 결과를 한 번에 GROUP BY → Sort 1,619kB
SELECT date_trunc('month', o.order_ts)::date AS order_month,
       count(DISTINCT o.order_id) AS order_cnt,
       sum(oi.line_total)         AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
 WHERE o.order_status IN ('paid','shipped','delivered')
 GROUP BY 1;

-- 튜닝 후 : 주문 단위로 먼저 접고, 월 단위로 한 번 더 접습니다
WITH order_amount AS (
    SELECT o.order_id,
           date_trunc('month', o.order_ts)::date AS order_month,
           sum(oi.line_total) AS amount
      FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
     WHERE o.order_status IN ('paid','shipped','delivered')
     GROUP BY o.order_id, 2          -- 여기서 중복이 사라집니다
)
SELECT order_month, count(*) AS order_cnt, sum(amount) AS revenue
  FROM order_amount
 GROUP BY order_month;               -- count(*) 라 Sort 가 필요 없습니다

count(DISTINCT)가 count(*)로 바뀌면서 Sort 노드가 통째로 사라지고 HashAggregate 두 단계로 처리됐습니다. 지금 규모에서는 31.9ms에서 25.6ms로 20% 정도 줄었을 뿐이지만, 정렬이 work_mem을 넘기면 external merge Disk로 떨어지므로 데이터가 커질수록 격차가 벌어지는 구조입니다.

쿼리를 고쳐서 얻은 것

인덱스 없이 쿼리 구조만 바꿔서 가장 크게 줄어든 것들입니다.

바꾼 것	어떻게	효과
2단계 집계	`count(DISTINCT)`를 주문 단위로 접어 `count(*)`로	`Sort` 1.6MB 소멸
조인 순서	집계·`HAVING`을 먼저 끝내고 마지막에 조인	조인 대상 600행 → 12행, 5.6ms → 0.7ms
자기조인 제거	같은 테이블 두 번 읽기를 `min(...) OVER (PARTITION BY ...)` 윈도우 함수로	198 → 38 buffers (81% 감소)
`IN` → `JOIN`	서브쿼리를 조인으로 바꿔 옵티마이저가 조인 순서를 고르게	31.2ms → 4.3ms
함수 인라인	`LANGUAGE plpgsql` → `LANGUAGE sql IMMUTABLE`	598 → 379 buffers

함수 인라인은 예상 밖이었습니다. 0으로 나눠도 에러가 안 나는 안전 나눗셈 함수를 plpgsql로 만들어 뒀는데, 행마다 함수 호출이 SPI를 거치면서 Buffers가 60% 가까이 늘어나 있었습니다. 본문이 SELECT 한 줄뿐인 함수는 LANGUAGE sql로 선언하면 옵티마이저가 본문을 쿼리 안으로 펼쳐 넣어 호출 자체가 사라집니다.

CREATE OR REPLACE FUNCTION ecom.f_safe_div(numer numeric, denom numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$      -- plpgsql 이 아니라 sql
  SELECT CASE WHEN denom IS NULL OR denom = 0 THEN 0 ELSE numer / denom END;
$$;

튜닝이 안 되는 쿼리도 있습니다

12개 중 2개는 무엇을 해도 숫자가 안 움직였습니다.

재고 임계치 미달 조회: WHERE qty_on_hand < reorder_point처럼 컬럼끼리 비교하는 조건은 일반 B-Tree 인덱스로는 처리할 수 없습니다. 비교 대상이 상수값이 아니라 두 컬럼의 값이라 인덱스로 탐색 범위를 만들 수 없기 때문입니다. 이 조건을 그대로
WHERE 절에 넣은 부분 인덱스를 만드는 방법이 있지만, 테이블이 600행뿐이라 옵티마이저는 Seq Scan을 선택했습니다. 값이 아니라 두 컬럼의 관계라서 색인할 키가 없기 때문입니다.
쿠폰 사용 영향 분석: 전체 주문을 다 집계하는 쿼리라 읽을 양을 줄일 방법이 없었습니다. 인덱스도 재작성도 안 통하는 유형이고, 여기서부터는 미리 계산해 두는 것 말고 답이 없습니다.

마지막 문항이 그 답이었습니다. 일자별 매출을 조회할 때마다 조인하고 집계하는 대신 Materialized View로 결과를 저장해 뒀습니다. 조회 쪽 플랜에서 Hash Join과 HashAggregate가 통째로 없어지고 3.6ms · 379 buffers가 0.06ms · 4 buffers로 바뀌었습니다. 12개 중 개선 폭이 가장 컸습니다.

CREATE MATERIALIZED VIEW ecom.mv_daily_gmv AS
SELECT date_trunc('day', o.order_ts)::date AS sales_day,
       count(DISTINCT o.order_id)          AS order_cnt,
       sum(oi.line_total)                  AS gmv
  FROM ecom.orders o JOIN ecom.order_items oi ON oi.order_id = o.order_id
 WHERE o.order_status IN ('paid','shipped','delivered')
 GROUP BY 1;

-- CONCURRENTLY 갱신을 쓰려면 UNIQUE 인덱스가 반드시 있어야 합니다
CREATE UNIQUE INDEX ux_mv_daily_gmv_day ON ecom.mv_daily_gmv (sales_day);
ANALYZE ecom.mv_daily_gmv;   -- MV 도 테이블이라 통계가 필요합니다

Materialized View는 자동으로 갱신되지 않는 스냅샷입니다. REFRESH를 걸기 전까지 데이터가 그 시점에 멈춰 있습니다. REFRESH MATERIALIZED VIEW CONCURRENTLY를 쓰면 갱신 중에도 조회가 안 잠기지만 UNIQUE 인덱스가 없으면 실행 자체가 실패하고, 일반 REFRESH보다 느립니다. 갱신 주기를 정할 때는 지표가 확정되는 단위와 사용자가 언제 보는지를 같이 봅니다. 일자별 매출은 하루 단위로 확정되고 리포트는 오후에 열리므로 매일 15시 1회로 잡았습니다.

12개 결과

쿼리	병목	적용한 개선	실행시간	Buffers
지난 한 달 매출	`Seq Scan` 후 77% 폐기	부분 + 커버링 인덱스	6.7 → 5.0 ms	376 → 291
월별 AOV	`count(DISTINCT)` Sort 1.6MB	2단계 집계	31.9 → 25.6 ms	376 (동일)
카테고리 Top 10	주문상품 전량이 3단 조인 입력	인덱스 재사용 + 필터 CTE 분리	20.4 → 15.6 ms	324 → 327
제품 매출 랭킹	순위 확정 전 600행 전부 조인	집계 → 랭킹 → 상위 20만 조인	18.8 → 17.2 ms	383 (동일)
고객 RFM	`count(DISTINCT)` Sort 1.8MB	2단계 집계	23.8 → 22.7 ms	376 (동일)
30일 내 재구매율	같은 테이블 2회 스캔 + 73% 폐기	윈도우 함수 + 복합 부분 인덱스	15.4 → 5.7 ms	198 → 38
재고 임계치 미달	컬럼 대 컬럼 비교	부분 인덱스 (미선택이 정상)	0.21 → 0.26 ms	12 (동일)
리뷰 효자상품	12행 뽑으려고 600행 조인	집계 · `HAVING` 선행	5.6 → 0.7 ms	28 (동일)
쿠폰 영향 분석	전량 집계	조회 시 계산하는 대신 Materialized View로 미리 집계	18.8 → 19.9 ms	376 (동일)
상위 1% 고객 매출	테이블 2회 읽기 + 정렬 3회	조인 제거 + `IN`→`JOIN`	31.2 → 4.3 ms	752 → 803
안전 나눗셈 함수	`plpgsql` 호출 오버헤드	`LANGUAGE sql` 인라인	소폭 단축	598 → 379
일자별 매출	열 때마다 조인 + 집계	Materialized View	3.6 → 0.06 ms	379 → 4

Buffers가 늘었는데 시간이 줄어든 경우(상위 1% 고객 매출)도 있습니다. 정렬 세 번을 없애는 대신 인덱스 페이지를 더 읽은 것이고, 이때는 시간 쪽이 맞는 판단입니다. 지표 하나를 절대 기준으로 두면 이런 경우를 놓칩니다.

느린 쿼리 찾아내기

위 패턴을 알아도 어떤 쿼리가 느린지부터 알아야 고칠 수 있습니다. PostgreSQL은 pg_stat_statements 확장이 실행된 쿼리의 통계를 모아 줍니다.

SELECT ROUND(mean_exec_time::NUMERIC, 2)  AS avg_ms,
       calls,
       ROUND(total_exec_time::NUMERIC, 0) AS total_ms,
       ROUND(shared_blks_hit::NUMERIC
             / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS cache_hit_pct,
       LEFT(query, 100) AS query_snippet
  FROM pg_stat_statements
 ORDER BY mean_exec_time DESC
 LIMIT 10;

여기서 정렬 기준을 무엇으로 두느냐가 중요합니다.

mean_exec_time: 한 번 실행에 오래 걸리는 쿼리입니다. 개별 화면이 느린 원인입니다.
total_exec_time: 누적으로 서버 시간을 가장 많이 쓴 쿼리입니다. 한 번은 빠른데 초당 수천 번 실행되는 쿼리가 여기서 잡힙니다. 전체 부하를 줄이려면 이쪽부터 봅니다.
cache_hit_pct: 낮으면 디스크를 읽고 있다는 뜻입니다.

튜닝 전후를 비교하려면 통계를 초기화한 뒤 같은 구간을 측정합니다.

SELECT pg_stat_statements_reset();

엔진마다 다른 것

여기까지는 PostgreSQL 기준입니다. 그런데 프로젝트가 바뀌면 MySQL이나 Oracle을 만나고, 같은 SQL인데 플랜이 다르게 나오거나 PostgreSQL에서 쓰던 방법이 아예 없는 경우가 생깁니다. 네 엔진의 옵티마이저가 어디서 갈리는지 정리했습니다.

구분	PostgreSQL	MySQL (InnoDB)	Oracle	SQL Server
통계 수집	`ANALYZE`, autovacuum이 자동 갱신. 기본 100버킷 히스토그램	인덱스 카디널리티 샘플링. 8.0부터 히스토그램	히스토그램 · 확장통계 · 동적 샘플링	자동 생성 · 자동 갱신. 밀도 벡터 + 히스토그램
쓸 수 있는 조인	Nested Loop · Hash · Merge	Merge Join 없음. Hash Join은 8.0.18부터	세 가지 전부, 병렬 실행 지원	세 가지 전부
힌트	공식 힌트 없음. `enable_*` 파라미터나 `pg_hint_plan` 확장	`/+ ... /`, `USE INDEX` / `FORCE INDEX`	`/+ ... /`. 실무에서 널리 사용	`OPTION (...)` 절, Plan Guide
계획 캐시	캐시하지 않고 매번 재계산 (`PREPARE` 시 generic plan)	캐시하지 않음	공유 풀에 캐시. Adaptive Plan으로 실행 중 변경	재사용이 강해 파라미터 스니핑 발생. Query Store로 이력 관리
계획 고정	통계와 인덱스로 유도하는 방법뿐	힌트로 유도	SQL Plan Baseline	Plan Guide · Query Store 강제 계획
실행계획 확인	`EXPLAIN (ANALYZE, BUFFERS)`	`EXPLAIN ANALYZE` (8.0.18+), `EXPLAIN FORMAT=JSON`	`EXPLAIN PLAN` + `DBMS_XPLAN.DISPLAY`	실제 실행 계획(Ctrl+M), `SET STATISTICS IO ON`

네 엔진 모두 비용 기반 옵티마이저(CBO)를 사용합니다. 통계를 바탕으로 각 실행 경로의 비용을 계산해 가장 비용이 낮은 계획을 선택합니다. 따라서 통계가 실제 데이터와 어긋나면 실행계획도 달라질 수 있습니다. 인덱스를 만든 뒤 ANALYZE를 함께 실행하는 이유가 여기에 있습니다.

엔진마다 가장 큰 차이는 실행계획을 얼마나 직접 제어할 수 있는지입니다. Oracle과 SQL Server는 힌트나 Plan Baseline 같은 기능으로 특정 실행계획을 강제로 사용할 수 있습니다. 반면 PostgreSQL은 공식 힌트를 제공하지 않으므로, 인덱스를 만들거나 ANALYZE로 통계를 갱신해 옵티마이저가 원하는 실행계획을 선택하도록 유도하는 방식이 일반적입니다.

앞에서 조인 방식을 비교할 때 사용한 enable_hashjoin = off도 실행계획을 확인하기 위한 실험용 설정입니다. 세션 전체에 적용되는 옵션이라 운영 환경에서 특정 쿼리의 실행계획을 제어하는 용도로 사용하지는 않습니다.

MySQL은 오랫동안 Nested Loop만 있었고 8.0.18에서야 Hash Join이 들어왔습니다. Merge Join은 지금도 없습니다. 이 글의 실습처럼 대량 집계와 조인이 많은 분석 쿼리라면 MySQL에서는 같은 SQL이 Nested Loop로 풀려 반복 액세스가 커질 수 있습니다.

정리하면

느린 쿼리를 만났을 때의 순서입니다.

① 찾기

pg_stat_statements

→

② 원인 보기

EXPLAIN ANALYZE

→

③ 고치기

쿼리 or 인덱스

→

④ 재측정

reset 후 비교

네 단계를 다 돌렸는데도 느리다면 쿼리 문제가 아니라 테이블 크기 문제일 수 있습니다. 그때부터는 파티셔닝으로 읽을 대상을 줄이거나, 읽기 복제본으로 부하를 나누거나, 샤딩까지 가는 선택이 남습니다. 모두 시스템 구조에 영향을 주는 변경이라 실무에서 마주치는 DB 트레이드오프에서 따로 정리했습니다.

플랜에서 볼 곳과 인덱스를 무력화하는 패턴을 정리해보겠습니다.

플랜에서 볼 곳	이상하면
`Seq Scan` + `Rows Removed by Filter`가 큼	그 조건 컬럼에 인덱스 검토
추정 `rows`와 실제 `rows`가 몇 배 차이	`ANALYZE` 실행
`loops`가 1이 아님	`actual time`에 곱해서 실제 시간 계산
`Sort Method: external merge Disk`	`work_mem` 부족
`Hash`의 `Batches`가 1보다 큼	해시가 디스크로 넘친 상태
`Buffers`의 `read`가 큼	디스크를 읽고 있음

안티패턴	문제	고치는 방법
컬럼에 함수 적용	인덱스 무력화	범위 조건으로 바꾸거나 함수 기반 인덱스 생성
`SELECT *`	전송량 증가, 커버링 인덱스 불가	필요한 컬럼만 지정
타입 불일치	암묵적 변환으로 인덱스 무력화	같은 타입으로 비교
`LIKE '%키워드%'`	시작점을 못 정해 전체 스캔	prefix 검색으로 바꾸거나 `pg_trgm` GIN
`OR`에 복합 인덱스	앞 컬럼으로 좁힐 수 없음	단일 인덱스를 각각 만들어 `BitmapOr` 유도
N+1	쿼리 개수가 행 수만큼 늘어남	JOIN이나 `IN`으로 한 번에
`NOT IN` + `NULL`	결과가 항상 비어 있음	`NOT EXISTS`
`DISTINCT` 남용	중복 원인을 덮음	`EXISTS`로 존재 여부만 확인
큰 `OFFSET`	뒤 페이지일수록 느려짐	Keyset(Cursor) 방식

한 줄 정리: 실행계획은 예측과 실측을 나란히 보여주는 표입니다. Rows Removed by Filter가 크거나 추정과 실제 행 수가 크게 벌어진 노드가 문제의 시작점입니다.

참고

PostgreSQL: Using EXPLAIN
PostgreSQL: Combining Multiple Indexes (Bitmap Scan)
PostgreSQL: pg_stat_statements
PostgreSQL: pg_trgm
실행계획 시각화: explain.dalibo.com (FORMAT JSON 결과를 붙여넣으면 트리로 보여줍니다)

'소프트웨어 개발 > 데이터·통계' 카테고리의 다른 글

트랜잭션 격리 수준과 MVCC \| ACID, WAL, Dirty Read와 Phantom Read (0)	2026.08.05
SQL JOIN 실수 패턴 \| LEFT JOIN과 WHERE, 중복 행, NOT IN NULL, EXISTS (0)	2026.08.03
PostgreSQL 인덱스 설계 \| 선택도, 복합 인덱스 순서, 커버링 인덱스, 쓰기 비용 (0)	2026.07.30
ERD와 SQL 기초: 데이터 모델링부터 JOIN까지 (0)	2026.07.28

현재글PostgreSQL 쿼리 튜닝 | EXPLAIN ANALYZE, Buffers, Nested Loop·Hash·Merge

boradora