Hash index for equality in PostgreSQL - Step-by-Step Execution

Concept Flow - Hash index for equality

Query with equality condition

↓

Check if hash index exists on column

Yes↓

Compute hash of search value

↓

Use hash index to find matching rows

↓

Return rows matching equality condition

↓

End

When a query uses equality (=) on a column with a hash index, PostgreSQL computes the hash of the search value and uses the index to quickly find matching rows.

Execution Sample

PostgreSQL

CREATE INDEX idx_hash_name ON users USING hash (name);
SELECT * FROM users WHERE name = 'Alice';

Create a hash index on the 'name' column and query rows where name equals 'Alice'.

Execution Table

Step	Action	Input/Condition	Result/Output
1	Receive query	SELECT * FROM users WHERE name = 'Alice'	Start processing query
2	Check index type on 'name'	Hash index exists	Use hash index for lookup
3	Compute hash	Hash('Alice')	Hash value computed
4	Search hash index	Hash value	Find matching row pointers
5	Fetch rows	Row pointers from index	Retrieve rows where name = 'Alice'
6	Return result	Matching rows	Rows with name 'Alice' returned
7	End	No more steps	Query complete

💡 Query ends after returning all rows matching the equality condition using the hash index.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
query	SELECT * FROM users WHERE name = 'Alice'	Same	Same	Same
hash_index_exists	Unknown	True	True	True
hash_value	None	Hash('Alice')	Hash('Alice')	Hash('Alice')
row_pointers	None	None	Found pointers	Found pointers
result_rows	None	None	None	Rows with name 'Alice'

Key Moments - 2 Insights

Why does PostgreSQL use a hash index only for equality conditions?

Because hash indexes store hash values that match exactly, they work efficiently only for equality (=) checks, not for range or inequality conditions. See execution_table step 2 and 3.

What happens if there is no hash index on the column?

PostgreSQL will not use a hash index and will perform a sequential scan or use another index type. This is implied in execution_table step 2 where the presence of the hash index is checked.

Visual Quiz - 3 Questions

Look at the execution_table, what is the hash value computed for?

A The entire row data

B The search value 'Alice'

C The table name

D The index name

💡 Hint

Refer to execution_table step 3 where hash is computed for 'Alice'

At which step does PostgreSQL fetch the actual rows from the table?

A Step 5

B Step 3

C Step 2

D Step 7

💡 Hint

Check execution_table step 5 where rows are retrieved using row pointers

If the query used a condition 'name LIKE 'Al%'' instead of equality, what would happen?

A Hash index would still be used

B Query would fail

C Hash index would not be used

D Hash index would be converted to B-tree

💡 Hint

Hash indexes only support equality, see key_moments explanation

Concept Snapshot

Hash index in PostgreSQL:
- Created with: CREATE INDEX idx ON table USING hash(column);
- Used only for equality (=) conditions
- Works by hashing the search value and quickly locating matching rows
- Not suitable for range or pattern searches
- Provides fast lookups when equality condition matches

Full Transcript

This visual execution trace shows how PostgreSQL uses a hash index for queries with equality conditions. First, the query is received and PostgreSQL checks if a hash index exists on the searched column. If yes, it computes the hash of the search value. Then it uses the hash index to find pointers to matching rows. Finally, it fetches and returns the rows where the column equals the search value. Hash indexes only work efficiently for equality conditions, not for ranges or patterns. If no hash index exists, PostgreSQL uses other methods like sequential scans or different indexes.