DB Index & Query Optimization

분류: Layer 8 - 데이터베이스 심화 | 작성일: 2026-04-02

1. 한 줄 정의

인덱스는 테이블 전체를 매번 훑지 않기 위해 검색 키와 실제 행 위치를 별도 자료구조에 정리해 둔 탐색 장치다. 쿼리 최적화는 이 탐색 장치를 무조건 많이 만드는 일이 아니라, SQL의 조건·정렬·조인 모양과 데이터 분포를 보고 PostgreSQL의 실행 계획이 더 적은 페이지와 행을 읽도록 만드는 일이다.

이 문서에서 말하는 실행 계획은 PostgreSQL의 플래너(planner)가 “이 쿼리를 어떤 순서와 알고리즘으로 실행할지” 계산한 결과다. EXPLAIN ANALYZE는 그 계획과 실제 실행 결과를 함께 보여주는 도구이며, 느린 API를 감으로 고치는 대신 “무엇을 많이 읽었는가”를 확인하게 해준다.

2. 왜 중요한가

느린 조회는 보통 “DB가 느리다”가 아니라 “DB가 너무 많은 것을 읽고 있다”에서 시작한다. 인덱스가 없으면 PostgreSQL은 조건에 맞는 행이 1개뿐이어도 테이블 페이지를 앞에서부터 끝까지 읽을 수 있다. 이것이 순차 스캔(Seq Scan)이다. 반대로 적절한 인덱스가 있으면 조건에 맞는 키 범위로 바로 이동해 필요한 후보 행만 읽을 수 있다.

하지만 인덱스는 성능 버튼이 아니다. 인덱스를 하나 만들 때마다 INSERT, UPDATE, DELETE 같은 DML(Data Manipulation Language, 데이터 변경문)은 테이블뿐 아니라 인덱스도 함께 갱신해야 한다. 인덱스가 많을수록 쓰기 비용, 저장 공간, VACUUM과 통계 갱신 비용도 늘어난다. 그래서 인덱스 학습의 핵심은 “어떻게 만들까”보다 “언제 만들고, 언제 만들지 않을까”에 있다.

TypeORM 같은 ORM(Object-Relational Mapper, 객체와 관계형 DB를 연결하는 라이브러리)에서는 쿼리 모양도 중요하다. find() 결과를 반복하면서 관계 데이터를 다시 조회하면 N+1 쿼리가 발생한다. N=100이면 101번, N=1,000이면 1,001번의 쿼리가 나가고, 각 쿼리가 2ms만 걸려도 왕복 지연과 커넥션 점유 시간이 빠르게 누적된다. Scout APM은 이 패턴을 Rails·Django·Elixir 같은 ORM 공통 문제로 설명한다.

BackOps 환경의 정산, 통계, 검색, 관리자 목록 조회는 데이터가 커진 뒤 문제가 드러난다. 처음 1만 건에서는 30ms였던 API가 1,000만 건에서 5초가 되는 이유를 이해하려면 인덱스 구조, 실행 계획, 플래너 통계, 조인 전략을 함께 읽어야 한다.

출처: Scout APM — Understanding N+1 Database Queries

2-1. 선행 한계 → B+Tree 인덱스가 등장한 이유

용어부터 정리하자. PostgreSQL 공식 문서는 기본 인덱스 access method를 B-tree라고 부른다. 학습 관점에서는 리프 페이지가 연결되어 범위 검색에 강한 B+Tree 계열 성격을 함께 설명한다. 그래서 이 문서에서는 PostgreSQL 기능명과 공식 문맥은 B-tree, 탐색 모델과 직관은 B+Tree로 풀어 쓴다.

문제의 출발점은 단순하다. 테이블이 정렬되지 않은 큰 더미라면 WHERE id = 123 조건도 “첫 행부터 끝 행까지 확인”해야 한다. 반환 행이 0~1개뿐이어도 후보를 빠르게 건너뛸 구조가 없기 때문이다. 데이터가 10만 건이면 10만 행 후보, 1억 건이면 1억 행 후보를 검사하는 식으로 비용이 선형으로 늘어난다.

B+Tree 인덱스의 철학은 쓰기 시점에 정렬 유지 비용을 미리 지불하고, 읽기 시점의 탐색 공간을 줄인다는 것이다. PostgreSQL은 인덱스 트리의 루트에서 시작해 브랜치 페이지를 거쳐 리프 페이지까지 내려가며 후보 키 범위를 찾는다. 이 과정은 대략 O(log N)이고, 일반적인 서비스 크기에서는 몇 단계의 페이지 접근으로 후보 위치에 도달한다.

경계도 함께 봐야 한다. 결과가 테이블의 큰 비율을 차지하면 인덱스를 타고 행을 하나씩 찾아가는 랜덤 I/O보다, 테이블을 순서대로 읽는 Seq Scan이 더 쌀 수 있다. PostgreSQL의 플래너가 인덱스가 있는데도 Seq Scan을 고르는 것은 항상 실패가 아니라, “읽어야 할 행 비율이 너무 커서 순차 읽기가 더 낫다”는 판단일 수 있다.

PostgreSQL B-tree 인덱스는 MVCC(Multi-Version Concurrency Control, 동시성 제어를 위해 행 버전을 보존하는 방식)와도 연결된다. 인덱스 리프에는 보통 실제 행 자체가 아니라 힙 튜플 위치가 저장되고, UPDATE/DELETE가 누적되면 죽은 행 버전과 인덱스 항목이 남아 VACUUM과 재정리 비용이 생긴다. Index bloat는 이런 불필요한 인덱스 항목과 빈 페이지 때문에 인덱스가 실제 유효 데이터보다 커진 상태다. 이 문서에서 MVCC는 트랜잭션 이론이 아니라 인덱스 생성, index bloat, EXPLAIN 해석에 영향을 주는 저장 구조 경계로만 다룬다.

출처: PostgreSQL 공식 문서 — Indexes Introduction, PostgreSQL 공식 문서 — CREATE INDEX

3. 핵심 개념

3-1. B-Tree 인덱스 동작 원리

비유: 도서관 십진분류 카탈로그

책이 100만 권인 도서관에서 “컴퓨터 공학” 책을 찾는다고 생각해보자. 모든 책을 하나씩 펼쳐보는 게 Seq Scan, 십진분류 카탈로그(대분류 → 중분류 → 소분류)로 찾아가는 게 B-Tree Index Scan이다.

왜 이렇게 설계되었는가 — B-Tree 선택의 이유

PostgreSQL이 기본 인덱스로 B-tree 계열을 선택한 이유는 세 가지다. 첫째, O(log N) 탐색으로 데이터 1억 건도 3~4단계면 찾는다. 둘째, 리프 페이지가 연결되어 범위 검색이 효율적이다(BETWEEN, >, <). 셋째, 균형 트리 구조로 어떤 값을 찾아도 일정한 성능이 보장된다. 실무에서 슬로우 쿼리 대부분은 B-tree 인덱스 누락이나 인덱스를 타지 못하는 쿼리 패턴에서 발생한다.

선택 심화: B-Tree 내부 최적화 기능

첫 회독에서는 이 블록을 건너뛰어도 된다. HOT Updates, deduplication, bottom-up deletion은 UPDATE가 많은 테이블이나 bloat 문제를 볼 때 돌아오면 충분하다. 지금 잡을 핵심은 하나다. 인덱스는 읽기를 빠르게 하지만, 쓰기·갱신·정리 비용도 함께 만든다.

PostgreSQL의 B-Tree는 multi-level tree이며 각 레벨은 페이지의 doubly-linked list로 동작한다. 공식 문서 기준으로 leaf page가 보통 전체 페이지의 99% 이상이고, leaf page가 꽉 차면 page split이 상위 레벨로 전파될 수 있다. 그래서 PostgreSQL은 쓰기 부하에서 split과 bloat를 줄이기 위해 HOT Updates, bottom-up deletion, deduplication 같은 최적화를 함께 사용한다.

핵심 최적화 3가지:

1. HOT Updates (Heap Only Tuple)
   - 인덱스 컬럼이 바뀌지 않는 UPDATE는 인덱스를 재작성하지 않음
   - 예: name은 인덱스인데 email만 바꾸는 경우 → 인덱스 갱신 비용 없음
   - 효과: 인덱스 비대화(bloat) 속도 크게 감소

2. Index Deduplication (PostgreSQL 13+)
   - 중복값이 많은 인덱스에서 같은 값을 하나의 포스팅 리스트로 압축
   - 예: status='PENDING' 값이 반복되면 key는 한 번 저장하고 TID 배열로 묶어 leaf page 공간 절약
   - 기본값: B-Tree `deduplicate_items = ON`

3. Bottom-up Deletion
   - 인덱스 페이지가 꽉 찰 때 페이지 분할 전에 dead tuple 정리 먼저 시도
   - UPDATE가 잦고 HOT 최적화가 적용되지 않는 테이블에서 version churn tuple을 줄임

출처: PostgreSQL 공식 문서 — B-Tree Indexes, PostgreSQL 공식 문서 — CREATE INDEX storage parameters

구조

