PK and FK using Hash index

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PK and FK using Hash index

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: PK and FK using Hash index

Олег Самойлов

> 22 марта 2019 г., в 22:38, PegoraroF10 <[hidden email]> написал(а):
>
> 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 for every column and XOR it.

I think the hash index is perfect for a natural primary key (text base, for instance), but only theoretically. PostgreSQL implementation still has too many problems.