이 모듈은 PostgreSQL 14+ 기준으로 작성됐습니다. MySQL은
EXPLAIN FORMAT=JSON과 실행계획 노드 이름이 다릅니다.
오후 2시. CS 팀에서 문의가 왔다. "주문 목록 페이지가 갑자기 10초 이상 걸려요. 어제까지는 괜찮았는데요." 개발팀에 물어보니 "DB는 문제없을 것 같은데요"라는 답변이 돌아왔다.
실제로 DB를 확인해보면 **풀 테이블 스캔(Seq Scan)**이 일어나고 있습니다. orders 테이블에 인덱스가 없거나, 있어도 플래너가 선택하지 않거나, 통계가 낡아서 잘못된 실행계획을 쓰고 있습니다. 원인은 EXPLAIN ANALYZE가 답을 줍니다.
- 1pg_stat_statements로 서버 전체에서 병목 쿼리를 발굴할 수 있다
- 2EXPLAIN ANALYZE 출력의 각 항목(cost, rows, actual time, Seq Scan 등)을 해석할 수 있다
- 3Seq Scan과 Index Scan의 차이를 설명하고 어떤 경우에 각각이 선택되는지 판단할 수 있다
- 4CREATE INDEX CONCURRENTLY로 운영 중단 없이 인덱스를 추가할 수 있다
- 5VACUUM ANALYZE가 필요한 시점을 알고 실행할 수 있다
확대
옵티마이저가 실행계획을 고르는 법 — 파싱부터 최저비용 계획 선택까지 5단계
WHERE status = 'completed' ORDER BY total_amount DESC LIMIT 50 같은 SQL은 "무엇을" 원하는지만 적을 뿐, "어떻게" 가져올지 — 풀스캔인지 인덱스인지, 조인 순서는 무엇인지 — 는 한 줄도 지시하지 않습니다. 그 "어떻게"를 정하는 게 옵티마이저(플래너)입니다. 이 결정 과정을 단계로 알면 "인덱스를 만들었는데 왜 Seq Scan을 골랐지"를 어느 단계의 문제인지로 좁혀 진단할 수 있습니다. EXPLAIN이 보여주는 실행계획은 아래 5단계의 결과물입니다.
[SQL] SELECT ... FROM orders WHERE status='completed'
ORDER BY total_amount DESC LIMIT 50
│
① 파싱·검증 → 문법을 파스 트리로 바꾸고 테이블·컬럼이 실제로 있는지 확인
│
② 재작성(rewrite) → 뷰를 원본 쿼리로 펼치고 일부 서브쿼리를 평탄화(논리적 정리)
│
③ 후보 접근경로 생성 → Seq Scan / Index Scan / Bitmap Scan,
│ 조인 방식(Nested Loop·Hash·Merge)과 조인 순서의 조합들
│
④ 통계 기반 비용 추정 → 각 후보의 카디널리티(반환 행 수)를 통계로 추정하고
│ 페이지 I/O + CPU를 합산해 '비용' 숫자를 매김
│
⑤ 최저비용 계획 선택 → 후보 중 비용이 가장 낮은 하나를 확정
▼
[실행기] 확정된 계획을 그대로 실행 → EXPLAIN이 보여주는 그 트리
각 단계에서 무슨 일이 일어나고, 틀어지면 어떤 증상인가:
| 단계 | 하는 일 | 여기서 틀어지면 |
|---|---|---|
| ① 파싱·검증 | SQL을 파스 트리로 바꾸고 테이블·컬럼 존재를 확인 | 오타·없는 컬럼 → column ... does not exist로 계획 수립 전에 실패 |
| ② 재작성 | 뷰를 펼치고 서브쿼리를 평탄화 | 상관 서브쿼리가 안 펼쳐지면 바깥 행마다 반복 실행되는 계획이 남음 |
| ③ 접근경로 생성 | 쓸 수 있는 스캔·조인 방식을 후보로 나열 | 컬럼에 함수·형변환이 걸리면 인덱스 경로가 후보에서 아예 빠짐(WHERE UPPER(email)=...) |
| ④ 비용 추정 | 통계로 각 후보의 반환 행 수·I/O를 추정해 비용 매김 | 통계가 낡으면 추정이 빗나가 엉뚱한 후보를 싸게 계산(rows vs actual rows 괴리) |
| ⑤ 계획 선택 | 추정 비용이 가장 낮은 후보를 확정 | ④가 틀리면 잘못된 계획을 '최적'이라 확신하고 고름 |
핵심은 옵티마이저의 선택이 ④의 통계만큼만 정확하다는 것입니다. EXPLAIN의 rows=는 ④가 통계로 추정한 값이고, EXPLAIN ANALYZE의 actual rows는 ⑤가 고른 계획을 실제로 돌린 결과입니다. 이 둘이 10배 이상 벌어지면 ④가 낡은 통계로 추정을 틀렸고 그 위에서 ⑤가 잘못된 계획을 골랐다는 뜻이라 VACUUM ANALYZE로 통계를 갱신해야 합니다. 인덱스를 만들었는데도 Seq Scan이 나온다면 대개 ③(함수·형변환으로 인덱스 후보가 안 생김) 아니면 ④(통계가 낡아 Seq Scan을 더 싸게 추정)의 문제입니다. 바로 다음 블록에서 배울 EXPLAIN ANALYZE 출력 읽기는, 이 ④·⑤가 내린 결정을 사후에 검증하는 작업입니다.
EXPLAIN ANALYZE 출력 해석 — 읽는 법을 모르면 아무 의미가 없다
쿼리가 느립니다. EXPLAIN ANALYZE를 실행했는데 수십 줄의 출력이 나왔습니다. 어디를 봐야 할까요?
읽는 순서: 안쪽(들여쓰기 깊은) 노드부터 바깥으로 읽습니다. 실행계획은 트리 구조이고, 실제 실행은 안쪽 노드부터 시작됩니다.
Seq Scan on orders (cost=0.00..45231.00 rows=3512 width=120)
(actual time=0.042..312.451 rows=3487 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 892513
Planning Time: 0.215 ms
Execution Time: 312.893 ms
| 항목 | 의미 | 주목해야 할 신호 |
|---|---|---|
cost=0.00..45231.00 | 플래너 비용 추정 (임의 단위) | 숫자 자체보다 다른 노드와 비교 |
rows=3512 | 플래너가 예상한 반환 행 수 | actual rows와 크게 다르면 통계 낡음 |
actual time=0.042..312.451 | 실제 첫 행까지(ms)..전체 완료(ms) | 이 값이 병목 위치 |
actual rows=3487 | 실제 반환된 행 수 | rows와 10배 이상 차이 → VACUUM ANALYZE 필요 |
Rows Removed by Filter: 892513 | 필터에서 버려진 행 수 | 많을수록 인덱스 효과 큼 |
loops=1 | 이 노드가 실행된 횟수 | Nested Loop 내부라면 N배 비용 |
핵심 진단 공식:
Execution Time> 500ms → 튜닝 대상- 대형 테이블에서
Seq Scan→ 인덱스 부재 의심 rowsvsactual rows10배 이상 차이 →VACUUM ANALYZE필요Rows Removed by Filter가actual rows의 100배 이상 → 인덱스로 필터링 가능
어떤 쿼리가 문제인지 모를 때 pg_stat_statements로 전체 서버에서 가장 많은 시간을 소비하는 쿼리를 발굴합니다.
pg_stat_statements가 활성화되어 있지 않다면 먼저 설정합니다:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' 후 재시작 필요
SELECT LEFT(query,80), calls, ROUND(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;- total_exec_time이 높은 쿼리 — 서버 전체 CPU/I/O에 가장 큰 영향을 주는 쿼리
- mean_exec_time이 높은 쿼리 — 한 번 실행할 때 사용자가 오래 기다리는 쿼리
- calls가 많고 mean_exec_time도 높은 쿼리 — 우선순위 1순위 개선 대상
- stddev_ms가 크면 — 어떤 때는 빠르고 어떤 때는 느린 불안정한 쿼리 (데이터 분포 편중 의심)
query_snippet | calls | mean_ms
----------------------------------------------------- +--------+---------
SELECT o.*, c.name FROM orders o JOIN customers c O... | 45231 | 2341.23
SELECT * FROM products WHERE category_id = $1 ORDER... | 12038 | 312.45
UPDATE orders SET status = $1 WHERE id = $2 | 102341 | 1.23
BUFFERS 옵션을 추가하면 디스크 I/O 현황(공유 버퍼 히트/미스)까지 확인할 수 있습니다.
DML 쿼리(UPDATE/DELETE/INSERT)는 실제 데이터가 변경되므로 반드시 트랜잭션으로 감쌉니다:
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'reviewed' WHERE customer_id = 42;
ROLLBACK;
EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_id, o.total_amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at >= '2024-01-01' AND o.status = 'completed' ORDER BY o.total_amount DESC LIMIT 50;- Execution Time — 전체 쿼리 실행 시간. 이 숫자가 개선 목표가 된다
- Seq Scan on orders — 대형 테이블에서 이게 나오면 인덱스 부재 신호
- Rows Removed by Filter — 필터 후 버려진 행이 실제 반환 행보다 100배 이상이면 인덱스 추가 효과가 크다
- shared hit=N read=M — read가 높으면 디스크 I/O가 많다는 것 (캐시 미스)
- Planning Time이 Execution Time보다 크면 — 플래너 복잡도 문제 (파티셔닝 등)
Sort (cost=45892.34..45892.46 rows=50 width=136)
(actual time=1243.891..1243.923 rows=50 loops=1)
Sort Key: o.total_amount DESC
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=1892.00..45231.00 rows=50000 width=136)
(actual time=45.123..1201.456 rows=48923 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders (cost=0.00..38231.00 rows=896000 width=80)
(actual time=0.034..923.123 rows=234891 loops=1)
Filter: ((status = 'completed') AND (created_at >= '2024-01-01'))
Rows Removed by Filter: 661109
-> Hash (cost=892.00..892.00 rows=27360 width=56)
(actual time=44.234..44.234 rows=27360 loops=1)
Planning Time: 2.345 ms
Execution Time: 1244.123 ms
일반 CREATE INDEX는 테이블에 ShareLock을 걸어 인덱스 생성 중 INSERT/UPDATE/DELETE가 차단됩니다. 운영 서비스에서는 반드시 CONCURRENTLY를 사용합니다.
WHERE status = 'completed'는 **부분 인덱스(Partial Index)**입니다. 전체 rows가 아닌 해당 조건의 데이터만 인덱싱해 인덱스 크기를 줄이고 갱신 비용을 낮춥니다.
확대
CREATE INDEX CONCURRENTLY idx_orders_created_status ON orders (created_at DESC, status) WHERE status = 'completed';- 인덱스 생성 중 서비스 쿼리가 정상 실행되는지 모니터링한다
- pg_stat_progress_create_index로 진행 상황을 확인한다
- 생성 완료 후 pg_indexes에서 INVALID 상태가 아닌지 반드시 확인한다
-- 인덱스 생성 진행률 모니터링
SELECT phase, blocks_done, blocks_total,
ROUND(blocks_done::numeric / NULLIF(blocks_total,0) * 100, 1) AS pct
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;
-- 생성 완료 후 INVALID 인덱스 확인
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- INVALID가 보이면: DROP INDEX CONCURRENTLY <인덱스명> 후 재시도
같은 쿼리를 동일하게 실행해서 Seq Scan → Index Scan으로 바뀌었는지, Execution Time이 감소했는지 수치로 확인합니다.
EXPLAIN (ANALYZE, BUFFERS) SELECT o.order_id, o.total_amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at >= '2024-01-01' AND o.status = 'completed' ORDER BY o.total_amount DESC LIMIT 50;- Seq Scan → Index Scan 또는 Index Only Scan으로 바뀌었는가
- Execution Time이 목표 범위(500ms 이하)로 줄었는가
- Rows Removed by Filter가 크게 줄었는가
- 개선 전후 Execution Time을 함께 기록해 장애 보고서에 포함한다
Index Scan using idx_orders_created_status on orders
(cost=0.56..892.34 rows=50 width=80)
(actual time=0.234..12.456 rows=50 loops=1)
Index Cond: ((created_at >= '2024-01-01') AND (status = 'completed'))
Execution Time: 15.234 ms ← 인덱스 전 1244ms에서 98.8% 개선
실제 출력
Nested Loop (cost=0.56..234891.23 rows=1000 width=136)
(actual time=0.345..8923.456 rows=8234 loops=1)
-> Seq Scan on orders (cost=0.00..45231.00 rows=896000 width=80)
(actual time=0.034..7834.123 rows=896000 loops=1)
Filter: (user_id = 1042)
Rows Removed by Filter: 887766
-> Index Scan using customers_pkey on customers
(cost=0.29..0.31 rows=1 width=56)
(actual time=0.001..0.001 rows=1 loops=8234)
Planning Time: 1.234 ms
Execution Time: 8924.789 ms❓ Execution Time이 약 9초입니다. 실행계획을 보고 (1) 병목이 어느 노드인지, (2) 왜 느린지, (3) 어떤 조치를 취해야 하는지 판단해보세요.
분명 인덱스를 만들었습니다. pg_indexes에서도 보입니다. 그런데 EXPLAIN을 실행하면 여전히 Seq Scan을 선택합니다.
원인 1: 테이블이 작아서 플래너가 Seq Scan을 선호
테이블 행 수가 수백~수천 개 수준이라면 인덱스 페이지와 테이블 페이지를 번갈아 읽는 Index Scan보다 순차 스캔이 더 빠릅니다. 이 경우 Seq Scan은 정상이며 문제가 없습니다.
원인 2: 통계가 오래되어 플래너가 잘못된 선택
-- 통계 갱신
ANALYZE orders;
-- 갱신 후 EXPLAIN 재실행
EXPLAIN SELECT * FROM orders WHERE user_id = 1042;
원인 3: 인덱스 컬럼에 함수 적용
-- 인덱스를 못 쓰는 패턴
WHERE UPPER(email) = 'USER@EXAMPLE.COM' -- 함수 적용
WHERE created_at::date = '2024-01-01' -- 타입 캐스팅
-- 인덱스를 쓰는 패턴
WHERE email = LOWER('USER@EXAMPLE.COM') -- 우변에 함수
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
원인 4: 플래너 강제 테스트 (임시)
-- 테스트: 인덱스 스캔 강제 (운영에서는 사용 금지)
SET enable_seqscan = OFF;
EXPLAIN SELECT * FROM orders WHERE user_id = 1042;
SET enable_seqscan = ON;
-- 강제 시 Index Scan으로 바뀌고 cost가 낮아지면 → 통계 문제
-- 강제 시에도 cost가 높으면 → 해당 쿼리엔 인덱스보다 Seq Scan이 유리
인덱스 생성 중 오류가 발생했습니다. pg_indexes에서 해당 인덱스를 조회하면 여전히 남아있고, 쿼리 성능은 개선되지 않습니다.
-- INVALID 인덱스 확인
SELECT indexname, indexdef
FROM pg_indexes pi
JOIN pg_class pc ON pc.relname = pi.indexname
JOIN pg_index pi2 ON pi2.indexrelid = pc.oid
WHERE pi2.indisvalid = false
AND pi.tablename = 'orders';
-- INVALID 인덱스 제거 (CONCURRENTLY로 락 없이)
DROP INDEX CONCURRENTLY idx_orders_created_status;
-- 재시도
CREATE INDEX CONCURRENTLY idx_orders_created_status
ON orders (created_at DESC, status);
INVALID 인덱스 문제점: 인덱스가 존재하지만 플래너가 사용하지 않습니다. 그러나 INSERT/UPDATE/DELETE 시 갱신 비용은 계속 발생합니다. 빠르게 제거하고 재생성해야 합니다.
실패 원인 파악:
-- 실패 당시 에러 확인 (PostgreSQL 로그)
tail -50 /var/log/postgresql/postgresql-*.log | grep -E "ERROR|FATAL"
-- 흔한 원인
-- 1. CONCURRENTLY는 트랜잭션 블록 안에서 사용 불가
-- 2. 다른 세션이 같은 테이블에 AccessExclusiveLock 보유
-- 3. 디스크 공간 부족
심화 — '싸 보이는 계획'이 가장 느릴 때: fast-start의 함정
심화: LIMIT + ORDER BY는 옵티마이저를 낙관하게 만든다
EXPLAIN이 낮은 cost를 보여줬는데 실제로는 몇 초가 걸리는, 앞의 진단 공식으로는 잘 안 잡히는 부류가 있습니다. 그 정체를 알아야 "인덱스도 탔는데 왜 느리지"에서 멈추지 않습니다.
ORDER BY created_at DESC LIMIT 10을 만나면 플래너는 fast-start(abort-early) 계획을 고려합니다. created_at 인덱스를 최신순으로 스캔하다 WHERE 조건에 맞는 행 10개가 나오면 즉시 멈추는 방식입니다. 전체를 정렬할 필요가 없으니 비용 추정이 아주 낮게 나옵니다.
문제는 이 낮은 비용이 **"조건에 맞는 행이 인덱스 순서상 고르게 퍼져 있다"**는 가정 위에 있다는 점입니다. 조건 선택도(예: status = 'pending')가 낮고, 게다가 그 행들이 인덱스 순서의 한쪽 끝(예: 과거)에 몰려 있으면, 최신 쪽부터 훑어도 10건을 못 채워 인덱스를 거의 끝까지 스캔합니다. cost는 낮은데 actual time은 폭발하는 것이죠.
- 징후: EXPLAIN ANALYZE에서
Index Scan Backward인데 actual rows(또는 스캔한 행)가 수십만이고Rows Removed by Filter가 거대. 추정 rows는 작은데 실제는 큼. - 직관과 반대되는 실험: LIMIT을 크게 하거나 빼면 오히려 빨라짐. 플래너가 abort-early 가정을 못 쓰게 되어 비트맵 스캔+정렬로 바뀌기 때문.
- 근본 해결: 인덱스가 필터와 정렬을 동시에 제공하게 만들기.
(status, created_at DESC)복합 인덱스나WHERE status = 'pending'부분 인덱스면, 인덱스 앞에서 10건만 읽고 진짜로 즉시 멈춥니다.
Rows Removed by Filter가 크면 "필터를 인덱스로 옮겨라"는 신호라는 앞의 공식이, LIMIT과 만나면 더 날카로워집니다 — 정렬 컬럼만 인덱싱된 상태가 가장 위험합니다.
상황: SELECT ... FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 10이 8초. 이상해서 LIMIT 1000으로 바꿔 봤더니 0.2초. LIMIT을 줄일수록 느려지는, 상식과 정반대 현상이 재현됩니다.
원인: orders에는 created_at 단일 인덱스만 있습니다. LIMIT 10에서 플래너는 이 인덱스를 최신순으로 훑다 pending 10건이 나오면 멈추는 fast-start 계획을 골랐습니다. 그런데 pending은 전체의 0.5%뿐이고 대부분 오래 방치된 과거 주문이라, 최신 쪽엔 거의 없습니다. 결국 인덱스를 과거까지 수십만 행 훑어야 10건이 채워집니다. LIMIT 1000이면 플래너가 이 계획의 이점을 못 보고 비트맵 스캔+정렬로 바꿔 오히려 빠릅니다.
진단: 두 경우를 각각 EXPLAIN ANALYZE로 비교합니다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, total_amount, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;
Limit (actual time=7981.2..7981.3 rows=10 loops=1)
-> Index Scan Backward using idx_orders_created_at on orders
(cost=0.43..612.5 rows=10 ...) ← 추정은 싸 보임
(actual time=41.0..7981.2 rows=10 loops=1)
Filter: (status = 'pending')
Rows Removed by Filter: 486213 ← 48만 행을 버리며 인덱스를 끝까지 훑음
Execution Time: 7981.9 ms
추정 cost는 612인데 actual time은 8초, Rows Removed by Filter가 48만입니다 — fast-start가 빗나간 전형입니다.
해결: 인덱스가 필터(status)와 정렬(created_at)을 함께 담당하게 합니다.
-- 복합 인덱스: pending 행만 최신순으로 인덱스에 정렬돼 담김
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);
-- 또는 pending 비중이 매우 작으면 부분 인덱스가 더 작고 빠름
CREATE INDEX CONCURRENTLY idx_orders_pending_created
ON orders (created_at DESC) WHERE status = 'pending';
적용 후 계획은 Index Scan using idx_orders_status_created로 바뀌고, 앞에서 10건만 읽으므로 Rows Removed by Filter가 거의 0, Execution Time이 수 ms로 떨어집니다. LIMIT을 늘려 두는 임시방편이 아니라, "정렬만 인덱싱된 상태"라는 뿌리를 없애는 것이 핵심입니다.
실무 대응: "주문 목록 페이지 10초 걸린다" CS 접수 → 해결까지
-- Step 1: 어떤 쿼리가 문제인지 찾기 (2분)
SELECT
LEFT(query, 100) AS query_snippet,
calls,
ROUND(total_exec_time::numeric / 1000, 1) AS total_sec,
ROUND(mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Step 2: 현재 실행 중인 슬로우 쿼리 확인 (현재 진행형이면)
SELECT pid, now() - query_start AS duration, state, LEFT(query, 80)
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < now() - interval '3 seconds'
ORDER BY duration DESC;
-- Step 3: 문제 쿼리 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.status, o.total_amount, o.created_at
FROM orders o
WHERE o.user_id = 1042
ORDER BY o.created_at DESC
LIMIT 20;
-- → Seq Scan 확인, Rows Removed by Filter 확인
-- Step 4: 인덱스 생성 (무중단)
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);
-- Step 5: 생성 진행 모니터링
SELECT phase, blocks_done, blocks_total,
ROUND(blocks_done::numeric / NULLIF(blocks_total,0) * 100, 1) AS pct
FROM pg_stat_progress_create_index;
-- Step 6: 개선 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.status, o.total_amount, o.created_at
FROM orders o
WHERE o.user_id = 1042
ORDER BY o.created_at DESC
LIMIT 20;
-- → Execution Time 비교: 9000ms → 12ms 등
CS 답변 예시:
"주문 목록 쿼리에 인덱스가 없어 896,000행 풀스캔이 발생하고 있었습니다. CREATE INDEX CONCURRENTLY로 서비스 중단 없이 인덱스를 추가했으며, 적용 후 응답 시간이 8924ms → 15ms로 개선됐습니다. 데이터 증가에 따른 인덱스 모니터링 주기를 월 1회로 설정하겠습니다."
명령어·구문 빠른 참조
이 모듈에서 다룬 실행계획 분석·인덱스 명령을 모았습니다(PostgreSQL 14+ 기준).
| 구문/명령 | 용도 | 예 |
|---|---|---|
EXPLAIN | 실행계획만(쿼리 미실행) | EXPLAIN SELECT * FROM orders WHERE user_id = 42 |
EXPLAIN ANALYZE | 실제 실행 + actual time/rows | EXPLAIN ANALYZE SELECT … |
EXPLAIN (ANALYZE, BUFFERS) | 디스크 I/O(버퍼 히트/미스)까지 | EXPLAIN (ANALYZE, BUFFERS) SELECT … |
pg_stat_statements | 서버 전체 병목 쿼리 발굴 | SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10 |
CREATE INDEX | 인덱스 생성(생성 중 쓰기 락) | CREATE INDEX idx_orders_user ON orders (user_id) |
CREATE INDEX CONCURRENTLY | 무중단 인덱스 생성 | CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id, created_at DESC) |
부분 인덱스 … WHERE | 조건 행만 인덱싱(크기·갱신비용↓) | CREATE INDEX … ON orders (created_at DESC) WHERE status = 'completed' |
DROP INDEX CONCURRENTLY | INVALID 인덱스 무중단 제거 | DROP INDEX CONCURRENTLY idx_orders_user |
ANALYZE / VACUUM ANALYZE | 통계 갱신(추정 정확도↑) | VACUUM ANALYZE orders |
pg_stat_progress_create_index | 인덱스 생성 진행률 | SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index |
pg_indexes | 인덱스 목록·정의·INVALID 확인 | SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders' |
SET enable_seqscan = OFF | (테스트)인덱스 스캔 강제 | SET enable_seqscan = OFF; EXPLAIN SELECT … |
관련 모듈로 더 깊이:
- B-Tree 인덱스의 작동 원리와 인덱스 설계의 핵심 조건 — 어떤 컬럼에 어떤 인덱스를 만들지의 근본 원리
- 쿼리 실행 계획(Execution Plan) 읽는 법과 인덱스 최적화 — EXPLAIN 출력을 노드 단위로 더 정밀하게 읽는 법
- N+1 문제, SELECT *, 인덱스 무력화 안티패턴 방지 — 애초에 슬로우 쿼리를 만드는 패턴을 피하는 법
다음 모듈에서는 계층형 댓글, 다대다 태그 시스템, Soft Delete 등 실전 스키마 설계 패턴을 다룹니다.