creating hash indexes

I inherited a database with several single-digit billion row tables. Those
tables have a varchar(36) column populated with uuids (all connected to
each other via FKs) each currently supported by a btree index.

After the recent conversations about hash indexes I thought I'd do some
comparisons to see if using a hash index could help and perhaps
depriortize my burning desire to change the data type. We never look up
uuids with inequalities after all. Indeed, in my test environments the
hash index was half the size of the btree index, and the select performance
was slightly faster than btree lookups. varchar(36) with hash index was
roughly comparable to using a uuid data type (btree or hash index).

I was pretty excited until I tried to create the index on a table with the
data (instead of creating it ahead of time and then loading up the test
data).

Working in PG 14.5, on a tiny 9M row table, in an idle database, I found:
- creating the btree index on the varchar(36) column to consistently take 7
*seconds*
- creating the hash index on the varchar(36) to consistently take 1 *hour*

I was surprised at how dramatically slower it was. I tried this on both
partitioned and non-partitioned tables (with the same data set) and in both
cases the timings came out similar.

I also tried creating a hash index on a varchar(100) column, also with 9M
rows. I gave up after it did not complete after several hours. (it wasn't
locked, just slow)

While I was experimenting with the different index types, I did some insert
tests. After putting the hash index on the column, the inserts were
significantly slower. The btree index was *6-7x *slower than no index, and
the hash index was *100x* slower than no index.

Assuming I can live with the slower inserts, is there any parameter in
particular I can tweak that would make the time it takes to create the hash
index closer to the btree index creation time? In particular if I wanted
to try this on a several billion row table in a busy database?

---

FWIW, from my tests on my laptop, on a 250M row table last weekend, after
100K selects:

MEAN (ms) | btree | hash
--------- | ------- | ----
varchar | 28.14916 | 27.03769
uuid | 27.04855 | 27.64424

and the sizes

SIZE | btree | hash
---- | ----- | ----
varchar | 12 GB | 6212 MB
uuid | 6595 MB | 6212 MB

- As long as the index fits in memory, varchar btree isn't really that
much slower in postgresql 14 (the way it was a few years ago), so we'll
probably just live with that for the forseeable future given the complexity
of changing things at the moment.

--
Rick