Hash index for equality in PostgreSQL

Introduction

A hash index helps find rows quickly when you search for exact matches in a column.

You want to speed up searches where you look for a specific value, like finding a user by ID.

You have a large table and often check if a value exists exactly in a column.

You want faster lookups for equality comparisons (=) but not for range queries.

You want to improve performance of queries that use WHERE column = value conditions.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING HASH (column_name);

Hash indexes only work well for equality comparisons, not for ranges like > or <.

In PostgreSQL, hash indexes are less commonly used than B-tree indexes but can be faster for exact matches.

Examples

This creates a hash index on the email column of the users table to speed up searches by email.

PostgreSQL

CREATE INDEX idx_user_email_hash ON users USING HASH (email);

This creates a hash index on the product_code column of the products table for quick lookups by product code.

PostgreSQL

CREATE INDEX idx_product_code_hash ON products USING HASH (product_code);

Sample Program

This example creates a table, inserts some rows, adds a hash index on the department column, and then runs a query to find employees in the Sales department. The EXPLAIN ANALYZE shows if the hash index is used.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'HR'),
('Charlie', 'Sales');

CREATE INDEX idx_department_hash ON employees USING HASH (department);

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Important Notes

Hash indexes in PostgreSQL are WAL-logged by default since PostgreSQL 10, making them safe in case of crashes.

Hash indexes only support equality operators (=), so they won't help with queries using <, >, or BETWEEN.

Summary

Hash indexes speed up exact match searches in a column.

They are useful when you often query with equality conditions.

They are not suitable for range queries.