Partial 과 Expression 인덱스

중요한 row 만 인덱스 — 더 작게

Partial 인덱스 가 WHERE 절에 매치되는 row 만 인덱싱. 더 작고, 유지 비용 적고, 항상 같은 식으로 필터링하는 query 면 극적으로 빠름.

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

Partial 인덱스 시기:

Query 가 항상 같은 predicate 로 필터링 (예: 'active', 'unprocessed').
대부분 테이블이 매치 안 함 — 작은 fraction 일 때 빛남.

Expression 인덱스 가 컬럼 대신 표현식 값 인덱싱. Case-insensitive lookup 이나 normalized 비교에 유용.

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 만), select-then-mark workflow 가 historic 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

Case-insensitive 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

100k+ row 테이블에서 대부분 'inactive'/'closed'/'archived' 면 'active' subset 만 cover 하는 partial 인덱스 디자인. 같은 query 시간 비교 + SELECT * FROM dbstat WHERE name='your_index' 로 디스크 비교. 그 다음 case-insensitive 검색용 expression 인덱스 + plan 확인.

Partial 과 Expression 인덱스

중요한 row 만 인덱스 — 더 작게

Code

External links

Exercise

Progress

댓글 0