> 22 марта 2019 г., в 22:38, PegoraroF10 <marcos@f10.com.br> написал(а):
>
> On version 10 and 11 hash index was redesigned and now seems to be better
> than btree.
> Their speed, size and memory consuption are better, so ...
> Why is not possible to have all PK and FK just using hash indices ? The only
> thing I need on a PK and FK is search and join, so hash index responds very
> well.
>
> I know a hash doesn´t accept unique index, so I cannot create a primary key
> on it.
> But I think it would be great, isn´t it ?
>
> There is something wrong with my thought ?
> If not, there is a way to have PK/FH structure using Hash index ?
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
The hash index is not good as expected. Some strange behaviour is observed even in 10 version.
https://www.postgresql.org/message-id/A841C4BC-A878-497E-AD9B-4DE0830DCC68%40ya.ru
Also btree keeps values inside an index and the uniqueness check can be made by index only scan. The hash index does
not.
Also hash index don’t support multicolumn without an obvious reason for me. This can be done simple, just creates hash
forevery column and XOR it.
I think the hash index is perfect for a natural primary key (text base, for instance), but only theoretically.
PostgreSQLimplementation still has too many problems.