部分インデックスと式インデックス

部分インデックス: 一部の行だけをインデックス化

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

部分インデックスが効果を発揮する場合

例: アクティブなセッション

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

よりよい方法: 安定した述語を使う

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

式インデックス

CREATE INDEX users_email_lower_idx ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

<ul><li>大文字と小文字を区別しない検索には<code>LOWER(email)</code></li><li>計算結果によるソートには<code>(price * tax_rate)</code></li><li>日次レポートには<code>date_trunc('day', created_at)</code></li><li>JSONBからの抽出には<code>(data ->> 'user_id')::BIGINT</code></li></ul>

式はIMMUTABLEでなければならない

部分インデックスと式インデックスの組み合わせ

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

一意部分インデックス

CREATE UNIQUE INDEX users_one_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Same email can exist many times in deleted users, but only once active.

インデックスの述語はクエリと一致させる

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

使いすぎない

まとめ

理解度チェック