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

[TAKATSUKA Haruka]

高塚と申します。

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の時も同じような結果になっていましたが
> これはどういった結果このようになるのでしょうか？