한양과기고 에듀테크 수업도구 대시보드

B-tree 복합 인덱스 부분 인덱스 INCLUDE CONCURRENTLY

인덱스는 '정렬된 사본'입니다. 특정 조건의 행을 전체 스캔 없이 찾게 해주지만, 쓰기마다 갱신 비용이 듭니다.

1. 언제 만드나

인덱스가 유리

자주 WHERE · JOIN ON · ORDER BY 에 쓰이는 컬럼
테이블이 크고, 조건이 소수의 행만 고를 때 (선택도 높음)
외래 키 컬럼 (PG 는 FK 에 자동 인덱스를 안 만듦!)

인덱스가 불리/무의미

행이 적은 테이블 · 조건이 전체의 큰 비율을 고를 때(순차 스캔이 빠름)
쓰기가 매우 잦은 테이블에 인덱스가 많음

2. 복합 인덱스와 컬럼 순서

CREATE INDEX idx_exams_student_date ON exams (student_id, exam_date DESC);

leftmost prefix 규칙

(student_id, exam_date) 인덱스는 WHERE student_id = ? 와 WHERE student_id = ? AND exam_date > ? 에 쓰임
WHERE exam_date = ? 단독은 못 씀
등호 조건 컬럼을 앞에, 범위/정렬 컬럼을 뒤에

3. 부분 · 표현식 인덱스

-- 활성 행만 색인 → 더 작고 빠름
CREATE INDEX idx_users_active ON users (last_login)
WHERE deleted_at IS NULL;

-- 함수 결과를 색인 (그 함수 그대로 WHERE 에 써야 적중)
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@x.com';

4. 커버링 인덱스 · 운영 중 생성

-- Index Only Scan 유도: 필요한 컬럼을 인덱스에 포함
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (status, total);

-- 운영 테이블은 락 없이
CREATE INDEX CONCURRENTLY idx_big ON big_table (col);
DROP INDEX CONCURRENTLY idx_old;

CONCURRENTLY 주의

트랜잭션 블록 안에서 못 씀 · 실패하면 INVALID 인덱스가 남음(\d 로 확인 후 DROP 하고 재생성)

"트랜잭션은 짧게, 잠금은 더 짧게"