Why is a Postgres 11 hash index so large?

Question

Postgres 11.4 on RDS and 11.5 at home.

I'm looking at hash indexes more closely today because I'm having problems with a citext index being ignored. And I find that I don't understand why a hash index is so large. It's taking about 50 bytes/row when I'd expect it to take 10 bytes + some overhead.

I've got a sample database with a table named record_changes_log_detail table that has 7,733,552 records, so ~8M. Within that table is a citext field named old_value that's the source for the hash index:

CREATE INDEX record_changes_log_detail_old_value_ix_hash
 ON record_changes_log_detail
 USING hash (old_value);

Here's a check on the index size:

select
'record_changes_log_detail_old_value_ix_hash' as index_name,
pg_relation_size ('record_changes_log_detail_old_value_ix_hash') as bytes,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_hash')) as pretty

That returns 379,322,368 bytes, or about 362MB. I've dug into the source a little, and this fine piece a bit more.

It sound like a hash index entry for a row is a TID paired with the hash key itself. And some kind of index counter within the page. That's two 4-byte integers and, I'm guessing a 1 or 2 byte integer. As a naive calculation, 10 bytes * 7,733,552 = 77,335,520. The actual index is a roughly 5x larger than that. Granted, you need space for the index structure itself, but it shouldn't take the rough cost per row from ~10 bytes to ~50, should it?

Here are the details of the index, read using pageinspect extension and then manually pivoted for legibility.

select * 
from hash_metapage_info(get_raw_page('record_changes_log_detail_old_value_ix_hash',0));
magic 105121344
version 4
ntuples 7733552
ffactor 307
bsize 8152
bmsize 4096
bmshift 15
maxbucket 28671
highmask 32767
lowmask 16383
ovflpoint 32
firstfree 17631
nmaps 1
procid 17269
spares {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,17631,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp {28673,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
select *
from hash_page_stats(get_raw_page('record_changes_log_detail_old_value_ix_hash',1));
live_items 2
dead_items 0
page_size 8192
free_size 8108
hasho_prevblkno 28671
hasho_nextblkno 4294967295
hasho_bucket 0
hasho_flag 2
hasho_page_id 65408

Answer 1

I get a freshly built index of 256MB with that size of table. Is your index freshly built? Was the table freshly analyzed just before the build (the index is pre-sized based on the estimated rows in the table). What is your distribution of duplicates like?

Things are stored with minimum 8-byte alignment, so a hash index tuple is 16 bytes even if it should fit in 10 (or 12, or whatever). And hash pages are on average only half full. Buckets are split in a predetermined sequence, it has to split the bucket whose turn is next, not the one that is most full.

select *
from hash_page_stats(get_raw_page('record_changes_log_detail_old_value_ix_hash',1));
live_items 2
dead_items 0
page_size 8192
free_size 8108

You aren't going to learn much by looking at just one page, but that page is oddly deficient in tuples. Maybe you have a pathological data distribution.

Micromanaging the database to this level is rarely worthwhile.

• Morris de Oryx: Thanks for the answer. And, yes, you nailed it: The data is very skewed. I looked yesterday, and the bulk the values are very, very short. Like, 1-10 characters. I just ran a check and there are only ~135K distinct entries across the entire table. So, some buckets are probably super full while most are sparse.
• Morris de Oryx: And fair point on micromanaging things, always a good reminder. In this case, I'm taking my current problem as an excuse to go down the rabbit hole a bit on various indexing and searching strategies. Either I'll come back with rabbits, or end up covered in....dirt..
• Laurenz Albe: Two hints for your investigations: 1) if you have a few very frequent values, consider a partial index that excludes them. 2) In addition to a b-tree index, consider a GIN index (after installing btree_gin).