Expression indexes in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of expression index on query performance

Given a table users with columns id (integer) and email (text), an expression index is created on LOWER(email). Which query will use this index efficiently?

PostgreSQL

CREATE INDEX idx_lower_email ON users (LOWER(email));

A SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

B SELECT * FROM users WHERE email = '[e-mail]';

C SELECT * FROM users WHERE UPPER(email) = '[e-mail]';

D SELECT * FROM users WHERE email LIKE '%example.com';

💡 Hint

Think about which query matches the expression used in the index.

The index is on LOWER(email), so only queries that use LOWER(email) in the WHERE clause can use this index efficiently. Option A matches exactly.

📝 Syntax

intermediate

Correct syntax to create an expression index

Which of the following is the correct syntax to create an expression index on the price * quantity expression in a sales table?

A CREATE INDEX idx_total ON sales (price, quantity);

B CREATE INDEX idx_total ON sales (price * quantity);

C CREATE INDEX idx_total ON sales USING btree (price * quantity);

D CREATE INDEX idx_total ON sales (price + quantity);

💡 Hint

Expression indexes require specifying the expression inside parentheses, optionally with an index method.

Option C correctly creates an expression index on price * quantity using the default btree method. Option C is missing the index method specification which is optional but recommended. Option C indexes columns separately, not the expression. Option C uses a different expression.

🧠 Conceptual

advanced

Why use expression indexes?

What is the main advantage of using an expression index in a database?

A To index the result of a function or expression for faster queries involving that expression.

B To automatically update all columns in a table when an expression changes.

C To store computed columns physically in the table for faster inserts.

D To create a backup of the table's data based on an expression.

💡 Hint

Think about what expression indexes store and how they help queries.

Expression indexes store the result of a function or expression applied to columns, allowing queries that filter on that expression to run faster. They do not update columns or store computed columns physically.

🔧 Debug

advanced

Why does this expression index not improve query performance?

A developer created this index:

CREATE INDEX idx_trim_name ON employees (TRIM(name));

But the query below does not use the index:

SELECT * FROM employees WHERE name = 'Alice';

Why?

A The TRIM function is not supported in expression indexes.

B The query does not use the TRIM function, so the index on TRIM(name) is not used.

C The index was created on the wrong table.

D The index needs to be created as UNIQUE to be used.

💡 Hint

Check if the query expression matches the index expression.

The index is on TRIM(name), but the query filters on name directly without trimming. The index is not used because the expressions do not match.

❓ optimization

expert

Optimizing queries with expression indexes on JSON data

Consider a products table with a JSONB column attributes. You want to speed up queries filtering products where attributes->>'color' = 'red'. Which index creation is best?

A CREATE INDEX idx_color ON products (attributes->'color');

B CREATE INDEX idx_color ON products (attributes);

C CREATE INDEX idx_color ON products USING gin (attributes);

D CREATE INDEX idx_color ON products ((attributes->>'color'));

💡 Hint

Think about indexing the exact expression used in the WHERE clause.

Option D creates an expression index on the exact expression attributes->>'color', which matches the query filter and speeds it up. Option D indexes the whole JSONB column, which is less efficient for this filter. Option D creates a GIN index for general JSONB queries but is less efficient for exact text matches. Option D uses attributes->'color' which returns JSON, not text, so it won't match the query.

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