Hash index for equality in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Hash Index on Equality Query Performance

Consider a PostgreSQL table users with columns id (primary key) and email (unique). A hash index is created on the email column.

Which query will most likely benefit from this hash index?

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE);
CREATE INDEX users_email_hash_idx ON users USING HASH (email);

A SELECT * FROM users WHERE email LIKE '[e-mail]';

B SELECT * FROM users WHERE email = '[e-mail]';

C SELECT * FROM users WHERE email > '[e-mail]';

D SELECT * FROM users ORDER BY email;

💡 Hint

Hash indexes are optimized for equality comparisons, not range or pattern matching.

Hash indexes in PostgreSQL are designed to speed up queries that use equality operators (=). Queries using LIKE with wildcards, range comparisons, or ordering do not benefit from hash indexes.

🧠 Conceptual

intermediate

Hash Index Limitations in PostgreSQL

Which of the following is NOT a limitation of hash indexes in PostgreSQL?

A They require more disk space than B-tree indexes for the same data.

B They cannot be used to enforce uniqueness constraints.

C They are not WAL-logged by default, so recovery can be problematic.

D They only support equality comparisons, not range queries.

💡 Hint

Think about the storage size and uniqueness enforcement capabilities.

Hash indexes support equality queries only and cannot enforce uniqueness. They were historically not WAL-logged, but since PostgreSQL 10, they are WAL-logged. They do not necessarily require more disk space than B-tree indexes.

📝 Syntax

advanced

Creating a Hash Index Syntax

Which of the following SQL statements correctly creates a hash index on the username column of the accounts table in PostgreSQL?

A CREATE HASH INDEX accounts_username_hash ON accounts (username);

B CREATE INDEX accounts_username_hash ON accounts (username) USING HASH;

C CREATE INDEX HASH accounts_username_hash ON accounts (username);

D CREATE INDEX accounts_username_hash ON accounts USING HASH (username);

💡 Hint

Remember the correct order of clauses in the CREATE INDEX statement.

The correct syntax is CREATE INDEX index_name ON table_name USING HASH (column_name);. Other options have syntax errors or misplaced keywords.

❓ optimization

advanced

Choosing Index Type for Query Optimization

You have a table products with a column sku that is queried mostly by exact matches. You want to optimize query speed and reduce index size. Which index type is best?

A Create a Hash index on <code>sku</code>.

B Create a B-tree index on <code>sku</code>.

C Create a GIN index on <code>sku</code>.

D Create a GiST index on <code>sku</code>.

💡 Hint

Consider the type of queries and index size.

Hash indexes are optimized for equality lookups and can be smaller and faster than B-tree indexes for this use case. GIN and GiST indexes are for full-text search and complex data types, not simple equality.

🔧 Debug

expert

Diagnosing Hash Index Usage in Query Plan

You created a hash index on the customer_id column of the orders table. However, your query SELECT * FROM orders WHERE customer_id = 123; does not use the hash index according to EXPLAIN. What is the most likely reason?

A The hash index is not WAL-logged and thus disabled for queries.

B PostgreSQL prefers B-tree indexes by default and ignores hash indexes.

C The <code>customer_id</code> column is of a data type that does not support hash indexing.

D The query uses an inequality operator instead of equality.

💡 Hint

Check the data type compatibility with hash indexes.

Hash indexes only support certain data types. If the column's data type is unsupported, PostgreSQL will not use the hash index. PostgreSQL does not ignore hash indexes by default, and since version 10, hash indexes are WAL-logged. The query uses equality, so that is not the issue.