Hash Index

posted in: Projects | 0

btree_image.003Hash index is based on hash map data structure. It has lot of properties like, rehasing, load factor etc. You can read more about it in wikipedia.

We will more focus on usage and design of this data structure in the context of databases, where its size could be bigger than the physical memory and will need to be serialized to disk. Following are various points to consider when using it in databases.

Rehash – Increasing/decreasing size of buckets

Hash maps need to lock the whole data structure during the rehash. This may not be very expensive operation for in memory implementations of hash map. But may be almost impossible in the context of databases since rehash will have to lock the whole table.

Most databases suggest to rebuild the index when performance starts degrading over time due size of items in the index vs number of buckets. Or load factor going below certain threshold value.

Generic implementations of hashCode() and equals() methods

It may not be easy to provide hashCode() and equals() implementations specific to your index class. Usually databases will use their generic implementations which may not be very efficient to your index class. In wonderdb, we are working on a feature to register data type.

Load factor – Calculating buck size

By default Java hash map implementation sets load factor to 0.75. Allowing size of hash map to grow up to certain size before it is rehashed. For example say initial capacity of hash map is 100, then it will be rehashed  when 75th item is stored in to the hash map.

We need different load factor considerations for hash index. Lets take a example on how to calculate load factor for hash indexes.

As shown in the figure above index entries are stored in disk block. It is called leaf node in wonderdb. Say size of disk block is 2048 bytes. Now lets say you want to store long in to the index (8 bytes). Also assume index stores pointer to actual table record, say pointer size is 12 bytes, then it needs 20 bytes to store index item in the disk. Say one disk block (2048 bytes) stores 100 index items.

In this case, for optimized use of disk space it will be better to assume optimal settings will be 100 items per bucket, instead of 1 item per bucket (as in Java hash map implementation). So load factor settings for hash index will be .0075. Or in another way to look at it; to store 750 items, java hash map will need 1000 buckets to achieve 0.75 load factor, whereas hash index will need only 7.5 (or 8) buckets to store 1000 items since we need to assume 100 items per bucket in case of hash index.

BTree+ Vs Hash Index

Advantage of hash map data structure vs tree data structure is its access time is constant time (O1). Or it just takes 1 comparison (one instruction) to get to the item assuming it is properly organized. Where as for tree the access time is O(log n). Or it takes 10 compare instructions to get to an item in a tree containing 1024 items.

So for 1M items it takes 20 compares and for 1B it takes 30 compares for the tree. Where as for hash map it will take 1 compare instruction to get to the item. But problem is, we probably wont be able to store billions to items in hash map due to physical memory size constraints.

So for storing billions of items in hash index we need to also take load factor in to account. Say load factor is 0.0075 in case of storing longs, or in other words, if bucket should contain 100 items for optimal disk usage then already it needs to do 100 long 100 = 7 compares within the buckets to get to the item. So already access time is no more O1 but O7.

So to see optimal performance, we need number of items for Btree+ to do at lease 3x of 7 or 21 compares which is ~ 1M items.

So point here is, unless you are expecting millions of items in the index, dont even consider hash index.


We are able to see hash index performance can go up to 2x for a case where BTree+ needed 3 level deep structure.

We see performance of 60K queries per second in case of 32000 items with key size of 1500. We selected key size of 1500 just to make force 3 level deep BTree+. Where as for BTree+ we see close to 35-40K queries per second.

Here was the setup for the test.


It needed 32000 leaf nodes to store 32000 items since it can store only 1 item per node with 1500 size with 2048 block size.

It needed 32000/150 = 234 branch blocks. Branch blocks store pointers to next branch or leaf blocks and can store 150 items per block.

Next level of branch blocks needed 234/150 = 2 blocks.

And root node pointed to 2 next level blocks.

So our tree structure had 4 levels with

– 2 items in root node

– 2 branch blocks pointing to 234 items in next level

– 234 branch to point to 32000 leaf blocks.

– and 32000 leaf blocks.

It needed 1 compare (on root level) + 8 compares (on next level containing 234 pointers) + 15 compares (to get to leaf node) + 1 compare to find item in leaf node = 25 compares.

Hash Index

We had 32000 buckets to make hash index fully optimized to get O1 compare to get to an item.

With this setup we saw 60K queries per second for hash index and 40K queries per second for BTree+. over 50% improvement.


