複合インデックスと列の順序

複合インデックスとは

CREATE INDEX orders_user_created_idx ON orders(user_id, created_at DESC);

複合インデックスを使う理由

左端プレフィックスのルール

順序が重要な理由

CREATE INDEX orders_user_total_idx ON orders(user_id, total);

-- Best for:  WHERE user_id = 42 AND total > 100
-- Also OK:  WHERE user_id = 42
-- Useless:  WHERE total > 100   (without user_id)

等価条件と範囲条件

-- Index: (status, created_at)
WHERE status = 'paid' AND created_at >= ...   -- both columns used
WHERE created_at >= ... AND status = 'paid'  -- index used for status, range scan on created_at

インデックスの順序 = ソート順

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC LIMIT 10;
-- Index Scan, no Sort node needed

キー以外の列を含める（INCLUDE）

CREATE INDEX orders_user_idx ON orders(user_id)
  INCLUDE (total, status);

-- Enables Index-Only Scan for queries that need just these columns.

複合インデックスを作らない場合

ビットマップインデックスの組み合わせ

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- BitmapAnd of two index scans — works without a composite, but slower than the right composite.

インデックスの肥大化に注意

列の順序の選び方 — 実例

-- Index design:
CREATE INDEX orders_user_created_idx
  ON orders(user_id, created_at DESC);

-- Query (efficiently uses the index):
SELECT * FROM orders
WHERE user_id = 42
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC LIMIT 10;

まとめ

理解度チェック