zukucode
主にWEB関連の情報を技術メモとして発信しています。
PostgreSQL

PostgreSQL インデックスで検索を速くする基本

商品数が少ない間は全行を調べても速く見えますが、データが増えると検索条件に合う行を探す時間が長くなります。インデックスは、特定の列から行を探しやすくする構造です。

検索条件に使う列へ作成する

CREATE INDEX idx_products_category_id
ON products (category_id);

次の検索で利用される可能性があります。

SELECT product_id, name, price
FROM products
WHERE category_id = 10;

EXPLAINで計画を確認する

EXPLAIN
SELECT product_id, name, price
FROM products
WHERE category_id = 10;

結果にIndex ScanやBitmap Index Scanが出れば、インデックスを使う計画です。ただしSeq Scanが必ず問題とは限りません。テーブルが小さい場合や、大部分の行が一致する場合は、全行を読む方が安いと判断されます。

実行時間も測るEXPLAIN ANALYZEは対象SQLを実際に実行します。更新・削除SQLで安易に使わず、検証環境などで確認してください。

並び替えを含む複合インデックス

CREATE INDEX idx_products_category_created
ON products (category_id, created_at DESC);

SELECT product_id, name, price
FROM products
WHERE category_id = 10
ORDER BY created_at DESC
LIMIT 20;

複合インデックスでは列順が重要です。この例はcategory_idで絞り、created_at順に読む用途に合わせています。

増やしすぎない

インデックスはディスク容量を使い、INSERT・UPDATE・DELETE時にも更新されます。検索が速くなる代わりに書き込みコストが増えるため、すべての列へ機械的に作るものではありません。

重複禁止の要件は、速度目的の通常インデックスではなくUNIQUE制約で表します。

PostgreSQL

関連記事