5

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial.

Now I'm wondering why a partial HASH index isn't smaller than a full hash index:

CREATE INDEX full_hash    ON mytable USING HASH(my_id); # 256 MB
CREATE INDEX partial_hash ON mytable USING HASH(my_id) WHERE my_ID IS NOT NULL; # 256 MB

CREATE INDEX full_btree    ON mytable (my_id); # 537 MB
CREATE INDEX partial_btree ON mytable (my_id) WHERE my_ID IS NOT NULL; # 32 MB

Both hash indices take exactly the same amount of space (as shown in pgHero). However, when using standard BTREE indices, the partial index takes only 5% of the space of the full index.

Are partial HASH indices not supported in PostgreSQL 10?

6

I would argue that this is a bug in the hash index code. When you create an index on an already-populated table, it tries to pre-size the index to hold all the data so that it doesn't have to keep splitting buckets as the index is created. But the code for doing this does not take the NULL fraction of the column nor (apparently) the selectivity of the partial index clause into account, so it arrives at a too-large number for the pre-sizing.

If you were to create the index first, and then populated the table, you will find that the hash index is small, whether you made it partial or not. If the table is going to grow substantially after the index is created, the extra space consumed by the index upon original creation will be put to good use.

4

It's not explicitly stated in the documentation, but in the source code there is the following comment:

/*
 * We do not insert null values into hash indexes.  This is okay because
 * the only supported search operator is '=', and we assume it is strict.
 */

So the is not null predicate does indeed change nothing, as null values are always ignored for hash indexes (which does make sense, as comparing null values with = would never return true).

  • 2
    Interesting. So apparently, hash indexes aren't appropriate for sparsely populated columns. I tested with a column even less populated (only a few 100 records out of >10 m total) and the index took 256 MB as well. So it looks like the space of a hash index only depends on table size, not on the number of different indexable values. – Ortwin Gentz Mar 8 at 13:19
  • This explains why the two HASH indexes are the same size as each other, but not why they are so large compared to the btree indexes. – jjanes Mar 8 at 14:23
  • The full btree index is more than double the size of the hash index. – Ortwin Gentz Mar 8 at 19:47

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.