Expression indexes in PostgreSQL - Mini Project: Build & Apply

Create and Use Expression Indexes in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a bookstore. You want to speed up searches for books by their lowercase titles to make the search case-insensitive and faster.

🎯 Goal: Build an expression index on the lowercase version of the title column in the books table to optimize case-insensitive searches.

📋 What You'll Learn

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

Insert three specific book titles into the books table.

Create an expression index on the lowercase of the title column.

Write a query that uses the expression index to find a book by a case-insensitive title.

💡 Why This Matters

🌍 Real World

Expression indexes help speed up searches that use computed values, like case-insensitive text matching, which is common in real-world applications such as search engines and user interfaces.

💼 Career

Database developers and administrators use expression indexes to optimize query performance and reduce response times for complex search conditions.

1

Create the books table and insert data

Create a table called books with columns id as an integer primary key and title as text. Then insert these three rows exactly: (1, 'The Great Gatsby'), (2, 'To Kill a Mockingbird'), and (3, '1984').

PostgreSQL

-- Create the books table and insert three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the books.

2

Add an expression index on lowercase titles

Create an index called idx_books_lower_title on the books table using the expression LOWER(title).

PostgreSQL

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

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

-- Create an expression index on LOWER(title)
-- Your code here

Need a hint?

Use CREATE INDEX with the index name and specify the expression LOWER(title) inside parentheses.

3

Write a query using the expression index

Write a SELECT query to find all columns from books where the lowercase title equals 'the great gatsby' using the expression LOWER(title) in the WHERE clause.

PostgreSQL

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

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

CREATE INDEX idx_books_lower_title ON books (LOWER(title));

-- Write a SELECT query to find the book with title 'the great gatsby' case-insensitively
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE LOWER(title) = 'the great gatsby' to use the expression index.

4

Verify the index usage with EXPLAIN

Write an EXPLAIN statement for the query SELECT * FROM books WHERE LOWER(title) = 'the great gatsby' to check that the expression index idx_books_lower_title is used.

PostgreSQL

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

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

CREATE INDEX idx_books_lower_title ON books (LOWER(title));

SELECT * FROM books WHERE LOWER(title) = 'the great gatsby';

-- Write EXPLAIN for the above SELECT query
-- Your code here

Need a hint?

Use EXPLAIN before the SELECT query to see the query plan and confirm index usage.