We've finally upgraded to PostgreSQL 12, and the new BTree index mechanism provides pretty unexpected results. It's much more compact now than the hash index, at least for a column with lots of duplicates. New btree is 25 GB, new hash index is 38 GB (billion+ rows).
Conversation
Sadly, I can’t provide additional numbers on that, as I have not tried to create a btree index on that column in pg11 before upgrade. I can probably find other btree indexes that aren’t upgraded yet, reindex and report the difference. Is it interesting?
2
Yes, that's interesting. Though it's not exactly fair to compare the new pristine index to the old one, it might be after there has been some natural churn. Trends over time are most interesting.
1
We run pg_repack on a PG11 right before upgrade, because we didn’t have enough free space to upgrade. So almost all of these indexes aren’t bloated much. I’m not sure I have enough time and expertise to produce quality data on trends here, though
1
I wouldn't be surprised if the hash index was larger on low cardinality data like that on any version. Thanks for taking a look at it, though.
I have another table (225M rows) with a bigint column (96M distinct values, is a foreign key to other PK). New hash index is 7.6 GB, new btree index is 4.8GB. This isn't low cardinality for me! We've converted all our hash indexes to btree just yesterday.