3

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
2

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.

  • 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 Sep 18 at 21:32
  • 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.. – Morris de Oryx Sep 18 at 21:34
  • 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). – Laurenz Albe Sep 19 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.