From the course: Advanced SQL for Application Development

Using hash indexes and equality operations

From the course: Advanced SQL for Application Development

Start my 1-month free trial

Using hash indexes and equality operations

“

- [Narrator] The next type of index we'll discuss is the hash index. The basis of this kind of index is a function called a hash function. Hash functions take an arbitrary length data and map it to a fixed-size value. Hash values are designed so that different inputs produce different outputs. Now, occasionally, quite rarely, two different inputs will produce the same output, but that's highly unlikely. In general, even a slight change in the input will produce different hash values. The has value that is created can vary in size, depending on which hash algorithm is used. You'll notice in this example that similar input strings can produce widely different hash values. Adding a single letter or changing the case of a letter is sufficient to lead to a new value, and as we can see, similar inputs can have vastly different outputs. Here are a few things to keep in mind about hash indexes. They're only used for equality comparisons. Hash values won't help you if you want to filter on a range of values. Som recent improvements in Postgres have led to hash indexes that can be smaller than B-tree indexes, but still just as fast or faster, so this can be an advantage when you want to keep an entire hash index in memory. Also, because they're fast or as fast as B-tree indexes, the choice between hash indexes and B-tree, if given the choice, come down to the advantage of being able to store an entire index in memory, which is possible with some hash indexes.

Contents