« postgresql.confの罠 | トップページ | 己の足らざるところを自覚すること »

2014.02.07

部分インデックス

今週はPostgreSQL強化週間と個人的には銘打って、ここ最近覚えたり発掘したTipsを忘れないようにクリッピングしている(^^;

私の周囲で意外と知られていなかった機能にこの部分インデックスがある。私は重宝して使っていただけに、周囲から
「そんなことできるの?」
といわれたときはちょっと得意だったけども、まあ人間そんなものですね。

使い方は超簡単で、CREATE INDEX時にWHERE条件を設定するだけ。

CREATE INDEX idx_hogehoge ON hoge (hoge_id)
WHERE hoge_status = TRUE
;
こんな感じで。
B-treeが苦手な検索というのは、値の分散が少ない(カーディナリティが低い)データ。
まあこれはテーブル設計時のミスともいえるのだが、フラグ関係のカラムが存在しかつ、それが検索条件となるような場合に力を発揮する。
hoge_statusはboolean型なので2値しか持ち得ない。データ生成時点はTrueで、一定の処理が行われるとfalseになるとしよう。
こうした場合、hogeテーブルには膨大なhoge_status=falseのデータが蓄積されるわけだが、一般的な処理においてはhoge_status=trueのものを取り扱いたいはずである。
そこで上記インデックスの登場です。

単純にhoge_statusをキーにしたインデックスを作る方法もあるけども、そちらだと圧倒的多数のFALSE行とごく少数のTRUE行、ということになり一見有効そうに見える。
ところがTRUEかFALSEだけでなくいろいろと連結条件などが重なった場合、結局別のインデックスのほうが有利と判断されて、意外とこのステータス用のインデックスというのはオプティマイザが使用してくれないものだったりする。
ところが部分インデックスにした場合は、この部分内だけを対象とするので、TRUEのデータ内でさらに有益な検索キーを設定することでオプティマイザも選択しやすいし、サーチパスも短くてすむのでたいていは高速に動作する。
また、複合インデックスにした場合は、全行が対象となるのでインデックスそのもののサイズが大きくなり、ディスク容量の面でも検索性能の面でも芳しくない。

もちろん部分インデックスにすれば万事解決というわけではないが、案外ずばっと、決まって検索性能を伸ばせる可能性を秘めているので注目して欲しいところだ。

あ、ただそうかといってやたらにインデックスを作ると今度はHOT更新がされにくくなるので、有効ではないインデックスと判断されたらDROPするのはもちろん必要である。

|

« postgresql.confの罠 | トップページ | 己の足らざるところを自覚すること »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59063878

この記事へのトラックバック一覧です: 部分インデックス:

« postgresql.confの罠 | トップページ | 己の足らざるところを自覚すること »