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

Need a 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

Need a 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

Need a 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

Need a hint?

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