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.

Practice

1.

What is the main advantage of using a hash index in PostgreSQL?

easy

A. It speeds up equality searches on a column.

B. It improves performance of range queries.

C. It compresses data to save disk space.

D. It automatically updates foreign keys.

Solution

1. Step 1: Understand hash index purpose

   Hash indexes are designed to speed up searches where you look for exact matches (equality) on a column.

2. Step 2: Compare with other index types

   Unlike B-tree indexes, hash indexes do not support range queries or ordering, so they are not useful for those.

3. Final Answer:

   It speeds up equality searches on a column. -> Option A

4. Quick Check:

   Hash index = equality speedup [OK]

Hint: Hash indexes are for exact matches, not ranges. [OK]

Common Mistakes:

• Thinking hash indexes speed up range queries
• Confusing hash indexes with data compression
• Assuming hash indexes handle foreign keys automatically

2.

Which of the following is the correct syntax to create a hash index on the email column of a table named users?

?

easy

A. CREATE INDEX ON users HASH (email);

B. CREATE HASH INDEX users_email ON users (email);

C. CREATE INDEX users_email ON users USING btree (email);

D. CREATE INDEX users_email_hash ON users USING hash (email);

Solution

1. Step 1: Recall hash index syntax

   The correct syntax uses CREATE INDEX, specifies the index name, the table, and uses USING hash to indicate a hash index.

2. Step 2: Check each option

   CREATE INDEX users_email_hash ON users USING hash (email); matches the correct syntax exactly. The other options have syntax errors or use the wrong index type.

3. Final Answer:

   CREATE INDEX users_email_hash ON users USING hash (email); -> Option D

4. Quick Check:

   CREATE INDEX ... USING hash ... [OK]

Hint: Use 'CREATE INDEX name ON table USING hash (column);' [OK]

Common Mistakes:

• Using CREATE HASH INDEX instead of CREATE INDEX
• Forgetting 'USING hash' clause
• Using btree instead of hash for hash index

3.

Given the table products(id INT, name TEXT) with a hash index on id, what will the query SELECT * FROM products WHERE id = 10; most likely use?

medium

A. A sequential scan ignoring the index

B. A hash index scan for fast equality lookup

C. A bitmap index scan for range search

D. A full table lock before scanning

Solution

1. Step 1: Identify query condition type

   The query uses an equality condition on the id column: id = 10.

2. Step 2: Match index type to query

   Since there is a hash index on id, PostgreSQL will use a hash index scan to quickly find rows where id equals 10.

3. Final Answer:

   A hash index scan for fast equality lookup -> Option B

4. Quick Check:

   Equality query + hash index = hash index scan [OK]

Hint: Equality WHERE uses hash index scan if available. [OK]

Common Mistakes:

• Assuming sequential scan always happens
• Confusing bitmap index with hash index
• Thinking hash index supports range queries

4.

Consider the following SQL commands:
CREATE TABLE employees(id INT, name TEXT);
CREATE INDEX emp_id_hash ON employees USING hash (id);
SELECT * FROM employees WHERE id > 5;
What is the problem with using the hash index in this query?

medium

A. The table must have a primary key before creating a hash index.

B. The index name is invalid for hash indexes.

C. Hash indexes do not support range queries like id > 5.

D. The query syntax is incorrect for using indexes.

Solution

1. Step 1: Understand hash index limitations

   Hash indexes only support equality searches, not range conditions like id > 5.

2. Step 2: Analyze the query condition

   The query uses a range condition, so the hash index cannot be used efficiently here.

3. Final Answer:

   Hash indexes do not support range queries like id > 5. -> Option C

4. Quick Check:

   Range query + hash index = no use [OK]

Hint: Hash indexes only work with '=' conditions. [OK]

Common Mistakes:

• Thinking hash indexes support range queries
• Believing index names must follow special rules
• Assuming primary key is required for hash index

5.

You have a large table orders(order_id INT, customer_id INT, status TEXT). You often query orders by customer_id with equality conditions, but sometimes you query by status with range-like conditions (e.g., status > 'A'). Which indexing strategy is best?

hard

A. Create a hash index on customer_id and a B-tree index on status.

B. Create hash indexes on both customer_id and status.

C. Create a B-tree index on customer_id only.

D. Create no indexes to avoid overhead.

Solution

1. Step 1: Match index types to query patterns

   Hash indexes are good for equality searches, so use one on customer_id. B-tree indexes support range queries, so use one on status.

2. Step 2: Evaluate options

   Create a hash index on customer_id and a B-tree index on status. correctly assigns hash index for equality and B-tree for range. Create hash indexes on both customer_id and status. wrongly uses hash for range. Create a B-tree index on customer_id only. misses index on status. Create no indexes to avoid overhead. ignores performance.

3. Final Answer:

   Create a hash index on customer_id and a B-tree index on status. -> Option A

4. Quick Check:

   Equality = hash, range = B-tree [OK]

Hint: Use hash for '=' and B-tree for ranges together. [OK]

Common Mistakes:

• Using hash index for range queries
• Not indexing frequently queried columns
• Avoiding indexes due to overhead without reason