I tried to create several types of indexes on same column of my table to see how they compare, all of them I was able to create quickly but not a HASH index. I read about them how they got better in recent Postgres versions but I guess they may still have some limitations.
My table has 96 477 996 rows and column I tried indexes on is type of integer.
CREATE INDEX gpps_brin_index ON cdc_s5_gpps_ind USING brin (id_transformace) WITH (pages_per_range='256');
--27s 879ms
-- drop index gpps_brin_index;
CREATE INDEX gpps_gin_index ON cdc_s5_gpps_ind USING gin (id_transformace);
-- 1m 13s
-- drop index gpps_gin_index;
CREATE INDEX gpps_btree_index ON cdc_s5_gpps_ind (id_transformace);
-- 45s 744ms
-- drop index gpps_btree_index;
But hash index didn't finish even after 38 minutes
CREATE INDEX gpps_hash_index ON cdc_s5_gpps_ind USING hash (id_transformace);
I tried to set work memory to 4GB to see if it makes any difference but no change.
So if other indexes are created within a minute then there is probably something wrong with hash index. I tried to create it on some small table and it finished quickly so it seems there is probably some size limitations when from certain table size index will start to struggle. Can someone confirm me this or is there something I am missing.
EDIT: As explained by @jjanes I tried hash index on another column which has only unique values (row id) and HASH index was created in 2m34s.
PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
Say you have 100 distinct values, which occur about 1 million times each. So only 100 buckets can ever be occupied. Once each id_transformance has its own bucket, then no matter how many more times you split a bucket, all the rows follow one path of the split and end up in the same bucket again. So each occupied bucket will have a long list of overflow pages. And I don't think there is a fast path to get to the end of such a list, you have to traverse it each time you need to add a record to the end.
So you get degenerate build performance when you have a large number of rows, but with only a small number of distinct values. This is not a general problem with large tables, but is specific to this situation.
This could possibly be improved for bulk index creation by creating a fast-path to the end of the overflow page list or the most-recently used bucket, but even if it were I still don't think this index type would be well suited for this type of data.