How fast is the hash index recalculation?

Question

PostgreSQL

I decided to change the index type from b-tree to hash on a table of 20 million records. PostgreSQL loaded percent, and SQL manager hung. After waiting 40 minutes, I restarted the PostgreSQL service. I don't understand, is it hanging or working (1 Xeon core was fully loaded)

Is the hashing operation so slow?

Answer 1

hash index is built in one thread, multi-threaded create index was not done for them. Therefore, loading 100% of one core is a normal situation.
No, create index does not hang.
If your field selectivity is bad, then the hash index will be built slowly, because many rows fall into the same hash value. I don’t remember exactly how the TID list is stored during hash function collisions, but it definitely sucks when there are a large number of duplicate values.
If the postgresql version is fairly recent, then see the pg_stat_progress_create_index system view. There will be some progress reporting.