When a hash index is created on a populated table, it estimates the number of buckets to start out with based on the number of tuples returned by estimate_rel_size. But this number ignores both the fact that NULLs are not stored in hash indexes, and that partial indexes exist. This can lead to much too large hash indexes. Doing a re-index just repeats the logic, so doesn't fix anything. Fill factor also can't fix it, as you are not allowed to increase that beyond 100.
This goes back to when the pre-sizing was implemented in 2008 (c9a1cc694abef737548a2a). It seems to be an oversight, rather than something that was considered.
Is this a bug that should be fixed? Or if getting a more accurate estimate is not possible or not worthwhile, add a code comment about that?