루트(Root) 페이지
  └─ 브랜치(Branch/Internal) 페이지 [100 ~ 500]
       ├─ 리프(Leaf) 페이지 [100 ~ 200]
       │    ├─ (101, → heap row ptr)
       │    ├─ (150, → heap row ptr)
       │    └─ (199, → heap row ptr)
       └─ 리프(Leaf) 페이지 [201 ~ 300]
            ├─ (201, → heap row ptr)
            └─ ...

리프 페이지들은 연결 리스트(Doubly Linked List)로 연결됨
→ 범위 검색 시 순서대로 탐색 가능

핵심 특성

3-1. B-Tree 인덱스 동작 원리 비교

균형 트리

설명: 모든 리프가 동일한 깊이 → 어느 값을 찾아도 탐색 시간이 일정

O(log N)

설명: 데이터 1억 건도 3~4단계면 도달 (루트 1페이지 → 브랜치 → 리프)

정렬 유지

설명: 값이 항상 오름차순으로 정렬된 상태 유지

리프 연결 리스트

설명: 범위 검색(BETWEEN, >, <) 시 연결된 리프를 순서대로 탐색

규모 스케일링 예시

2레벨 트리: 약 18만 건 (600 브랜치 포인터 × 300 항목/페이지)
3레벨 트리: 약 1억 건 이상
→ 전형적인 서비스 DB는 3레벨이면 충분

B-Tree vs B+Tree

PostgreSQL의 공식 명칭은 B-tree다. 다만 리프 페이지 중심 범위 스캔 특성 때문에 학습 자료에서는 B+Tree 계열 모델로 설명되기도 한다. 여기서 중요한 것은 이름 논쟁이 아니라, 정렬된 트리와 리프 연결이 Seq Scan보다 후보 탐색 공간을 줄인다는 점이다.

구분	일반적인 B-Tree 설명	B+Tree식 리프 중심 모델
데이터 위치	내부 노드에도 값이 있을 수 있음	리프에 실제 항목이 집중됨
범위 검색	구현에 따라 다름	리프 연결을 따라 순차 탐색
학습 포인트	균형 트리로 탐색 깊이를 제한	범위/정렬 조회에 강한 이유를 설명

→ 범위 검색이 많은 실무에서 B+Tree가 유리한 이유: WHERE created_at BETWEEN '2026-01-01' AND '2026-04-01' 같은 쿼리는 시작 리프를 찾은 뒤 연결 리스트를 따라가면 되기 때문이다.

📖 더 보기: Understanding the Mechanics of PostgreSQL B-Tree Indexes — B-Tree 페이지 구조를 pageinspect 확장으로 직접 확인하는 방법 포함

B+Tree 원리의 다른 시스템 전이

B+Tree와 “정렬된 자료구조로 탐색 범위를 줄인다”는 원리는 PostgreSQL에만 국한되지 않는다.

시스템	내부 구조	PostgreSQL B+Tree와의 차이
MySQL InnoDB	B+Tree — 단, PK 자체가 Clustered Index (데이터 행이 리프 노드에 직접 저장). 보조 인덱스 리프에는 실제 행 위치가 아닌 PK 값이 저장되어, 보조 인덱스 조회 시 PK → Clustered Index 추가 탐색 발생	PostgreSQL Non-Clustered는 힙 주소를 저장; MySQL은 PK를 저장
Elasticsearch/Lucene	역색인(Inverted Index) — 텍스트 토큰 → 문서 ID 목록. B+Tree처럼 “조건에 해당하는 대상만 찾는다”는 목적은 동일하지만, 구조가 불변(Immutable) 세그먼트로 관리되어 업데이트 시 새 세그먼트 생성 후 병합	B+Tree는 제자리 갱신; Lucene 역색인은 병합 기반 갱신
파일시스템(ext4/APFS)	디렉터리 엔트리에 B-Tree 변형 사용. inode 번호 탐색이 DB PK 탐색과 구조적으로 동일	파일 데이터가 “힙”, 디렉터리 트리가 “인덱스”에 대응

핵심 전이 통찰: “탐색 공간을 미리 정렬해 두면 O(N) → O(log N)으로 줄 수 있다”는 원리는 동일하다. 시스템마다 갱신 전략(제자리 갱신 vs. 불변 세그먼트)과 데이터 위치(힙 포인터 vs. PK vs. 문서 ID)가 다를 뿐이다.

📖 출처: MySQL 공식 문서 — InnoDB Clustered and Secondary Indexes, Elastic Blog — Elasticsearch from the Bottom Up

Hash Index

-- Hash Index 생성 (PostgreSQL 10+ 에서 WAL 지원)
CREATE INDEX idx_user_email_hash ON users USING HASH (email);

구분	B-Tree	Hash Index
동등 검색 (`=`)	O(log N)	O(1)
범위 검색 (`>`, `<`, `BETWEEN`)	가능	불가능
정렬 (`ORDER BY`)	가능	불가능
사용 권장	대부분의 경우	오직 동등 검색만 할 때

실무에서는 B-Tree를 기본으로 사용하고, Hash는 거의 쓰지 않는다.

3-2. 인덱스 종류

Clustered Index (클러스터드 인덱스)

테이블 데이터 자체가 인덱스 순서로 물리적으로 정렬되는 구조다. MySQL InnoDB는 primary key가 clustered index 역할을 하지만, PostgreSQL의 primary key는 테이블을 자동으로 물리 정렬하지 않는다. PostgreSQL의 CLUSTER는 선택한 인덱스 기준으로 테이블을 한 번 재정렬하는 명령에 가깝고, 이후 쓰기까지 계속 정렬을 유지해주는 자동 구조는 아니다.

-- PostgreSQL: 인덱스 순서로 테이블을 물리적으로 재정렬 (1회성)
CLUSTER users USING idx_users_pkey;

특징: 범위 검색, 정렬 쿼리에서 매우 빠름 (데이터가 이미 순서대로 있으므로)
단점: 테이블당 1개만 가능, 재정렬 비용 발생

Non-Clustered Index (논클러스터드 인덱스)

별도의 인덱스 구조에 키 값 + 실제 행을 가리키는 포인터(힙 주소)를 저장한다. PostgreSQL의 일반 CREATE INDEX가 여기 해당된다.

인덱스 리프: (email 값, → 실제 row 위치)
                              ↓
                        heap 페이지에서 실제 행 읽기 (Random I/O)

특징: 여러 개 생성 가능
단점: 인덱스 조회 후 힙 페이지를 한 번 더 읽어야 함 (= Table Lookup)

Composite Index (복합 인덱스) + Leftmost Prefix Rule

-- (status, created_at) 복합 인덱스
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Leftmost Prefix Rule은 복합 B-tree가 선두(왼쪽) 컬럼 조건을 가질 때 가장 효율적이라는 강한 설계 휴리스틱이다. 선두 컬럼의 equality 조건과 그 다음 첫 range 조건이 실제 스캔 범위를 가장 안정적으로 줄인다.

-- 강한 기본 패턴: status가 leftmost 조건이다.
SELECT * FROM orders WHERE status = 'PENDING';
SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2026-01-01';

-- 선두 조건이 없어 대개 비효율적이다. 다만 PostgreSQL 18+는 조건부 skip scan을 고려할 수 있다.
SELECT * FROM orders WHERE created_at > '2026-01-01';

PostgreSQL 18부터 B-tree skip scan이 추가되어, 선두 컬럼의 distinct 값이 적고 뒤쪽 컬럼 조건이 충분히 선택적이면 (status, created_at)으로 created_at만 검색해도 planner가 선두 값별 반복 탐색을 선택할 수 있다. 하지만 선두 값이 많으면 거의 전체 인덱스를 읽게 되어 Seq Scan이 더 쌀 수 있다. 따라서 “왼쪽 컬럼이 없으면 절대 못 쓴다”가 아니라 “왼쪽 조건이 가장 안정적으로 범위를 줄이며, skip scan 예외는 버전·통계·비용에 따라 EXPLAIN으로 확인한다”고 기억한다.

컬럼 순서 설계 기준은 “항상 카디널리티가 높은 컬럼 먼저”가 아니다. 실제 쿼리에서 equality 조건으로 고정되는 컬럼, range 조건, ORDER BY, grouping, 결과 행 비율을 함께 본다. (tenant_id, created_at)처럼 tenant_id의 선택도가 낮아도 모든 쿼리가 tenant를 먼저 고정한다면 앞에 두는 것이 맞을 수 있다.

Covering Index (커버링 인덱스)

쿼리에 필요한 모든 컬럼을 인덱스 안에 포함해 Index Only Scan을 가능하게 하는 인덱스다. 실제로 힙 페이지를 건너뛸 수 있는지는 MVCC visibility map 상태에 달려 있다.

-- user_id로 검색해서 email, name만 SELECT하는 경우
CREATE INDEX idx_users_covering ON users(user_id) INCLUDE (email, name);
-- PostgreSQL 11+ INCLUDE 문법 사용

-- EXPLAIN 결과는 "Index Only Scan" 후보가 된다.
-- EXPLAIN (ANALYZE, BUFFERS)의 Heap Fetches는 visibility map에 따라 0보다 클 수 있다.

