I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial.
Now I'm wondering why a partial HASH index isn't smaller than a full hash index:
CREATE INDEX full_hash ON mytable USING HASH(my_id); # 256 MB CREATE INDEX partial_hash ON mytable USING HASH(my_id) WHERE my_ID IS NOT NULL; # 256 MB CREATE INDEX full_btree ON mytable (my_id); # 537 MB CREATE INDEX partial_btree ON mytable (my_id) WHERE my_ID IS NOT NULL; # 32 MB
Both hash indices take exactly the same amount of space (as shown in pgHero). However, when using standard BTREE indices, the partial index takes only 5% of the space of the full index.
Are partial HASH indices not supported in PostgreSQL 10?