[pgsql-jp: 42068] Re: hash インデックスがb-treeインデックスより大きくなる

[NISHIMURA Yutaka]

高塚様
西村です。

ご説明ありがとうございます

なるほど、確かにnullの割合が多い感じですね
ということは、

・実データとしてはhashインデックスの方が小さい
・場所を確保する関係で見た目上のインデックスサイズが大きくなっている

と言う事ですね。

確かに、もうちょっと大きなTEXTカラムのデータはbtreeより小さくなっていますので
キャッシュヒット率等を考えると、実サイズは気にする事は無さそうですので、
このまま運用したいと思います

ありがとうございました。

西村


Reply to <20201013172043.ed380046793d867bbaa28497 ＠ postgresql.jp>
TAKATSUKA Haruka <harukat ＠ postgresql.jp> wrote:
> 高塚と申します。
> 
> PostgreSQLカンファレンス2020 でインデックスの話をしようと思っている
> ので宣伝を兼ねてコメントさせていただきます。
> 
>   https://www.postgresql.jp/jpug-pgcon2020
> 
> 
> TEXT型で4〜10バイト、uuid型で 16バイト程度のデータなら、
> btreeインデックスの方が小さくなってもおかしくありません。
> 
> もう少しサイズの大きい列なら、格納されるのがハッシュ値のみという
> 理屈通りの効果があらわれて hashインデックスの方が小さくなると思います。
> 
> 
> db=# SELECT pg_relation_filepath('estatus_id_excl');
>  pg_relation_filepath
> ----------------------
>  base/16384/91564
> (1 row)
> 
> db=# CHECKPOINT;
> db=# \q
> 
> $ bvi $PGDATA/base/16384/91564
> 
> などとして、インデックスの中身を覗くと分かりますが、0x00 が並んでいて
> 明らかにデータが詰まっていない領域が占めている割合が btreeインデックス
> よりも多くなっているはずです。
> 
> ハッシュで取りうる値に対応した格納場所を用意しなければいけないという
> 都合によります。件数が少なければ（サイズの小ささを競うのに）更に
> ハッシュインデックスが不利、Btreeインデックスが有利になります。
> 
> 
> > hash インデックスがb-treeインデックスよりかなり大きくなり
> > 同じテーブルのhashインデックス同士でサイズが違うのいう状態になっており
> > 理解が追いつかないので質問させて頂きます。
> > 
> > 1000万件程度のデータが入ったテーブルに置いて、下記のようになります
> > (インデックス名等、一部加工してます)
> > 
> > psql (12.4)
> > Type "help" for help.
> > hoge=# \di+
> >  public | estatus_id_excl      | index | | estatus | 384 MB  |
> >  public | setatus_label_hidx   | index | | estatus | 514 MB  |
> >  public | estatus_label_idx    | index | | estatus | 274 MB  |
> >  public | estatus_type_hidx    | index | | estatus | 514 MB  |
> >  public | estatus_type_idx     | index | | estatus | 217 MB  |
> > 
> > estatus_id_excl、setatus_label_hidx、estatus_type_hidxはhashインデックス
> > estatus_label_idx、estatus_type_idxは btreeインデックスです
> > どちらもTEXT型で4〜10バイト程度のデータが格納されています
> > estatus_id_exclもUUID型のhashインデックスですが、
> > ALTER TABLE public.estatus 
> >   ADD CONSTRAINT estatus_id_excl EXCLUDE USING hash (id WITH =);
> > として付けたものです
> > 
> > 1．hashインデックスは4バイトのハッシュ値を取るため、データ型の種類等に
> > 　　関係なく同一のサイズのインデックスになる
> > 2．hashインデックスは4バイトのハッシュ値を取るため、
> > 　　データをそのまま格納しているtext型のbtreeインデックスより、
> > 　　ほとんどの場合、小さなインデックスになる
> > 
> > と言う認識でしたが、実際の所 UUIDを格納したカラムのインデックス estatus_id_excl と
> > TEXT型を格納したカラムのインデックス、setatus_label_hidx、estatus_type_hidx のサイズが異なる
> > 
> > btreeインデックスの estatus_type_idx より、hashインデックスのestatus_type_hidxの方が
> > 2倍以上に大きい
> > 
> > PostgreSQL11の時も同じような結果になっていましたが
> > これはどういった結果このようになるのでしょうか？

-- 
NISHIMURA,Yutaka./西村ゆたか <iscream ＠ nugae.org>