0 Responses

  1. Your comment is awaiting moderation.

    This is nicely put! .
    best canadian mail order pharmacies
    canadian pharmacy
    prescription drugs without prior prescription
    list of legitimate canadian pharmacies
    best online pharmacy – https://www.canadianpharmacyu.com/

    canadian drugs without prescription Ml f7b9efa

  2. Your comment is awaiting moderation.

    Reliable material. Thanks!
    online pharmacies
    canadian pharmacy
    discount prescription drug
    best 10 online canadian pharmacies
    pharmacies in canada – http://canadianpharmacyntv.com/

    canada drug pharmacy ki b1e8904

  3. Your comment is awaiting moderation.

    Truly a good deal of good advice.
    24 hour pharmacy
    canadian pharmaceuticals online
    canadian drugstore
    london drugs canada
    ed meds online – https://www.canadianpharmacy-guide.com/

    buy prescription drugs canada Pi 06202d9

  4. Your comment is awaiting moderation.

    Cheers. A lot of content.

    my canadian pharmacy
    canadian pharmacy drugs online
    canadian mail order pharmacies
    best 10 online canadian pharmacies
    prescription drugs from canada online – https://canadianpharmaciescubarx.com/

    safe canadian online pharmacies Sn 4b8b11_

  5. Your comment is awaiting moderation.

    Many thanks. I value it.
    best non prescription online pharmacies
    pharmacy northwest canada
    canadian pharmacies
    medical information online – https://www.canadianpharmacycom.com/

    prescription prices comparison Wame 14_917c

  6. Your comment is awaiting moderation.

    Appreciate it, A lot of info.

    no prior prescription required pharmacy
    canadian pharmacies-24h
    best online pharmacies canada
    canada pharmaceuticals online
    list of approved canadian pharmacies – https://www.canadianwwwonlinepharmacy.com/

    canada pharmacy online no script Ma 202d94b

  7. Your comment is awaiting moderation.

    Info effectively taken!!
    order prescriptions online without doctor
    canadian pharmacy
    canadian pharmacy 365
    canadian drug
    canada pharmacies online pharmacy – https://www.trustedwebpharmacy.com/

    prescription drugs from canada online Vat b18_917

  8. Your comment is awaiting moderation.

    Superb advice. Cheers.
    walgreens pharmacy
    canadian pharmaceuticals online
    internet pharmacy
    canadian drug
    online pharmacy canada – https://www.canadianonlinepharmacytrust.com/

    canada pharmacies sn 4b8b14_

  9. Your comment is awaiting moderation.

    Really quite a lot of helpful tips.
    canadian drugstore
    online pharmacies of canada
    canada pharmacy
    canadian prescriptions online
    pharmacy drug store – https://www.canadianpharmacy-guide.com/

    canada prescription drugs on 0b1e890

  10. Your comment is awaiting moderation.

    Superb information, Many thanks.
    canada pharmacies
    canadian viagra
    canadian online pharmacies legitimate
    canadian online pharmacies legitimate – http://canadianpharmacyntv.com/

    drug stores near me nom 7b9efa0

  11. Your comment is awaiting moderation.

    Tips nicely considered!!
    buy drugs online
    canadian pharmacy online
    online pharmacy no prescription needed
    canadian viagra
    canadian pharmacies top best – https://www.canadianwwwonlinepharmacy.com/

    canadadrugs.com pharmacy Vat 0406062

  12. Your comment is awaiting moderation.

    You stated it effectively.
    fda approved canadian online pharmacies
    ed meds online without doctor prescription
    canada pharmacies prescription drugs
    london drugs canada
    pharmacy prescription – https://canadianpharmaciescubarx.com/

    canadian pharmacy cialis Foes e890406

  13. Your comment is awaiting moderation.

    You revealed that fantastically.
    viagra from canada
    canada drugs online
    my canadian pharmacy
    london drugs canada
    canada pharmacy online no script – https://www.canadianpharmacyu.com/

    canadian pharma companies hed fa0b1e8

  14. Your comment is awaiting moderation.

    Whoa a lot of excellent knowledge!
    canadian pharmacies top best
    canadian drugs
    canadian prescription drugstore
    canadian viagra
    canadadrugs – http://canadianpharmaceuticalsonlinerx.com/

    canadadrugs nex 0b1e890

  15. Your comment is awaiting moderation.

    Reliable content. Appreciate it!
    canadian pharmacy
    canadian pharmacies that are legit
    canadian pharmacy viagra
    canadianpharmacyservice.com – https://www.canadianpharmacy-guide.com/

    canadian pharmacy viagra Paky 0_917c5

  16. Your comment is awaiting moderation.

    Info nicely applied.!
    drugs online
    canadian pharmacy
    canada drugs online
    ed meds online without doctor prescription
    canada pharmacies prescription drugs – http://canadianpharmaceuticalsonlinerx.com/

    canadian pharcharmy aff7b9e

  17. Your comment is awaiting moderation.

    Great info. Many thanks.
    canada drugs online pharmacy
    canadian pharcharmy online
    online discount pharmacy
    ed meds online without doctor prescription
    canadian family pharmacy – https://www.canadianwwwonlinepharmacy.com/

    canada medication pharmacy ea 202d94b

  18. Your comment is awaiting moderation.

    Kudos! Helpful information.
    ordering prescriptions from canada legally
    canadian pharmaceuticals online
    best online pharmacies no prescription
    canadian pharmaceuticals online reviews
    discount pharmacy online – https://www.pharmacyonline-canadian.com/

    londondrugs Vaw b8b12_9

  19. Your comment is awaiting moderation.

    You said it perfectly.!
    northwest pharmacy/com
    canadian pharmaceuticals online reviews
    online discount pharmacy
    canadian prescriptions online
    canadian discount pharmacy – https://www.canadianpharmacy-guide.com/

    canadian pharmacies that are legit Sr 16_917c

Leave a Reply