PostgreSQL 인덱스에는 각 row가 현재 트랜잭션에 보이는지에 대한 MVCC 정보가 없다. 대응 heap page의 all-visible bit가 visibility map에 설정되어 있으면 인덱스만 읽지만, 최근 UPDATE 등으로 bit가 꺼져 있으면 가시성 확인을 위해 heap을 방문한다. 따라서 covering index는 heap-free 실행의 보장이 아니라 필요한 조건 중 하나다.

PostgreSQL에서 TypeORM @Index로는 INCLUDE를 직접 지원하지 않아 마이그레이션에서 직접 생성해야 한다.

3-3. EXPLAIN ANALYZE 읽는 법

기본 사용법

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123 AND status = 'PENDING'
ORDER BY created_at DESC;

출력 예시 (Seq Scan - 인덱스 없을 때)

Seq Scan on orders  (cost=0.00..4821.00 rows=12 width=128)
                     (actual time=0.042..38.721 rows=12 loops=1)
  Filter: ((user_id = 123) AND (status = 'PENDING'::text))
  Rows Removed by Filter: 99988
Planning Time: 0.215 ms
Execution Time: 38.749 ms

출력 예시 (Index Scan - 인덱스 있을 때)

Index Scan using idx_orders_user_status on orders
                     (cost=0.43..16.82 rows=12 width=128)
                     (actual time=0.025..0.187 rows=12 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'PENDING'::text))
Planning Time: 0.318 ms
Execution Time: 0.221 ms

핵심 항목 해석

항목	의미	주목 포인트
`cost=0.43..16.82`	예상 비용 (시작..전체). 단위는 임의 숫자 (8KB 페이지 읽기 = 1)	큰 숫자 = 무거운 작업
`rows=12`	플래너가 예측한 결과 행 수	실제 rows와 크게 다르면 통계 갱신 필요
`actual time=0.025..0.187`	실제 실행 시간(ms). 시작..완료	loops > 1 이면 합산해서 봐야 함
`loops=1`	이 노드가 실행된 횟수	Nested Loop에서 N이 크면 주의
`Rows Removed by Filter`	인덱스 없이 필터로 걸러낸 행 수	이 수가 크면 인덱스 후보

스캔 타입 비교

스캔 타입	동작	언제
Seq Scan	전체 테이블 순차 읽기	인덱스 없거나, 결과가 전체의 ~10% 이상
Index Scan	인덱스 → 힙 페이지 Random I/O	소수 행 정확히 찾을 때
Index Only Scan	인덱스에서 값 조회, 필요 시 힙 가시성 확인	Covering Index + all-visible page가 많을 때
Bitmap Index Scan	인덱스로 비트맵 생성 후 힙 읽기	중간 정도의 행 수

📖 더 보기: PostgreSQL 공식 문서 - Using EXPLAIN — 위 스캔 타입별 동작과 EXPLAIN 출력의 모든 항목 해석법 공식 레퍼런스

BUFFERS 옵션으로 I/O 병목 진단 — 실무 필수

EXPLAIN (ANALYZE, BUFFERS)를 사용하면 각 노드가 얼마나 많은 디스크/메모리 I/O를 수행했는지 확인할 수 있다. 단순한 실행 시간만이 아닌 I/O 근본 원인을 파악할 수 있는 실무 핵심 옵션이다.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'PENDING';

예상 출력 (캐시 히트가 높은 경우)

Index Scan using idx_orders_user_status on orders
  (cost=0.43..16.82 rows=12 width=128)
  (actual time=0.025..0.187 rows=12 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'PENDING'::text))
  Buffers: shared hit=8 read=0
Planning Time: 0.318 ms
Execution Time: 0.221 ms

예상 출력 (디스크 읽기가 많은 경우 — 슬로우 쿼리 원인)

Seq Scan on orders  (cost=0.00..4821.00 rows=12 width=128)
                     (actual time=0.042..185.231 rows=12 loops=1)
  Filter: ((user_id = 123) AND (status = 'PENDING'::text))
  Rows Removed by Filter: 99988
  Buffers: shared hit=12 read=621   ← read=621이 문제! 디스크에서 읽음
Planning Time: 0.215 ms
Execution Time: 185.249 ms

Buffers 항목 해석:

항목	의미	주목 포인트
`shared hit=N`	공유 버퍼(메모리)에서 읽은 페이지 수	높을수록 좋음 (캐시 히트)
`shared read=N`	디스크에서 읽은 페이지 수	높으면 cold cache, 큰 working set, plan 문제, 통계 문제를 분리
`shared dirtied=N`	버퍼에서 변경(더티)된 페이지	UPDATE/DELETE 작업량 확인
`shared written=N`	디스크에 쓰여진 더티 페이지	큰 수 = checkpoint 비용

실무 체크포인트: read 값이 hit 값보다 크면 곧바로 인덱스를 추가하지 않는다. cold cache인지, working set이 메모리보다 큰지, 잘못된 plan인지, 통계가 stale한지 먼저 분리한 뒤 인덱스나 설정 변경을 검토한다.

TypeORM에서 EXPLAIN ANALYZE 확인하기

// TypeORM QueryBuilder에서 직접 실행
const result = await dataSource.query(`
  EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT o.id, o.status, u.email
  FROM orders o
  INNER JOIN users u ON o.user_id = u.id
  WHERE o.status = 'PENDING'
  ORDER BY o.created_at DESC
  LIMIT 20
`);
console.log(result[0]["QUERY PLAN"]);

// TypeORM 쿼리 로그 활성화 (ormconfig / DataSource 옵션)
const dataSource = new DataSource({
  type: "postgres",
  logging: ["error"], // TypeORM의 유효한 logging level만 지정
  maxQueryExecutionTime: 1000, // 별도 'slow' level 없이 1초 초과 쿼리를 로깅
});

TypeORM의 logging level은 query, error, schema, warn, info, log다. slow는 유효한 level이 아니며, 느린 쿼리는 maxQueryExecutionTime으로 기록한다. 모든 쿼리까지 필요할 때만 query를 추가한다.

3-4. 선택 심화: 운영 튜닝이 필요할 때만 돌아오기

처음 읽을 때는 이 절의 세부 knob를 모두 외울 필요가 없다. Nested Loop, Hash Join, Merge Join이 “두 테이블을 어떤 방식으로 붙이는가”만 잡고, work_mem과 random_page_cost는 실행 계획이 이상할 때 다시 돌아오는 선택 심화로 보면 된다. §3-3까지 읽고 Seq Scan, Index Scan, rows, actual rows, BUFFERS 감각이 잡혔다면 바로 §3-5의 N+1 또는 §4의 인덱스 설계로 넘어가도 된다.

§3-3에서 단일 테이블 Seq Scan vs Index Scan을 봤다면, 다음 단계의 EXPLAIN ANALYZE는 Join 노드가 왜 그 알고리즘으로 선택됐는지 를 읽는 능력이다. PostgreSQL은 cost-based optimizer라 3가지 join 알고리즘 중 예상 cost가 가장 낮은 것을 고르며, 통계·work_mem·random_page_cost 같은 설정이 실제 환경과 크게 어긋나면 잘못된 plan을 선택할 수 있다.

3가지 Join 알고리즘

알고리즘	동작 요약	비용 구조	강한 시나리오	깨지는 시나리오
Nested Loop	outer 한 row마다 inner index lookup	O(outer × inner_lookup)	outer 작고(<수백 행) inner index 잘 탐	outer >1000행 + inner도 큰 경우 → 폭발
Hash Join	smaller side로 hash table build → larger side로 probe	O(build + probe). hash table이 work_mem fit 가정	inputs 정렬 안 됨, equality join, work_mem 충분	hash table > work_mem → 디스크 spill (`Batches: N>1`)
Merge Join	양쪽이 정렬된 상태에서 zipper merge	O(left + right). 사전 정렬 필요	양쪽 index sort order로 정렬돼 있을 때	정렬 비용이 추가되면 hash가 더 빠름

Planner의 결정 로직

planner는 각 알고리즘의 예상 cost 를 계산해 가장 낮은 것을 선택한다:

nested_loop_cost = outer_rows × inner_lookup_cost
hash_join_cost   = build_phase + probe_phase + (work_mem 초과 시 disk spill 페널티)
merge_join_cost  = sort_cost(if needed) + merge_cost

cost 산정의 핵심 knob (postgresql.conf):

seq_page_cost (기본 1.0) — 순차 페이지 read 비용
random_page_cost (기본 4.0) — 랜덤 페이지 read 비용. SSD/RAM 캐시 환경에서는 낮춰볼 수 있지만, 쿼리별 EXPLAIN과 통계 문제를 먼저 확인한다.
cpu_tuple_cost (기본 0.01) — row 처리 CPU 비용
cpu_index_tuple_cost (기본 0.005) — index entry 처리 비용
work_mem (기본 4MB) — sort/hash 작업 메모리. 세션·노드당

knob이 환경과 맞지 않으면 planner가 구조적으로 잘못된 선택을 한다.

EXPLAIN ANALYZE에서 join 알고리즘 진단

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.email, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
  AND o.created_at >= '2026-05-01';

출력 예 (간소화):

