PostgreSQL은 언제 색인을 쓸까

색인이 있다고 반드시 고르지는 않아

계획기는 테이블 전체를 순서대로 읽는 비용과 색인을 거쳐 힙을 찾는 비용을 비교해. 결과가 1%라면 색인이 유리할 수 있지만 50%라면 차례로 읽는 편이 더 쌀 수 있어. 작은 테이블도 같은 이유로 순차 스캔을 고를 수 있지.

질의 모양이 색인과 맞아야 해

WHERE 조건이 색인 표현과 다르면 쓰지 못할 수 있어. WHERE LOWER(email) = 'x'에는 표현식 색인이나 pg_trgm을 검토하고, WHERE name LIKE '%foo'처럼 앞쪽 와일드카드에는 B-tree가 맞지 않아. WHERE numeric_col = '123'의 형 변환을 바로잡고, WHERE a = 1 OR b = 2에는 열별 색인과 Bitmap OR 계획을 확인해.

추측 대신 실제 계획을 봐

통계가 낡았다면 ANALYZE를 실행하고, EXPLAIN에 BUFFERS를 더해 실제로 읽은 블록을 확인해. 예제 값 하나만 보고 색인을 만들지 말고 선택도와 호출 빈도를 함께 봐.

Code

EXPLAIN ANALYZE로 실행 과정 들여다보기·sql

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- Index Scan? 좋음. 1M 행 테이블에 Seq Scan? 나쁨 — 조사.

열에 함수를 씌우면 색인을 못 쓸 수 있어·sql

-- users_email_idx 안 씀
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- 수정: expression 인덱스
CREATE INDEX users_lower_email_idx ON users (LOWER(email));

-- 같은 쿼리가 새 인덱스 사용.

ANALYZE로 계획기를 돕기·sql

-- 큰 import 또는 스키마 변경 후
ANALYZE users;          -- 테이블 한 개 통계 갱신
ANALYZE;                -- 전체 DB 통계 갱신

색인이 있다고 반드시 고르지는 않아

질의 모양이 색인과 맞아야 해

추측 대신 실제 계획을 봐

Code

External links

Exercise

Progress

댓글 0