Partial과 Expression 인덱스

중요한 row만 인덱싱하면 더 작아져

Partial 인덱스는 WHERE 조건에 걸리는 row만 인덱싱해. 그래서 더 작고, 유지 비용도 덜 들고, 늘 같은 방식으로 거르는 query라면 속도가 확 달라져.

CREATE INDEX idx_active_users ON users(email) WHERE archived = 0;

Partial 인덱스가 어울리는 자리는 이래.

query가 언제나 같은 조건으로 거를 때. 'active'라든가 'unprocessed' 같은 것들.
테이블 대부분이 그 조건에 안 걸릴 때. 걸리는 비율이 작을수록 빛나.

Expression 인덱스는 컬럼 값이 아니라 표현식의 결과를 인덱싱해. 대소문자 무시 검색이나 정규화한 값끼리 비교할 때 요긴하지.

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

이걸 만들어두면 SELECT * FROM users WHERE lower(email) = ?이 인덱스를 타. 없으면 SQLite가 query를 돌릴 때마다 모든 email에 lower()를 다시 걸어야 해.

Tip: 큐 테이블에는 partial 인덱스가 딱이야. CREATE INDEX idx_queue_pending ON jobs(created_at) WHERE status = 'pending'처럼. 인덱스가 pending row만 담으니까 작고, 과거 row가 수백만 개로 불어나도 '뽑아서 처리 표시하기' 흐름이 계속 빨라.

Code

큐 테이블용 partial 인덱스·sql

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending',
  payload TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

-- Pending job 만 인덱싱 -> 작음 + 10M historic row 가도 빠름
CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending';

-- Optimizer 가 같은 predicate 포함 query 에 사용:
EXPLAIN QUERY PLAN
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 10;
-- USING INDEX idx_jobs_pending

대소문자 무시 lookup용 expression 인덱스·sql

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

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE lower(email) = ?;
-- USING INDEX idx_users_email_lower

Exercise

row가 10만 개 넘고 대부분이 'inactive'나 'closed', 'archived'인 테이블에서 'active'만 덮는 partial 인덱스를 설계해봐. 같은 query의 시간을 비교하고 SELECT * FROM dbstat WHERE name='your_index'로 디스크 사용량도 견줘. 그다음 대소문자 무시 검색용 expression 인덱스를 만들고 plan이 바뀌는지 확인해.

중요한 row만 인덱싱하면 더 작아져

Code

External links

Exercise

Progress

댓글 0