인덱스 설계 — 복합·부분·표현식·GIN·BRIN

목표

20만 행 위에서 복합·부분·표현식·커버링·GIN·BRIN 인덱스를 직접 만들고, EXPLAIN (FORMAT JSON) 으로 계획이 정말 그 인덱스를 쓰는지 확인합니다. 마지막에 안 쓰이는 인덱스를 찾아 걷어 냅니다.

왜 중요한가

인덱스는 '만들면 빨라지는 것' 이 아니라 질의의 모양에 맞춰 고르는 것입니다. 열 순서가 틀린 복합 인덱스, 함수를 씌워 못 쓰게 된 인덱스, 종류가 안 맞는 인덱스는 자리와 쓰기 비용만 먹고 계획에 나타나지 않습니다. 어떤 인덱스가 어떤 질의를 받는지를 계획으로 확인하는 습관이, 인덱스를 늘리고도 느린 상황에서 벗어나는 길입니다.

환경

이 파드는 postgres 계정으로 돕니다. psql 만 치면 labdb 에 붙습니다.

export PATH=/usr/lib/postgresql/16/bin:$PATHexport PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdbpsql

실습 표(app_events, 20만 행)는 1단계에서 /opt/lab/fixtures/pg/idx_events.sql 을 적재해 만듭니다. 계획을 볼 때는 EXPLAIN (FORMAT JSON) <질의> 를 쓰고, 채점기도 같은 질의를 살아 있는 DB 에서 다시 돌려 노드 종류로 판정합니다. 산출물 메모는 /root/idx/ 아래에 둡니다.

단계

1. 픽스처(app_events)를 적재하고 행 수와 씨앗 인덱스를 확인합니다.
2. 두 열 등호 질의를 한 번의 인덱스 스캔으로 받는 복합 인덱스를 만듭니다.
3. status='error' 만 담는 부분 인덱스를 만듭니다.
4. lower(user_email) 표현식 인덱스를 만듭니다.
5. amount 를 INCLUDE 한 커버링 인덱스로 Index Only Scan 을 만듭니다.
6. props(jsonb)와 본문(tsvector)에 GIN 인덱스를 만듭니다.
7. occurred_at 에 BRIN 을 만들어 B 트리와 크기를 비교하고 /root/idx/07-brin.txt 에 남깁니다.
8. pg_stat_user_indexes 로 안 쓰이는 인덱스를 찾아 지우고 /root/idx/08-scans.txt 에 남깁니다.

참고

• EXPLAIN (FORMAT JSON) 의 계획 트리에서 "Node Type" 과 "Index Name" 을 보면 어떤 인덱스가 쓰였는지 알 수 있습니다.
• 인덱스를 만든 뒤에는 ANALYZE app_events 로 통계를 새로 잡아야 계획이 바뀝니다.
• 5단계의 Index Only Scan 은 VACUUM 으로 가시성 맵이 차 있어야 실제로 힙을 건너뜁니다.

단계 8개

1. 픽스처를 적재하고 씨앗 인덱스를 본다
2. 복합 인덱스로 두 열 등호 질의 받기
3. 부분 인덱스로 오류 행만 담기
4. 표현식 인덱스로 함수 질의 받기
5. 커버링 인덱스로 Index Only Scan
6. jsonb 와 전문 검색에 GIN
7. BRIN 으로 시계열 범위, 크기 비교
8. 안 쓰이는 인덱스를 찾아 지운다