Hash indexes are used for only equality queries. Hash indexes store only hash values but do not store index key values. Therefore, hash indexes are suitable for equality query scenarios where the lengths of fields are long and many valid values of the fields are available.

Index structure

Hash index structure
  • Hash values are converted, and mapped to a bucket.
  • The number of buckets is 2 to the power of N.
  • Metapages contain relevant internal information about indexes.
  • Each bucket must contain at least one primary page.
  • Pages store hash values.
  • Before data is written to the bucket, the data is written to the primary page first. If the space of the primary page is insufficient, the data is written to the overflow page.
  • Bitmap pages are used to track clean overflow pages.

Operators

Hash indexes support only the equal sign operator (=). This indicates that hash indexes are suitable for only equality queries.

  • Query data.
    select * from test where id=1;
  • Create a hash index.
    create index ON test using hash(id);
  • View the execution plan.
    postgres=# explain select * from test where id=1;
                                   QUERY PLAN
    -------------------------------------------------------------------------
     Index Scan using test_id_idx on test  (cost=0.00..8.02 rows=1 width=10)
       Index Cond: (id = 1)
    (2 rows)

Examples

  • Create a table.
    create table a(id int,name text);
  • Insert data.
    insert into test select id,md5(id::text)||md5(id::text)||md5(id::text)||md5(id::text) from generate_series(1,3000000) t(id);
  • Create a hash index.
    create index idx_test_hash ON test using hash (name);
  • Create a B-tree index.
    create index idx_test_btree on test(name);
  • Query data.
    select * from test where name='c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b';
Index type Index size Query duration
Hash indexes 224 MB 0.029 ms
B-tree index 491 MB 0.103 ms