Nested Loop  (cost=0.71..456.32 rows=120 width=44)
              (actual time=0.045..2.318 rows=87 loops=1)
  ->  Index Scan using users_country_idx on users u
        (cost=0.43..32.12 rows=24 width=12)
        (actual time=0.024..0.341 rows=18 loops=1)
        Index Cond: (country = 'KR')
        Buffers: shared hit=4
  ->  Index Scan using orders_user_created_idx on orders o
        (cost=0.42..17.65 rows=5 width=36)
        (actual time=0.012..0.107 rows=4 loops=18)
        Index Cond: ((user_id = u.id) AND (created_at >= '2026-05-01'))
        Buffers: shared hit=72 read=3

Planning Time: 0.521 ms
Execution Time: 2.418 ms

시니어가 읽는 포인트 4가지:

cost=0.71..456.32 — startup..total cost. planner의 예상 비용 단위 (단위는 임의, 비교용).
rows=120 (예상) vs rows=87 (실제) — 2배 이상 차이나면 통계 부정확 신호 → ANALYZE 재실행 필요.
loops=18 — outer 18행마다 inner lookup. Nested Loop의 outer count 가 작아 OK. 만약 loops=10000+이면 outer가 너무 커서 Hash/Merge로 가야 했음.
Buffers: shared hit=72 read=3 — 캐시 hit 대 디스크 read. read 비율이 높으면 cold cache 또는 shared_buffers 부족.

결정 트리거 — Nested Loop이 잘못 선택됐다고 의심해야 할 때

다음 신호가 1개라도 보이면 planner의 join 알고리즘 선택을 검증해야 한다:

신호	가능한 원인	검증·조치
outer side `rows > 1000` + inner `rows > 100`	통계 부정확 또는 join 선택성 오판	`ANALYZE <table>` 후 재실행. 그래도 같으면 세션에서 `SET enable_nestloop = off`
`Plan rows vs Actual rows` 차이 > 10배	`pg_statistic` stale	`ANALYZE` 즉시. autovacuum 미작동 또는 임계 미달 의심
Hash Join에 `Batches: N (N > 1)`	hash table이 work_mem 초과 → disk spill	`SET work_mem = '256MB'` 세션 단위 증가 후 재시도. 권장은 batch 쿼리 단위 SET
Merge Join 직전에 `Sort` + `Sort Method: external merge Disk`	sort가 work_mem 초과 → 디스크 sort	work_mem 증가 또는 인덱스 추가로 정렬 회피
`random_page_cost` 4.0 + SSD 환경	planner가 index scan 비용을 높게 볼 수 있음	먼저 쿼리별 `EXPLAIN`, 통계, 캐시 상태를 확인하고 세션/스테이징에서 조정 실험
`Planning Time` > `Execution Time`	plan 계산 자체가 병목	prepared statement / plan cache 활용. ORM의 plan cache 동작 확인

Planner 통계 — `ANALYZE`의 역할

planner는 pg_statistic 통계를 기반으로 cost를 추정한다. 통계가 stale하면 모든 결정이 어긋난다.

-- 통계 상태 확인
SELECT
  schemaname, relname,
  last_analyze, last_autoanalyze,
  n_mod_since_analyze AS mods_since_analyze,
  n_live_tup AS live_rows
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 특정 칼럼의 분포 확인
SELECT attname, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'user_id';

-- 강제 ANALYZE (vacuum 없이 통계만)
ANALYZE VERBOSE orders;

-- vacuum + analyze (bloat 정리 포함)
VACUUM (ANALYZE, VERBOSE) orders;

autovacuum_analyze_scale_factor 기본값 0.1 — 테이블 10% 변경 시 자동 ANALYZE. 대용량 테이블(>1억 행)에서는 너무 늦으므로 테이블별로 0.02 등으로 낮춰 잡는다:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

`work_mem` 튜닝 — sort·hash 메모리 한계

work_mem 기본값 4MB. Hash Join build, Sort, CTE materialize 등 세션당, 노드당 사용. 너무 크면 동시 세션이 OOM, 너무 작으면 디스크 spill로 느림.

계산 가이드:
- 동시 active connection N + 평균 노드 사용 노드 M
- 안전 max_work_mem ≈ (RAM × 25%) / (N × M)
- 예: 32GB RAM, 100 connections, 평균 2 node/query → 8GB / 200 = 40MB

세션 단위 일시 증가 (큰 batch 쿼리만 — 이게 안전한 패턴):
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ... 큰 join ...;
COMMIT;
-- SET LOCAL은 트랜잭션 종료 시 자동 RESET

전역 work_mem을 무작정 늘리면 peak 동시 세션 수 만큼 RAM이 곱해진다. peak 200 connection × work_mem 256MB × node 2 = 100GB OOM 위험.

운영 장애나 대용량 쿼리 분석 때의 참고 절차

이 체크리스트는 첫 회독 필수 암기 목록이 아니다. 운영 장애나 대용량 쿼리 분석에서 슬로우 쿼리 1개를 받았을 때 30초 안에 확인할 순서 다:

운영 장애나 대용량 쿼리 분석 때의 EXPLAIN ANALYZE 참고 절차

