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

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

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

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

Hint

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

Practice

1. What is the main purpose of an expression index in PostgreSQL?

easy

A. To create a backup of the database

B. To store data in a compressed format

C. To speed up queries that filter or sort by a calculated expression

D. To enforce foreign key constraints

Solution

1. Step 1: Understand what expression indexes do

   Expression indexes are special indexes built on the result of an expression or function, not just a column.

2. Step 2: Identify their main use

   They help speed up queries that filter or sort using that expression, improving performance.

3. Final Answer:

   To speed up queries that filter or sort by a calculated expression -> Option C

4. Quick Check:

   Expression index purpose = speed up expression queries [OK]

Hint: Expression indexes speed up queries using expressions [OK]

Common Mistakes:

• Confusing expression indexes with data compression
• Thinking expression indexes create backups
• Mixing expression indexes with constraints

2. Which of the following is the correct syntax to create an expression index on the lowercased username column in PostgreSQL?

easy

A. CREATE INDEX idx_lower_username ON users (LOWER(username));

B. CREATE INDEX idx_lower_username ON users ((LOWER(username)));

C. CREATE INDEX idx_lower_username ON users [LOWER(username)];

D. CREATE INDEX idx_lower_username ON users {LOWER(username)};

Solution

1. Step 1: Recall expression index syntax

   Expression indexes require double parentheses around the expression inside the index definition.

2. Step 2: Check each option

   CREATE INDEX idx_lower_username ON users ((LOWER(username))); uses double parentheses correctly: ((LOWER(username))). Options B, C, and D use incorrect syntax.

3. Final Answer:

   CREATE INDEX idx_lower_username ON users ((LOWER(username))); -> Option B

4. Quick Check:

   Expression index syntax = double parentheses [OK]

Hint: Use double parentheses for expressions in CREATE INDEX [OK]

Common Mistakes:

• Using single parentheses instead of double
• Using square or curly brackets
• Missing parentheses around the expression

3. Given the table products(id INT, price NUMERIC) and the index:
CREATE INDEX idx_discounted_price ON products ((price * 0.9));
What will the query below use to speed up filtering?
SELECT * FROM products WHERE price * 0.9 < 100;

medium

A. It will perform a full table scan ignoring the index

B. It will use a default index on price if it exists

C. It will cause a syntax error due to the expression

D. It will use the expression index on (price * 0.9) to speed up the query

Solution

1. Step 1: Understand the expression index usage

   The index is created on the expression (price * 0.9), matching the WHERE clause expression exactly.

2. Step 2: Match query filter with index expression

   Since the query filters on price * 0.9 < 100, PostgreSQL can use the expression index to speed up filtering.

3. Final Answer:

   It will use the expression index on (price * 0.9) to speed up the query -> Option D

4. Quick Check:

   Matching expression in WHERE = index used [OK]

Hint: Expression index used if query expression matches exactly [OK]

Common Mistakes:

• Assuming default indexes are used instead
• Thinking expression indexes cause errors
• Believing full table scan always happens

4. You tried to create an expression index with:
CREATE INDEX idx_expr ON sales (price * discount);
But PostgreSQL returned a syntax error. What is the problem?

medium

A. Expression indexes require double parentheses around the expression

B. You cannot create indexes on expressions involving multiplication

C. The table name is missing

D. The index name is invalid

Solution

1. Step 1: Check expression index syntax

   Expression indexes must have the expression enclosed in double parentheses to be valid.

2. Step 2: Identify the syntax error cause

   The given statement uses single parentheses, causing a syntax error.

3. Final Answer:

   Expression indexes require double parentheses around the expression -> Option A

4. Quick Check:

   Double parentheses fix syntax error [OK]

Hint: Use double parentheses for expressions to avoid syntax errors [OK]

Common Mistakes:

• Using single parentheses for expressions
• Thinking multiplication is not allowed
• Ignoring syntax error details

5. You want to speed up queries filtering by the first 3 letters of a city column in a locations table. Which expression index will best help?
CREATE INDEX idx_city_prefix ON locations (???);

hard

A. LEFT(city, 3)

B. SUBSTRING(city FROM 1 FOR 3)

C. city[1:3]

D. city LIKE '___%'

Solution

1. Step 1: Identify correct expression syntax for substring

   PostgreSQL supports the function LEFT(string, n) to get the first n characters.

2. Step 2: Evaluate options for expression index

   LEFT(city, 3) uses LEFT(city, 3) correctly inside the index. SUBSTRING(city FROM 1 FOR 3) uses SUBSTRING but syntax is less common and may be less efficient. city[1:3] is invalid syntax. city LIKE '___%' is a condition, not an expression.

3. Final Answer:

   LEFT(city, 3) -> Option A

4. Quick Check:

   LEFT function best for prefix expression index [OK]

Hint: Use LEFT(column, n) for prefix expression indexes [OK]

Common Mistakes:

• Using invalid substring syntax
• Confusing LIKE pattern with expression
• Using array slice syntax on strings