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.

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