Hash index for equality in PostgreSQL - Mini Project: Build & Apply

Hash Index for Equality in PostgreSQL

📖 Scenario: You are managing a small library database. You want to speed up searches for books by their exact ISBN number. Using a hash index can help PostgreSQL find books faster when you search by ISBN.

🎯 Goal: Create a table called books with columns for id, title, and isbn. Then create a hash index on the isbn column to speed up equality searches.

📋 What You'll Learn

Create a table named books with columns id (integer primary key), title (text), and isbn (text).

Insert three specific books with given id, title, and isbn values.

Create a hash index named idx_books_isbn_hash on the isbn column of the books table.

Use exact syntax for table creation, insertion, and index creation as specified.

💡 Why This Matters

🌍 Real World

Hash indexes are useful when you want to quickly find rows that exactly match a value, like looking up a book by its ISBN in a library database.

💼 Career

Database administrators and developers use indexes to optimize query performance, especially for large datasets where searching without indexes would be slow.

1

Create the books table

Create a table called books with three columns: id as an integer primary key, title as text, and isbn as text.

PostgreSQL

-- Create the books table with id, title, and isbn columns
-- Your code here

Hint

Use CREATE TABLE books and define id as INTEGER PRIMARY KEY.

2

Insert three books into books

Insert these three books into the books table with exact values:
1. id=1, title='The Great Gatsby', isbn='9780743273565'
2. id=2, title='1984', isbn='9780451524935'
3. id=3, title='To Kill a Mockingbird', isbn='9780061120084'

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    isbn TEXT
);
-- Insert the three books below
-- Your code here

Hint

Use a single INSERT INTO books (id, title, isbn) VALUES statement with all three rows.

3

Create a hash index on isbn

Create a hash index named idx_books_isbn_hash on the isbn column of the books table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    isbn TEXT
);

INSERT INTO books (id, title, isbn) VALUES
(1, 'The Great Gatsby', '9780743273565'),
(2, '1984', '9780451524935'),
(3, 'To Kill a Mockingbird', '9780061120084');
-- Create the hash index below
-- Your code here

Hint

Use CREATE INDEX idx_books_isbn_hash ON books USING HASH (isbn);

4

Verify the hash index creation

Add a comment line that confirms the hash index idx_books_isbn_hash is created on the isbn column.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    isbn TEXT
);

INSERT INTO books (id, title, isbn) VALUES
(1, 'The Great Gatsby', '9780743273565'),
(2, '1984', '9780451524935'),
(3, 'To Kill a Mockingbird', '9780061120084');

CREATE INDEX idx_books_isbn_hash ON books USING HASH (isbn);
-- Add a comment confirming the hash index creation
-- Your code here

Hint

Add a comment line starting with -- that mentions the hash index idx_books_isbn_hash on isbn.

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