Hash index for equality in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a hash index in PostgreSQL?

A hash index is a type of database index that uses a hash function to map keys to a location. It is optimized for fast equality comparisons, meaning it quickly finds rows where a column equals a specific value.

beginner

When should you use a hash index in PostgreSQL?

Use a hash index when you need very fast lookups for equality comparisons (e.g., WHERE column = value). It is not suitable for range queries or sorting.

beginner

How do you create a hash index on a column named 'username' in PostgreSQL?

You use the command: <br>CREATE INDEX idx_username_hash ON tablename USING hash (username);<br>This creates a hash index on the 'username' column.

intermediate

What is a limitation of hash indexes in PostgreSQL compared to B-tree indexes?

Hash indexes only support equality comparisons and cannot be used for range queries or sorting. Also, before PostgreSQL 10, hash indexes were not WAL-logged, so they were not crash-safe.

beginner

How does PostgreSQL use a hash index during a query?

When a query has a condition like WHERE column = value, PostgreSQL can use the hash index to quickly find the matching rows by hashing the value and looking up the location directly.

What type of queries are hash indexes in PostgreSQL best suited for?

A Range queries (e.g., WHERE column > value)

B Full text search

C Sorting results

D Equality comparisons (e.g., WHERE column = value)

Hash indexes are optimized for equality comparisons only, not for range queries or sorting.

Which command creates a hash index on the 'email' column of a table named 'users'?

A CREATE INDEX idx_email_hash ON users USING btree (email);

B CREATE INDEX idx_email_hash ON users USING hash (email);

C CREATE INDEX idx_email_hash ON users (email);

D CREATE HASH INDEX idx_email ON users (email);

The correct syntax uses 'CREATE INDEX' with 'USING hash' to specify a hash index.

Before PostgreSQL 10, what was a major drawback of hash indexes?

A They were not crash-safe because they were not WAL-logged

B They could not be created on text columns

C They were slower than sequential scans

D They supported only range queries

Before version 10, hash indexes were not WAL-logged, so they could be lost after a crash.

Can a hash index be used to speed up queries with 'WHERE column > value'?

A No, hash indexes only support equality comparisons

B Yes, hash indexes support all comparison operators

C Yes, but only if combined with a B-tree index

D No, but they support sorting

Hash indexes only work for equality comparisons, not for range queries like greater than.

Which of the following is true about hash indexes in PostgreSQL?

A They support full text search

B They are the default index type

C They use a hash function to map keys to locations

D They automatically maintain sorted order

Hash indexes use a hash function to quickly find matching keys but do not maintain sorted order.

Explain what a hash index is and when you would use it in PostgreSQL.

Think about how hash indexes help find exact matches quickly.

Describe the limitations of hash indexes compared to B-tree indexes in PostgreSQL.

Consider what queries hash indexes cannot optimize.