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.