**1단계**: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` 로 실행 (BUFFERS·VERBOSE 필수)
**2단계**: `Plan rows vs Actual rows` 차이 10배 이상인 노드 찾기 → 통계 지연을 의심하고 `ANALYZE <table>` 검토
**3단계**: Nested Loop `loops > 10,000` 또는 outer `rows > 1000`이면 Hash/Merge 검토
**4단계**: Hash Join `Batches > 1` 또는 `Sort Method: external merge Disk`면 쿼리 범위 축소, 인덱스, 세션 단위 `work_mem` 실험 순서로 검토
**5단계**: `Buffers: shared read >> hit`면 OS page cache 또는 `shared_buffers` 부족 점검
**6단계**: SSD인데 `random_page_cost = 4.0` 기본값이면 통계·캐시·스토리지 특성 확인 후 세션/스테이징에서 조정 실험
**7단계**: `Planning Time > Execution Time`이면 prepared statement·plan cache 활용
**8단계**: 그래도 안 되면 `enable_nestloop=off` / `enable_hashjoin=off` 로 _다른 plan_ 강제 후 비교 (production은 세션 단위로만)

실전 예 — “동일 쿼리가 어제는 100ms, 오늘은 30s”

가장 흔한 패턴. EXPLAIN ANALYZE 한 번 돌려보면:

어제: Nested Loop (rows=1200 actual=1180)
오늘: Nested Loop (rows=1200 actual=180,000)  ← 통계 stale
                                ↑ 150배 차이!
Planning Time: 0.3 ms
Execution Time: 31,420 ms

진단: 어제 데이터로 추정된 rows=1200은 오늘 통계 갱신 전이라 그대로 사용됨 → planner가 작은 outer 라 Nested Loop 선택 → 실제는 18만 row × inner lookup으로 폭발.

복구 (1분 안):

ANALYZE orders;        -- 통계 갱신
-- 그래도 같은 plan이면 work_mem 증가 + plan cache invalidate
DISCARD PLANS;          -- 세션의 prepared statement 캐시 폐기

근본 대응: autovacuum_analyze_scale_factor 낮추기 + pg_stat_statements로 plan 변동 모니터링.

3-5. N+1 쿼리 비용 — 모델링 문제가 아니라 쿼리 모양 문제

N+1 문제는 관계 자체가 나쁜 것이 아니라, 관계 데이터를 가져오는 쿼리 모양이 반복 조회로 풀렸을 때 생긴다. 주문 100개와 주문자 이메일을 보여주는 화면을 만든다고 하자. 주문 목록을 한 번 조회한 뒤 각 주문마다 users를 다시 조회하면, 필요한 관계는 단순한 orders.user_id = users.id 조인인데도 DB에는 101번의 요청이 들어간다.

이 문서에서는 N+1을 테이블 설계나 정규화의 정답 문제로 다루지 않는다. db-modeling 문서가 관계 모양을 책임지고, 여기서는 같은 관계라도 ORM 사용 방식에 따라 실행 쿼리 수와 조인 비용이 어떻게 달라지는지만 본다.

발생 원리

// ❌ N+1 문제 발생 코드
// 1번 쿼리: 모든 주문 조회
const orders = await orderRepository.find();

// N번 쿼리: 각 주문마다 user 조회 (orders.length만큼 반복!)
for (const order of orders) {
  const user = await userRepository.findOne({ where: { id: order.userId } });
  console.log(user.email);
}

실제 발생하는 쿼리 로그:

-- 1번 쿼리
SELECT * FROM orders;

-- 이후 orders 수만큼 반복 (N=100이면 100번!)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ... 총 N+1 = 101번

해결책 비교

방법 1: find({ relations }) - 간단한 경우

// ✅ relations 옵션으로 JOIN 수행
const orders = await orderRepository.find({
  relations: ["user"], // LEFT JOIN users
  where: { status: "PENDING" },
});

// 발생 쿼리 (단 1번):
// SELECT orders.*, users.* FROM orders
// LEFT JOIN users ON orders.user_id = users.id
// WHERE orders.status = 'PENDING'

방법 2: QueryBuilder leftJoinAndSelect - 복잡한 조건

// ✅ QueryBuilder로 세밀한 JOIN 제어
const orders = await orderRepository
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user") // user 컬럼 SELECT 포함
  .leftJoinAndSelect("order.items", "item") // items도 JOIN
  .where("order.status = :status", { status: "PENDING" })
  .andWhere("order.createdAt > :date", { date: startDate })
  .orderBy("order.createdAt", "DESC")
  .take(20)
  .getMany();

// 발생 쿼리 (단 1번):
// SELECT order.*, user.*, item.*
// FROM orders order
// LEFT JOIN users user ON order.user_id = user.id
// LEFT JOIN order_items item ON item.order_id = order.id
// WHERE order.status = 'PENDING'
//   AND order.created_at > $1
// ORDER BY order.created_at DESC
// LIMIT 20

방법 3: Eager Loading - 항상 같이 쓰이는 관계

// Entity 정의
@Entity()
export class Order {
  @ManyToOne(() => User, { eager: true }) // Order 조회 시 항상 User JOIN
  user: User;
}

주의: eager: true는 모든 find() 에 적용되어 불필요한 JOIN이 생길 수 있다. 신중히 사용.

비교 정리

방법	쿼리 수	적합한 경우
`find()` 반복	N+1	절대 사용 금지
`find({ relations })`	1	단순 관계, 빠른 개발
QueryBuilder JOIN	1	복잡한 조건, 세밀한 제어
Eager Loading	1	항상 함께 쓰이는 필수 관계
DataLoader	배치	GraphQL, 동적 관계

DataLoader는 GraphQL resolver처럼 관계가 동적으로 열리는 환경에서 여러 개의 id 조회를 한 번의 batch query로 묶는 패턴이다. 로그에서 같은 SELECT ... WHERE id = $1 형태가 요청 하나 안에서 반복되면 N+1 후보로 본다.

3-6. TypeORM `@Index()` 데코레이터

import { Entity, Column, Index, PrimaryGeneratedColumn } from "typeorm";

// 단일 컬럼 인덱스
@Entity()
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Index() // 단일 인덱스: idx_order_user_id
  @Column()
  userId: number;

  @Index()
  @Column()
  status: string;

  @Column()
  createdAt: Date;
}

// 복합 인덱스 (Entity 레벨에서 선언)
@Entity()
@Index(["userId", "status"]) // (userId, status) 복합 인덱스
@Index(["status", "createdAt"]) // (status, createdAt) 복합 인덱스
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  status: string;

  @Column()
  createdAt: Date;
}

// 유니크 인덱스
@Index(["email"], { unique: true })
@Entity()
export class User {
  @Column()
  email: string;
}

Covering Index (INCLUDE) - 마이그레이션으로 직접 생성

// TypeORM 마이그레이션 파일
export class AddCoveringIndex1234567890 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE INDEX idx_orders_covering
      ON orders(user_id, status)
      INCLUDE (created_at, total_amount)
    `);
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX idx_orders_covering`);
  }
}

4. 실무에서 어떻게 쓰이나

인덱스 설계 기준 (체크리스트)

WHERE 절에 자주 등장하는 컬럼 → 인덱스 후보 1순위
JOIN ON 조건 컬럼 → 반드시 인덱스 (FK 컬럼은 PostgreSQL이 자동 생성하지 않음!)
ORDER BY, GROUP BY 컬럼 → 인덱스로 정렬 비용 제거 가능
카디널리티 확인 → 값의 종류가 매우 적은 컬럼(예: is_deleted만 있는 컬럼, true/false)은 인덱스 효과 낮음
복합 인덱스 순서 → 자주 쓰는 단독 조건 컬럼 먼저 (Leftmost Prefix Rule)

인덱스 추가 정량 판단 기준

인덱스는 읽기 성능을 높이는 대신 쓰기(INSERT/UPDATE/DELETE) 시 인덱스 갱신 비용이 추가된다. 아래 기준을 참고해 추가 여부를 판단한다.

판단 기준	인덱스 추가 권장	인덱스 효과 낮음
카디널리티(선택도)	결과가 전체 행의 ~10% 미만을 반환할 때	결과가 10% 이상 — Planner가 Seq Scan을 선택
컬럼 고유값 수	`user_id`처럼 고유값 수가 많을수록 유리	`status`(3~5가지), `is_deleted`(2가지)는 단독 인덱스 효과 낮음
쓰기:읽기 비율	읽기가 압도적으로 많을 때 (예: 조회 API)	초당 수만 건 INSERT 테이블 — 인덱스당 쓰기 오버헤드 누적
인덱스 수	테이블당 인덱스 5개 이하를 권장 기준으로 검토	인덱스가 많을수록 `VACUUM`, `ANALYZE` 비용도 증가

📖 출처: PostgreSQL 공식 문서 — Indexes Chapter, pganalyze — How Postgres Chooses Which Index To Use

실용 판단 순서

1. pg_stat_statements에서 mean_exec_time > 10ms인 쿼리 추출
2. EXPLAIN ANALYZE → Seq Scan이고 "Rows Removed by Filter"가 크면 인덱스 후보
3. 해당 컬럼의 고유값 수 확인: SELECT COUNT(DISTINCT col) / COUNT(*) FROM table;
   → 이 값은 컬럼 cardinality 감각일 뿐, 특정 WHERE 조건의 반환 비율을 직접 말하지는 않는다. 실제 predicate의 selectivity는 MCV, histogram, `EXPLAIN`의 추정 rows와 실제 rows 차이로 함께 본다.
4. 쓰기가 많은 테이블은 인덱스 추가 전 EXPLAIN 비용과 초당 DML 빈도를 함께 검토

이 판단은 PostgreSQL의 planner cost와도 연결된다. random_page_cost 기본값은 4.0, seq_page_cost 기본값은 1.0이며, PostgreSQL 문서는 random_page_cost를 낮추면 index scan 선호가 커지고 올리면 index scan 비용이 더 비싸게 평가된다고 설명한다. 즉 SSD/RAM 캐시가 충분한 운영 DB에서는 같은 인덱스라도 planner가 더 적극적으로 사용할 수 있지만, PostgreSQL 공식 문서가 경고하듯 몇 번의 실험만으로 cost 상수를 바꾸는 것은 위험하다. 먼저 쿼리별 EXPLAIN (ANALYZE, BUFFERS)와 ANALYZE로 통계 문제를 제거한 뒤 조정한다.

출처: PostgreSQL 공식 문서 — Query Planning

실제 쿼리 흐름 예시: 인덱스를 추가해야 하는 경우

BackOps 정산 API: "이번 달 COMPLETED 상태의 주문을 user_id별로 합산"

인덱스 없을 때: Seq Scan → 주문 100만 건 전체 스캔 → 5초
인덱스 추가 후: Index Scan on (status, created_at) → 0.05초

이 경우는 status 단독 인덱스보다 (status, created_at, user_id) 또는 (created_at, status) 후보를 실제 쿼리의 WHERE, GROUP BY, ORDER BY 순서로 비교한다. 예를 들어 최근 30일 데이터가 전체의 3%이고 status='COMPLETED'가 전체의 70%라면, status만 앞에 둔 인덱스는 선택도가 낮아 Seq Scan이 유지될 수 있다. 반대로 created_at >= now() - interval '30 days'가 먼저 후보를 좁히면 Bitmap/Index Scan으로 바뀔 가능성이 높다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, sum(total_amount)
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= now() - interval '30 days'
GROUP BY user_id;

기대 출력은 Seq Scan이 Index Scan 또는 Bitmap Index Scan으로 바뀌고 Rows Removed by Filter와 Buffers: shared read가 줄어드는 것이다. 출력이 그대로라면 인덱스가 틀린 것이 아니라 통계가 오래됐거나, 결과 행 비율이 너무 커서 planner가 Seq Scan을 더 싸게 본 것일 수 있다. 이때 첫 조치는 새 인덱스 추가가 아니라 ANALYZE orders; 후 같은 plan을 다시 보는 것이다. PostgreSQL의 autovacuum_analyze_scale_factor 기본값은 0.1이라 1억 행 테이블에서는 약 1,000만 행 변경 전까지 자동 ANALYZE가 늦을 수 있다.

SELECT relname, last_autoanalyze, n_mod_since_analyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

예상 출력에서 n_mod_since_analyze / n_live_tup이 0.1에 가까운데 last_autoanalyze가 오래됐다면, 통계 지연으로 잘못된 plan이 선택됐을 가능성을 먼저 의심한다.

출처: PostgreSQL 공식 문서 — Vacuuming / autovacuum_analyze_scale_factor

5. 내 업무와 어떻게 연결되나

배치 정산 작업: WHERE status = 'PENDING' AND processed_at IS NULL 형태의 쿼리가 느리다면 복합 인덱스 (status, processed_at) 추가
TypeORM Repository 메서드 검토: find() 루프 → find({ relations }) 또는 QueryBuilder로 리팩토링
AWS RDS 모니터링: CloudWatch의 DatabaseConnections, ReadLatency 지표가 높을 때 슬로우 쿼리를 pg_stat_statements로 추적
배포 후 인덱스 추가: CREATE INDEX CONCURRENTLY로 쓰기를 막는 락을 피한다. lock-free 작업은 아니며 기존 트랜잭션 대기, 추가 CPU/I/O, schema 변경 제한이 남는다.

-- 운영 중 안전하게 인덱스 추가
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders(status, created_at);

6. 비교 / 대안

접근 방법	장점	단점	언제 선택
B-Tree 인덱스	범용적, 범위/정렬 지원	쓰기 시 인덱스 갱신 비용	대부분의 경우
Hash 인덱스	동등 검색 O(1)	범위 검색 불가	오직 `=` 검색만 할 때
Partial 인덱스	조건부 행만 인덱싱 → 크기 작음	조건 외 쿼리에선 무효	`WHERE deleted_at IS NULL` 같은 패턴
GIN 인덱스	전문 검색, JSONB, 배열	공간 많이 차지	`@>`, `?` 연산자, 전문 검색
BRIN 인덱스	매우 작은 크기	물리적 정렬된 데이터에서만 효과	타임스탬프처럼 순서대로 쌓이는 대용량
`find({ relations })`	간단한 코드	조건 제어 어려움	단순 관계 로딩
QueryBuilder JOIN	세밀한 제어	코드 복잡	복잡한 조건

Partial Index 예시

-- 삭제 안 된 활성 주문만 인덱싱 → 인덱스 크기 대폭 감소
CREATE INDEX idx_orders_active
ON orders(user_id, created_at)
WHERE deleted_at IS NULL;

6.5. 실패 신호로 읽는 쿼리 최적화

트러블슈팅을 명령어 순서로 외우면 상황이 조금만 바뀌어도 막힌다. 더 좋은 학습 방식은 관측된 증상 → 그 증상이 뜻하는 실행 계획 문제 → 먼저 확인할 개념으로 연결하는 것이다. 아래 항목은 운영 절차가 아니라, EXPLAIN과 통계 지표를 볼 때 놓치지 말아야 할 실패 신호다.

신호 1: 인덱스를 만들었는데도 `Seq Scan`이 남는다

Seq Scan은 항상 나쁜 신호가 아니다. 결과 행이 전체의 큰 비율이면 PostgreSQL은 인덱스를 오가며 랜덤 I/O를 반복하는 것보다 테이블을 순서대로 읽는 편이 싸다고 판단할 수 있다. 문제는 “분명히 소수 행만 필요한데도” Seq Scan이 남는 경우다.

먼저 확인할 질문은 네 가지다.

조건이 인덱스 컬럼을 그대로 사용하고 있는가.
결과 행 비율이 실제로 작은가.
컬럼 타입이 조건 값 타입과 맞는가.
통계가 최신인가.

타입이 맞지 않는 예는 짧지만 자주 놓친다.

-- status가 text 컬럼인데 숫자로 비교하면 암묵적 캐스팅 때문에 인덱스 사용성이 깨질 수 있다.
WHERE status = 1

-- 컬럼 타입에 맞춰 비교한다.
WHERE status = '1'

함수로 컬럼을 감싸면 일반 B+Tree 인덱스의 정렬 순서를 그대로 사용할 수 없다.

-- 인덱스가 email에 있어도 컬럼에 함수를 씌우면 일반 인덱스를 못 탈 수 있다.
WHERE LOWER(email) = 'user@example.com'
WHERE DATE(created_at) = '2026-04-01'

-- 값을 바꾸고 컬럼을 그대로 비교하는 편이 기본적으로 안전하다.
WHERE email = LOWER('USER@EXAMPLE.COM')
WHERE created_at >= '2026-04-01'
  AND created_at <  '2026-04-02'

정말 함수 결과로 검색해야 한다면 expression index를 검토한다.

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

이 선택의 경계는 명확하다. expression index도 별도 인덱스이므로 쓰기 비용과 저장 공간을 늘린다. 해당 조건이 자주 호출되고 결과 행 비율이 작다는 근거가 있을 때만 만든다.

앞부분 와일드카드도 B+Tree의 정렬 이점을 깨뜨린다.

-- 앞에서부터 정렬된 키를 좁힐 수 없다.
WHERE title LIKE '%결제%'

-- 접두어가 고정되면 시작 위치를 찾을 수 있다.
WHERE title LIKE '결제%'

전문 검색이나 중간 문자열 검색이 핵심 요구라면 B+Tree가 아니라 GIN, trigram, 검색 엔진 같은 별도 구조를 검토한다. pg_trgm의 trigram은 문자열을 3글자 조각으로 나누어 유사도나 중간 문자열 검색 후보를 좁히는 방식이다. 이 문서의 핵심은 “B+Tree가 만능이 아니다”라는 경계다.

신호 2: `rows` 예측과 실제 행 수가 크게 다르다

EXPLAIN ANALYZE에서 rows=120은 플래너의 예상이고, actual rows=180000은 실제 실행 결과다. 두 값이 10배 이상 차이 나면 인덱스 문제가 아니라 통계 문제일 수 있다. 플래너는 데이터 분포를 직접 매번 세지 않고 pg_statistic 통계를 보고 비용을 추정한다.

예를 들어 어제까지는 status='PENDING' 주문이 1,200건이었는데 오늘 대량 적재 후 18만 건이 됐다고 하자. 통계가 갱신되지 않으면 플래너는 여전히 작은 결과라고 믿고 Nested Loop와 인덱스 lookup을 고를 수 있다. 실제로는 18만 번 반복되어 실행 시간이 폭발한다.

최소 확인 SQL은 아래 정도면 충분하다.

SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_mod_since_analyze,
  n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

n_mod_since_analyze / n_live_tup 비율이 높고 last_autoanalyze가 오래됐다면 ANALYZE orders;로 통계만 갱신한 뒤 같은 EXPLAIN ANALYZE를 다시 본다. VACUUM은 죽은 튜플 정리까지 포함하므로, 단순 통계 갱신이 목적이면 ANALYZE가 더 직접적이다.

신호 3: `Rows Removed by Filter`가 매우 크다

Rows Removed by Filter: 99988은 “읽은 뒤 버린 행”이 많다는 뜻이다. 이 숫자가 크면 인덱스 후보를 의심할 수 있지만, 바로 인덱스를 추가하면 안 된다. 버린 행이 많다는 사실만으로는 결과 행 비율, 정렬 요구, 쓰기 비용을 알 수 없기 때문이다.

판단 순서는 다음이 실용적이다.

확인 항목	질문	인덱스 후보 신호
결과 비율	전체 중 몇 %를 반환하는가	대략 10% 미만이면 후보
호출 빈도	이 쿼리가 하루 몇 번 실행되는가	평균 2ms라도 수만 번이면 개선 가치가 큼
정렬/그룹화	`ORDER BY`, `GROUP BY`가 같은 컬럼인가	정렬 비용까지 줄이면 가치가 커짐
쓰기 부하	초당 INSERT/UPDATE/DELETE가 많은가	쓰기가 많으면 인덱스 추가 비용을 더 크게 봄
기존 인덱스 중복성	비슷한 복합 인덱스가 이미 있는가	새 인덱스보다 기존 순서 조정이 나을 수 있음

반례도 기억해야 한다. status='COMPLETED'가 전체의 70%라면 Rows Removed by Filter가 많아 보여도 status 단독 인덱스는 효과가 낮다. 반대로 최근 30일이 전체의 3%이고 그 안에서 status를 필터링한다면 (created_at, status)가 더 나을 수 있다.

신호 4: Nested Loop의 `loops`가 너무 크다

Nested Loop는 outer 쪽 행마다 inner 쪽을 다시 찾는다. outer가 18행이고 inner lookup이 인덱스를 잘 타면 빠르다. outer가 18만 행이면 같은 구조가 폭발한다. loops=18은 정상일 수 있지만, loops=10000 이상이면서 inner 쪽도 큰 테이블이면 조인 전략을 의심해야 한다.

조인 전략은 테이블 설계의 선악이 아니라 현재 쿼리 모양과 예상 행 수의 결과다.

조인 전략	좋은 상황	실패 신호
Nested Loop	outer가 작고 inner lookup 인덱스가 있음	outer가 커져 `loops`가 폭증
Hash Join	동등 조인이고 build side가 메모리에 들어감	`Batches: N>1`로 디스크 spill 발생
Merge Join	양쪽이 이미 정렬되어 있음	조인 전 `Sort`가 크고 external merge가 발생

SET enable_nestloop = off 같은 강제 옵션은 학습용 비교나 세션 단위 진단에만 쓴다. 운영 해결책은 대개 통계 갱신, 인덱스 순서 조정, 쿼리 조건 축소, work_mem을 특정 배치 세션에서만 늘리는 쪽이다.

신호 5: Hash Join이나 Sort가 디스크로 spill 된다

work_mem은 정렬과 해시 작업에 쓰이는 작업 메모리다. 한 쿼리 전체에 한 번만 쓰는 메모리가 아니라, 세션·노드별로 쓸 수 있다. 그래서 전역 값을 크게 올리면 동시 쿼리 수와 실행 노드 수만큼 메모리가 곱해진다.

수치 감각은 다음처럼 잡는다.

32GB RAM 서버에서 작업 메모리에 전체의 25%인 8GB만 쓰겠다고 가정
동시 active connection 100개
쿼리당 sort/hash 노드 평균 2개
안전한 work_mem 대략값 = 8GB / (100 * 2) = 40MB

큰 배치 쿼리 하나만 문제라면 전역 설정을 바꾸기보다 트랜잭션 안에서 SET LOCAL work_mem = '256MB'처럼 범위를 좁힌다. 이 값은 트랜잭션이 끝나면 되돌아가므로 피크 동시성 리스크가 작다.

신호 6: Index Only Scan이 기대와 달리 힙을 계속 읽는다

Covering Index는 필요한 컬럼을 인덱스 안에 포함해 힙 페이지 접근을 줄인다. PostgreSQL에서는 INCLUDE 컬럼으로 구현할 수 있다. 하지만 Index Only Scan이 항상 힙을 0번 읽는다는 뜻은 아니다. MVCC 가시성 확인이 필요하면 힙을 읽을 수 있고, visibility map이 충분히 갱신되지 않았으면 기대보다 효과가 작다. visibility map은 어떤 heap page의 tuple들이 모든 트랜잭션에서 볼 수 있는 상태인지 표시하는 보조 구조다.

선택 기준은 다음과 같다.

SELECT 컬럼이 적고 고정되어 있다.
같은 조건의 읽기 요청이 매우 많다.
테이블 행은 넓지만 필요한 응답 컬럼은 작다.
쓰기 빈도가 낮거나, 읽기 절감 효과가 쓰기 비용보다 크다.

반대로 관리자 목록처럼 매번 다른 컬럼을 많이 선택하거나, 테이블이 자주 갱신되어 visibility map이 자주 깨지는 경우에는 covering index의 이득이 줄어든다.

신호 7: 인덱스 크기가 계속 커지고 성능이 천천히 나빠진다

Index bloat는 인덱스 안에 더 이상 유효하지 않은 항목과 빈 공간이 누적되어 크기가 커지는 현상이다. PostgreSQL의 MVCC는 UPDATE/DELETE 때 이전 행 버전을 즉시 덮어쓰지 않으므로, 테이블과 인덱스 모두 정리 대상이 생긴다.

여기서 중요한 경계는 VACUUM과 REINDEX의 역할 차이다.

작업	주 역할	경계
`VACUUM`	죽은 튜플을 재사용 가능한 공간으로 표시	파일 크기나 인덱스 구조를 항상 줄이지 않음
`ANALYZE`	플래너 통계 갱신	bloat 자체를 줄이지 않음
`REINDEX`	인덱스를 새로 만들어 물리적 비대화를 줄임	비용과 락 또는 concurrent 빌드 시간이 듦

따라서 “쿼리가 느리다 → REINDEX”가 아니라, 인덱스 크기 증가, dead tuple 비율, UPDATE 패턴, HOT update 가능 여부를 같이 봐야 한다. HOT(Heap Only Tuple) update는 인덱스 컬럼이 바뀌지 않는 UPDATE에서 인덱스 갱신을 피하는 최적화다. 인덱스 컬럼을 자주 UPDATE하면 HOT가 깨지고 bloat가 빨리 커질 수 있다.

신호 8: `CREATE INDEX` 후 쓰기가 멈추거나 `INVALID` 인덱스가 남는다

운영 중 큰 테이블에 일반 CREATE INDEX를 실행하면 쓰기 작업이 대기할 수 있다. CREATE INDEX CONCURRENTLY는 동시 INSERT/UPDATE/DELETE를 막는 락을 잡지 않도록 여러 단계로 인덱스를 만들지만, lock-free는 아니다. 기존 트랜잭션이 끝나기를 기다리고, schema 변경은 제한하며, 추가 CPU/I/O를 사용한다. 일반 빌드보다 오래 걸리고 트랜잭션 블록 안에서 실행할 수도 없다.

가장 위험한 실패 신호는 INVALID 인덱스다. concurrent index build가 중간에 실패하면 쿼리에는 사용되지 않지만, 업데이트 오버헤드는 만드는 인덱스가 남을 수 있다. 생성 직후에는 pg_index.indisvalid를 확인해야 한다.

SELECT i.relname AS index_name, ix.indisvalid, ix.indisready
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE i.relname = 'idx_orders_status';

indisvalid = false면 “배포는 성공했는데 쿼리는 여전히 느린” 상태가 될 수 있다. 복구 절차는 운영 정책에 따라 다르지만, 개념적으로는 무효 인덱스를 정리하고 단독 명령으로 다시 만드는 흐름이다. 자세한 명령은 선택 부록에 둔다.

신호 9: `pg_stat_statements`에서 평균은 낮은데 총 시간이 크다

pg_stat_statements는 PostgreSQL 쿼리 통계 확장이다. 이 도구의 학습 포인트는 “가장 느린 쿼리 1개”만 찾는 것이 아니라, 자주 호출되어 총 시간을 많이 쓰는 쿼리를 찾는 데 있다.

예를 들어 평균 2.5ms 쿼리가 50,000번 호출되면 총 125초다. 평균 800ms 쿼리가 하루 5번 호출되는 것보다 사용자 경험과 DB 부하에 더 큰 영향을 줄 수 있다. 그래서 개선 우선순위는 mean_exec_time, total_exec_time, calls, rows를 함께 봐야 한다.

관측값	의미	판단 방향
`mean_exec_time` 큼	개별 요청이 느림	EXPLAIN으로 단일 쿼리 plan 개선
`total_exec_time` 큼	DB 시간을 많이 소비	호출 빈도까지 포함해 우선 개선
`calls` 큼	애플리케이션 쿼리 모양 반복 가능성	캐시, 배치, N+1, 페이지네이션 확인
`rows` 큼	반환량 자체가 큼	API 응답 범위, cursor pagination, 집계 전략 검토

7. 체크리스트

7. 체크리스트 체크

`EXPLAIN ANALYZE`에서 읽은 행/페이지, 결과 비율, 통계 최신성을 확인했는가?
TypeORM `find()` + 루프 패턴이 없는가? (`relations` 또는 QueryBuilder 사용)
FK 컬럼(예: `user_id`, `order_id`)에 인덱스가 걸려 있는가?
복합 인덱스 선두 컬럼이 주요 equality 조건과 맞는가? PG18+ skip scan 예외는 EXPLAIN으로 확인했는가?
`WHERE LOWER(col)`, `WHERE DATE(col)` 같은 함수 감싸기를 하지 않는가?
`LIKE '%keyword'` 패턴을 사용하고 있지 않은가?
pg_stat_statements로 슬로우 쿼리를 주기적으로 점검하는가?
운영 중 인덱스 추가 시 `CREATE INDEX CONCURRENTLY`를 사용하는가?
대량 데이터 변경 후 `ANALYZE` 로 통계를 갱신하는가?
인덱스 사용 현황을 `pg_stat_user_indexes`로 확인해 미사용 인덱스를 정리하는가?

8. 키워드

키워드	한 줄 설명
B-tree/B+Tree	PostgreSQL 공식 명칭은 B-tree. 학습 모델로는 B+Tree식 리프 중심 구조를 함께 이해
Clustered Index	테이블 데이터 자체가 정렬된 인덱스 (PK 기반)
Non-Clustered Index	별도 구조로 키+포인터 저장. 대부분의 CREATE INDEX
Composite Index	여러 컬럼 복합 인덱스. 선두 컬럼 조건이 가장 효율적이며 PG18+에는 skip scan 예외가 있음
Covering Index	필요한 모든 컬럼을 포함해 Index Only Scan을 가능하게 하는 인덱스
Seq Scan	전체 테이블 순차 스캔. 인덱스 없거나 결과 비율이 클 때
Index Scan	인덱스 → 힙 Random I/O. 소수 행 검색
Index Only Scan	인덱스에서 값을 반환하되 visibility map이 all-visible이 아니면 힙을 확인
EXPLAIN ANALYZE	쿼리 실행 계획 + 실제 실행 결과 동시 출력
N+1 Problem	1번 조회 후 N번 추가 쿼리 발생. ORM 관계 로딩 실수
QueryBuilder	TypeORM의 세밀한 SQL 생성 API
pg_stat_statements	PostgreSQL 확장. 슬로우 쿼리 통계 수집
Leftmost Prefix Rule	복합 B-tree는 선두 컬럼 조건이 있을 때 가장 효율적이라는 강한 휴리스틱
Index Bloat	인덱스 내 dead tuple 누적으로 크기 비대화
REINDEX CONCURRENTLY	운영 중 쓰기 차단을 줄여 인덱스를 재빌드하는 방식. lock-free는 아님 (PG 12+)
Partial Index	특정 조건의 행만 인덱싱하는 부분 인덱스

8.5. 추천 리소스

📖 PostgreSQL 공식 문서 - Using EXPLAIN — EXPLAIN ANALYZE의 모든 옵션과 각 노드 타입(Seq Scan, Index Scan 등) 공식 레퍼런스 (입문)
📖 Understanding the Mechanics of PostgreSQL B-Tree Indexes - PostgreSQL Fastware — B-Tree 구조를 페이지 단위까지 시각적으로 설명. pageinspect 확장으로 직접 확인하는 법 포함 (중급)
📖 Solving N+1 Problem in NestJS with TypeORM - Medium — NestJS + TypeORM 환경에서 N+1 발생 원인과 4가지 해결책 코드 예시 (입문)
📖 How to Use pg_stat_statements for Query Analysis - OneUptime — pg_stat_statements 설치부터 슬로우 쿼리 진단까지 실전 가이드, EXPLAIN ANALYZE 해석법 포함 (중급)
📖 TypeORM Indices 공식 문서 — @Index() 데코레이터 사용법, 복합 인덱스, 유니크 인덱스 등 TypeORM 인덱스 전체 레퍼런스 (입문)

출처

9. 직접 확인해보기

실습 1: 인덱스 유무에 따른 성능 차이 측정

-- 1. 테스트 테이블 생성 및 대량 데이터 삽입
CREATE TABLE test_orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO test_orders (user_id, status, created_at)
SELECT
  (random() * 10000)::INT,
  (ARRAY['PENDING', 'COMPLETED', 'CANCELLED'])[floor(random() * 3 + 1)],
  NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000);  -- 100만 건

-- 2. 인덱스 없이 실행 (Seq Scan 확인)
EXPLAIN ANALYZE
SELECT * FROM test_orders
WHERE status = 'PENDING' AND user_id = 1234;

-- 3. 인덱스 생성
CREATE INDEX idx_test_orders_user_status
ON test_orders(user_id, status);

-- 4. 동일 쿼리 재실행 (Index Scan 확인)
EXPLAIN ANALYZE
SELECT * FROM test_orders
WHERE status = 'PENDING' AND user_id = 1234;

-- 5. 정리
DROP TABLE test_orders;

실습 2: TypeORM N+1 재현 및 해결

import { DataSource } from "typeorm";

// DataSource에 logging: ['query'] 설정
// 1. N+1 발생 버전 실행 → 로그에서 반복 쿼리 확인
const orders = await orderRepo.find();
for (const order of orders) {
  const user = await userRepo.findOne({ where: { id: order.userId } });
}

// 2. 해결 버전 실행 → 쿼리 1번만 발생 확인
const orders = await orderRepo
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user")
  .getMany();

실습 3: pg_stat_statements로 슬로우 쿼리 찾기

-- 1. 설정 확인
SHOW shared_preload_libraries;  -- pg_stat_statements 포함 확인

-- 2. 통계 초기화 후 쿼리 실행
SELECT pg_stat_statements_reset();

-- 3. 실제 쿼리들 실행 (애플리케이션 사용 or 직접 실행)

-- 4. 슬로우 쿼리 확인
SELECT
  left(query, 100) AS query_preview,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

실습 4: 안티패턴 확인

-- 아래 쿼리들이 인덱스를 타는지 EXPLAIN으로 확인
-- (idx_test_orders_user_status 인덱스가 있다고 가정)

-- 안티패턴 1: 함수 감싸기
EXPLAIN SELECT * FROM test_orders WHERE LOWER(status) = 'pending';
-- → Seq Scan 발생

-- 안티패턴 2: LIKE 앞 와일드카드
EXPLAIN SELECT * FROM test_orders WHERE status LIKE '%END%';
-- → Seq Scan 발생

-- 정상 패턴
EXPLAIN SELECT * FROM test_orders WHERE status = 'PENDING';
-- → Index Scan 발생

실습 후 자가 점검 질문

실습을 마친 뒤 아래 질문에 스스로 답할 수 있으면 개념이 내재화된 것이다.

EXPLAIN ANALYZE 해석

cost=0.00..4821.00에서 앞의 0.00과 뒤의 4821.00은 각각 무엇을 의미하는가?
rows=12(예측)와 actual rows=1200(실제)의 차이가 크면 어떻게 대응하는가?
Rows Removed by Filter: 99988 이 표시됐을 때 가장 먼저 할 행동은?
Buffers: shared hit=5 read=620 결과를 보고 어떤 결론을 내리는가?

인덱스 설계 판단

SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders; 결과가 0.003(0.3%)라면 status 단독 인덱스를 추가하겠는가? 이유는?
동일 테이블에 인덱스가 8개 있다면 쓰기 성능에 어떤 영향이 있는가?
MySQL InnoDB에서 보조 인덱스 조회가 PostgreSQL Non-Clustered 인덱스 조회보다 항상 느린가? 왜 그런가?

목표 수치 기준

슬로우 쿼리를 개선했을 때 “성공”을 판단하는 지표는 무엇인가? (예: mean_exec_time, Seq Scan 제거, actual time ms)
인덱스 추가 후 pg_stat_user_indexes의 어떤 컬럼을 2주 후 확인해야 하는가?

10. 요약

핵심 포인트	한 줄 요약
B-tree/B+Tree 구조	루트→브랜치→리프(연결 리스트), O(log N), 범위 검색 강점
인덱스 종류	Clustered(정렬된 실제 데이터), Non-Clustered(별도 구조+포인터), Composite(선두 컬럼 우선, PG18+ skip scan 예외), Covering(Index Only Scan 가능)
EXPLAIN ANALYZE	cost=예상비용, rows=예상행수, actual time=실제시간. 목표는 읽는 행과 페이지를 줄이는 것
N+1 해결	`find({ relations })` 또는 `.leftJoinAndSelect()` 로 단일 쿼리로 통합
안티패턴	함수 감싸기(LOWER, DATE), LIKE ‘%keyword’, 타입 불일치 → 인덱스 무효화
슬로우 쿼리	pg_stat_statements로 total_exec_time 기준 TOP N 추출 후 EXPLAIN ANALYZE 분석
TypeORM 인덱스	`@Index()` 로 단순 인덱스, `@Index(['col1','col2'])` 로 복합 인덱스, INCLUDE는 마이그레이션 직접 작성

인덱스는 읽기를 빠르게 하지만 쓰기(INSERT/UPDATE/DELETE)는 느려진다. 무분별하게 추가하지 말고, 실제 슬로우 쿼리를 확인한 뒤 필요한 곳에 정확하게 추가하는 것이 원칙이다.

11. 선택 부록 — 운영 절차는 짧게 확인하기

본문의 목표는 실행 계획을 읽는 힘이다. 아래 절차는 학습 흐름을 끊지 않도록 부록으로 둔다. 실제 운영에서는 조직의 변경 승인, RDS 파라미터 그룹, 트래픽 시간대, 백업 정책을 함께 확인해야 한다.

pg_stat_statements로 후보 쿼리 찾기

pg_stat_statements는 쿼리별 호출 수와 실행 시간을 누적한다. 설치와 활성화는 환경마다 다르므로 여기서는 “어떤 지표를 볼 것인가”에 집중한다.

SELECT
  left(query, 100) AS query_preview,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

읽는 순서는 total_ms로 DB 시간을 많이 쓰는 쿼리를 찾고, calls로 반복 호출 여부를 확인하고, mean_ms로 개별 요청 지연을 본다. 후보를 찾은 뒤에는 해당 SQL을 EXPLAIN (ANALYZE, BUFFERS)로 다시 읽는다.

인덱스 bloat 확인과 정리 경계

인덱스 크기와 테이블의 dead tuple을 함께 본다. 크기만 보고 바로 REINDEX하지 않는다.

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

VACUUM은 재사용 가능한 공간을 늘리고, REINDEX는 인덱스 구조를 다시 만든다. 운영 중 재빌드는 비용이 크므로 먼저 autovacuum이 제 역할을 하고 있는지, 인덱스 컬럼 UPDATE 때문에 HOT update가 깨지는지 확인한다.

-- 운영 정책과 버전 확인 후 사용
REINDEX INDEX CONCURRENTLY idx_orders_status;

CREATE INDEX CONCURRENTLY 후 확인할 것

운영 중 큰 테이블에 인덱스를 추가할 때는 일반 CREATE INDEX보다 CONCURRENTLY가 쓰기 차단을 줄이지만, lock-free는 아니며 실패 후 무효 인덱스가 남을 수 있다.

CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders(status, created_at);

진행 상황은 다음처럼 볼 수 있다.

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;

생성 뒤에는 유효성을 확인한다.

SELECT i.relname AS index_name, ix.indisvalid, ix.indisready
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE i.relname = 'idx_orders_status_created';

indisvalid=false면 쿼리 plan에는 사용되지 않을 수 있다. 정리와 재시도는 트랜잭션 안에서 묶지 말고, 변경 승인과 트래픽 상황을 확인한 뒤 단독 명령으로 처리한다.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status_created;
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders(status, created_at);

슬로우 쿼리 발생 시 5분 안에 분류하는 법

DB 전체 지표가 나쁜지, 특정 SQL 하나가 나쁜지 분리한다.
현재 실행 중인 긴 쿼리를 확인한다.
후보 SQL을 EXPLAIN (ANALYZE, BUFFERS)로 실행한다.
lock wait인지, Seq Scan인지, 높은 Rows Removed by Filter인지, 큰 loops인지, stale statistics인지, disk spill인지 이름 붙인다.
lock 해소, 인덱스 추가, 통계 갱신, 쿼리 수정, 배치 세션의 work_mem 조정 중 가장 작은 변경을 고른다.

현재 실행 중인 긴 쿼리 후보는 다음처럼 볼 수 있다.

SELECT
  pid,
  state,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;

lock wait이면 느린 실행 계획처럼 보여도 원인이 다르다. blocker를 확인할 때는 아래처럼 현재 세션을 막는 PID를 같이 본다.

SELECT
  pid,
  pg_blocking_pids(pid) AS blockers,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

장시간 실행 쿼리를 취소하는 것은 사용자 영향이 있는 운영 조치다. 이 문서에서는 명령을 외우기보다 “취소 전 원인 SQL과 트랜잭션 영향 범위를 확인해야 한다”는 경계를 기억한다.